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

%3CLINGO-SUB%20id%3D%22lingo-sub-2077047%22%20slang%3D%22en-US%22%3EQuery%20on%20Date%20Formatting%20from%20dd-mmm-yyyy%20to%20dd-mmm-yy%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2077047%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20change%20format%20of%20date.%20I%20have%20this%20Cell%20date%20formatted%20in%20dd-mmm-yyyy.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EColumn%20E%2C%20Cell%20E2%20(18-FEB-2020).%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20made%20Column%20F%20as%20the%20formatted%20cell%20and%20I%20want%20to%20look%20in%20this%20way%20(dd-mmm-yy)%5B18-FEB-20%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EColumn%20F%2C%20Cell%20F2%20(%3DE2)%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20upon%20formatting%20Cell%20F2%20(Ctrl1)%2C%20even%20when%20I%20chose%20the%20dd-mmm-yy%20format%20it%20doesnt%20change!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2077047%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2077116%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20on%20Date%20Formatting%20from%20dd-mmm-yyyy%20to%20dd-mmm-yy%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2077116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F935392%22%20target%3D%22_blank%22%3E%40JCarms%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20that%20the%20date%20in%20E2%20is%20a%20real%20date%20not%20a%20date%20as%20a%20text%20string.%20To%20check%20whether%20the%20date%20in%20E2%20is%20a%20real%20date%20or%20not%2C%20in%20an%20empty%20cell%2C%20place%20the%20formula%20%3CSTRONG%3E%3DISNUMBER(E2)%3C%2FSTRONG%3E%20and%20if%20this%20formula%20returns%20True%2C%20E2%20contains%20a%20real%20date%20otherwise%20it's%20a%20date%20as%20a%20text%20string.%3C%2FP%3E%3CP%3EExcel%20treats%20real%20dates%20as%20numbers.%20If%20E2%20is%20a%20date%20as%20a%20text%20string%2C%20you%20would%20first%20need%20to%20convert%20it%20to%20a%20real%20date.%20In%20F2%2C%20place%20the%20formula%20%3CSTRONG%3E%3DDATEVALUE(E2)%3C%2FSTRONG%3E%20and%20this%20would%20return%20the%20underneath%20date%20number%20in%20F2%20which%20you%20can%20then%20format%20as%20per%20your%20requirement.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.