Query on Date Formatting from dd-mmm-yyyy to dd-mmm-yy Format

Occasional Visitor

I can't change format of date. I have this Cell date formatted in dd-mmm-yyyy.

 

  • Column E, Cell E2 (18-FEB-2020).

 

Now, I made Column F as the formatted cell and I want to look in this way (dd-mmm-yy)[18-FEB-20]

 

  • Column F, Cell F2 (=E2)

 

However, upon formatting Cell F2 (Ctrl1), even when I chose the dd-mmm-yy format it doesnt change!

 

1 Reply

@JCarms 

Make sure that the date in E2 is a real date not a date as a text string. To check whether the date in E2 is a real date or not, in an empty cell, place the formula =ISNUMBER(E2) and if this formula returns True, E2 contains a real date otherwise it's a date as a text string.

Excel treats real dates as numbers. If E2 is a date as a text string, you would first need to convert it to a real date. In F2, place the formula =DATEVALUE(E2) and this would return the underneath date number in F2 which you can then format as per your requirement.