Date format showing different after downloading

[indresh268]

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

[#2]

@indresh268 

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.

[#3]

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.

[#4]

@indresh268 

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.

[#5]

Dear @hans,

Thank you for the reply, tried this solution and its working for the date column.
but customer wanted the date as customized and our solution stores the same in text column.
but when I apply your suggestion over there, these columns are as text type instead of date.
Now I need to read and every columns as value and check for the format 'DD-MON-YY' and if values matches needs to convert that as date and apply.
will update you with the progress. meanwhile if you have any suggestion regarding this via javascript please let me know.


Thank you.
Regards,
Indra

[#6]

regarding this via javascript

what you mean via JavaScript？
or vba?