Forum Discussion
Excel Date issue
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.
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.
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.
