Excel Date issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2859465%22%20slang%3D%22en-US%22%3EExcel%20Date%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2859465%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20column%20with%20date%20formatted%20as%20Thursday%2013%20May%202021%20and%20want%20to%20change%20it%20in%20the%20next%20column%20as%2013%2F05%2F2021.%20However%20it%20won't%20let%20me%20change%20it%20using%20the%20Category%20DATE%20under%20format%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20assist%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGayle%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2859465%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2859557%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Date%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2859557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1189320%22%20target%3D%22_blank%22%3E%40Gayle1965%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20%22Thursday%2013%20May%202021%22%20is%20just%20a%20text.%20It%20needs%20to%20be%20a%20number%20for%20it%20to%20be%20possible%20to%20reformat%20it%20as%20you%20desire.%20Dates%20are%20stored%20a%20sequential%20numbers%20starting%20at%201%20on%20January%201%2C%201900.%20So%2013%20May%202021%20becomes%20the%20number%2044329%20for%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20just%20an%20example%20of%20how%20you%20can%20transform%20texts%20to%20real%20dates.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-19%20at%2006.40.15.png%22%20style%3D%22width%3A%20351px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F318247iEB680D3DEB0D7706%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-19%20at%2006.40.15.png%22%20alt%3D%22Screenshot%202021-10-19%20at%2006.40.15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20achieve%20that%2C%20then%20you%20can%20format%20it%20as%20a%20%3CEM%3E%3CSTRONG%3ELong%20date%3C%2FSTRONG%3E%3C%2FEM%3E%20(i.e.%20with%20the%20day%20name)%20and%20in%20the%20other%20column%20you%20choose%20%3CEM%3E%3CSTRONG%3EShort%20date%3C%2FSTRONG%3E%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20very%20large%20data%20set%20and%20need%20to%20do%20this%20over%20and%20over%20again%2C%20there%20are%20more%20efficient%20ways%20to%20do%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi

I have a column with date formatted as Thursday 13 May 2021 and want to change it in the next column as 13/05/2021. However it won't let me change it using the Category DATE under format cells.

 

Can you assist?

 

Gayle

6 Replies

@Gayle1965 I suspect that "Thursday 13 May 2021" is just a text. It needs to be a number for it to be possible to reformat it as you desire. Dates are stored a sequential numbers starting at 1 on January 1, 1900. So 13 May 2021 becomes the number 44329 for Excel.

 

Here's just an example of how you can transform texts to real dates.

Screenshot 2021-10-19 at 06.40.15.png

 

Once you achieve that, then you can format it as a Long date (i.e. with the day name) and in the other column you choose Short date.

 

If you have a very large data set and need to do this over and over again, there are more efficient ways to do it.

@Riny_van_Eekelen the date is actually "date" formatted. I cannot change it to anything else.

 

Gayle1965_0-1634619140596.png

 

@Gayle1965 Let's say B1 contains your problem date. What do you get when you enter a the formula =ISNUMBER(B1) ? If the answer is FALSE, the cell contains a text and can thus not be formatted as a date. It needs to be a number.

Hi I have tried to convert it to a number and it wont convert. All i can do is delete the date in the cell. I have a large document I want to convert to normal date without the day. regards Gayle
I get TRUE

@Gayle1965Can you extract the date column from your file (a 100 rows or so), copy it to a new workbook and upload it here?