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

Forge Component
(49)
Published on 13 Aug (5 days ago) by Hanno
49 votes
Published on 13 Aug (5 days ago) 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.