[Advanced Excel] [Advanced Excel] - Incorrect date format on Cell_Read

Forge Component
(51)
Published on 27 Aug by Hanno
51 votes
Published on 27 Aug by Hanno

Hello,

I'm trying to read date formats from a file. The dates are written in the following dd/MM/yyyy format. In my excel file, the cells are formatted as "Date".

When I call Cell_Read, I am getting differently formated dates.


04/10/1965 becomes 10/04/1965

14/12/1972 becomes 12/14/1972

20/03/1957 becomes 03/20/1957


This breaks further along in the system, as there's no such thing as a 14th or 20th month of the year.


The questions I would like to ask:

1. What area of the code is determining a change is required?

2. Is this something that can be changed on my end?


Thank you

Hi David

I will need to take a look at this as it is definitely not ideal. It seems to be picking up some regional settings and using that date format.

You could, and I know this is really not ideal, adjust your code to cater for the "mm/dd/yyyy" format as an interim measure?

Do you perhaps know what the regional settings on your server are?


Regards

Hanno

Processing Upload...

David Bear wrote:

Hello,

I'm trying to read date formats from a file. The dates are written in the following dd/MM/yyyy format. In my excel file, the cells are formatted as "Date".

When I call Cell_Read, I am getting differently formated dates.


04/10/1965 becomes 10/04/1965

14/12/1972 becomes 12/14/1972

20/03/1957 becomes 03/20/1957


This breaks further along in the system, as there's no such thing as a 14th or 20th month of the year.


The questions I would like to ask:

1. What area of the code is determining a change is required?

2. Is this something that can be changed on my end?


Thank you

Hi David,

I have attached one demo file using same scenario that you mention but in my case it it working fine. Please let me know in case any concern.

Thanks,

Amit


Hanno wrote:

Hi David

I will need to take a look at this as it is definitely not ideal. It seems to be picking up some regional settings and using that date format.

You could, and I know this is really not ideal, adjust your code to cater for the "mm/dd/yyyy" format as an interim measure?

Do you perhaps know what the regional settings on your server are?


Regards

Hanno

Thank you for your reply.

I'm unsure where to find my regional settings. I'm based in the UK, so it may be safe to assume that's what it is.

I've also checked the date format in Service Center -> Administration -> Environment Configuration. We're on YYYY-MM-DD format. I've changed this to DD/MM/YYYY, but experienced the same results. It seems this property is superficial.


I've been tackling this issue for a few days but I am making no progress. I've attached an example of where I am experiencing this - it's the DOB column. I would appreciate any further help debugging this issue.

David, there seems to exist a bug in the EPPlus library (perhaps re-introduced) in the current version. 

If you change the date format in Excel to "yyyy-mm-dd" it should work okay.

EDIT: Note that I also had to set the ReadText property on Cell_Read to true.

Hope this helps, and sorry for the wait.

Hanno

Solution

So after about a month of investigation, I've managed to resolve this issue with a temporary fix. I'm writing this here incase someone else comes into this issue.

First of all, I created a new Read_cell method that is specifically for dates. In my use-case, I know when I will be reading a date and don't need a generic read for this.

In that method, the first thing I did was set CultureInfo to "en-GB" and applied it to the running thread. 

  • CultureInfo culture = new CultureInfo("en-GB");
    Thread.CurrentThread.CurrentCulture = culture;
    Thread.CurrentThread.CurrentUICulture = culture;

This would override whatever was making it appear in US format.

Finally, I set the following NumberFormat on the cell before reading it @"dd/MM/yyyy". This would fix another issue with second variant (dd MMMM yyyy).

Solution

Thank you for sharing your findings with us, and Kudos for sticking it out!!