Forum Discussion

larsgunnar007's avatar
larsgunnar007
Copper Contributor
Mar 19, 2020

Change date format.

Hi.   I struggle getting the date format change.   If there stay 2020.03.20 in A1 anf i mark A1, and change the format from yyyy.mm.dd to dd.mm.yyy A1 do not change. Still it stay 2020.03.20 in s...
excel
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Mar 19, 2020

larsgunnar007 

If changing the date format in A1 doesn't change the display format of the date in A1 that means the date in A1 looks like a date but in fact it is a date string.

Remember that Excel treats dates as real numbers and to check whether the date in A1 is a real date or not, put the formula =ISNUMBER(A1) in a blank cell. If the formula returns a True that means the A1 contains a real date but if the formula returns False, A1 contains a text string which looks like a date but not a real date.

What date separator do you use as per your regional settings? Is it a dot (period)?

If yes, select the date in A1, go to Data Tab --> Text to Columns --> click on Finish in the next step.

 

This would convert the date text string into a real date and then you can change the formatting of the date without an issue.

Resources