Excel Date issue

Copper 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?