Forum Discussion
Date format showing different after downloading
Dear Experts,
from an application i am downloading the data dump, and from when filetring on the date column its showing as '01-Jan-29' as Jan-2029 and '01-Jan-30' and after as Jan 1930 and so on.
when i checked the same with the plain excel its showing the same in excel. as it will show any year after 30 as 1930. could you please help us how to fix this or any idea.
Below screenshots for your sample.
Thank you.
Regards,
Indrakumar
Let's say your dates are in A2 and down.
In another cell in row 2, enter the formula
=EDATE(A2,1200*(YEAR(A2)<2000))
and fill down.
You can either use the new dates, or copy them, then paste them as Values over the original dates.
- This logic is working fine when using in excel formatting. could you please explain the logic of the same so that i can use this in my code for download option.
Thank you.Apparently the download specifies the year using only 2 digits. Excel (or rather Windows) then has to decide whether 40 means 1940 or 2040. There is a cutoff for this in Regional Settings. On many systems, 30 is the cutoff: 00 to 29 are interpreted as 2000 to 2029, and 30 to 99 are interpreted as 1930 to 1999.
So Excel will convert 1-Jan-30 to 01/01/1930.
The formula that I posted checks whether the year of the converted date is before 2000, and if so, adds 100 years = 1200 months using the EDATE function.
In VBA, you could use DateAdd("yyyy", 100, SomeDate) to add 100 years.
