Read Range vs 1900-01-01

Help Studio
, ,
1

Hi there,

Weird problem here:

I’m trying to use the Read Range activity to read a date from an Excel file. The date string is “1900-01-01” but when Read Range processes it, it comes back as “12/31/1899 00:00:00”.

Thoughts?

Thanks,

Carlo.

2

According to Microsoft it’s a feature in Excel, to “provide greater compatibility with Lotus 1-2-3”.

See also:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2010/using-vba-to-set-a-cell-to-a-date-erroneously/870250b1-f695-4965-8514-842febe6a94d

1 Like
3

So, this is a known issue with no solution other than to hard code adding 1 day to every date returned between 1899-12-31 and 1900-02-28? And if the actual date happens to really be 1899-12-31, it will not be handled properly?

Thank you Microsoft… argh!

4

Dates before 1900-01-01 are not considered “dates” so they will be as is when you read them with Read Range. If you enter “1899-12-31” in Excel, Read Range will return “1899-12-31” (no time added). So before adding a day, you also need to check for the " 00:00:00" part.

5

In my situation, I won’t need to deal with pre-1900 dates. The 1900-01-01 is only there due to a depersonalization process for a non-prod environment.

Thank you,

Carlo.

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.