Oct 18 2021 08:31 PM
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
Oct 18 2021 09:43 PM - edited Oct 18 2021 09:44 PM
@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.
Oct 18 2021 09:52 PM
Oct 18 2021 10:10 PM
@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.
Oct 18 2021 10:17 PM
Oct 18 2021 10:44 PM
@Gayle1965Can you extract the date column from your file (a 100 rows or so), copy it to a new workbook and upload it here?