Forum Discussion

Gayle1965

Copper Contributor

Oct 19, 2021

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. ...

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Oct 19, 2021

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

Copper Contributor

Oct 19, 2021

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

Riny_van_Eekelen
Platinum Contributor
Oct 19, 2021
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.
- Gayle1965
  Copper Contributor
  Oct 19, 2021
  I get TRUE
  - Riny_van_Eekelen
    Platinum Contributor
    Oct 19, 2021
    Gayle1965Can you extract the date column from your file (a 100 rows or so), copy it to a new workbook and upload it here?
- Gayle1965
  Copper Contributor
  Oct 19, 2021
  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