2

This question already has an answer here:

I want to change a number of cells formatted for date to text format, but unable to do so. For example, one cell contains 29/01/2018. When I changed the format to text, its changed to 43129. The question is : How to change the format, but retain the original content of the cell?

Any help would be appreciated

marked as duplicate by Máté Juhász, PeterH, bertieb, Dave M, music2myear Nov 26 '18 at 21:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Why has this been downvoted? – Dave Nov 22 '18 at 6:29
  • @DocBrown Wow, thats so much work for a seemingly small change. Well, thanks for the info anyway – Agil GA Nov 22 '18 at 7:53
  • You can use TEXT function to translate the DATE to TEXT format. – Lee Nov 22 '18 at 8:16
1

Excel stores dates as numbers. 1 is 1st-Jan-1900.

You can use formatting to display a date any way you want to.

The date 29-Jan-2018 is the number 43129, so when you format the cell as "General" it will show that number.

You can select a different format, for example "Short Date" or "Long Date", which will show the date following the conventions of the regional settings of your computer.

Or you can format the cell with a custom format and use any format you like by specifying a format string. Some format string examples are

dd-mm-yyyy ' this will show as 29-01-2018
dd/mmm/yyyy ' this will show as 29/Jan/2018

Note that the format does NOT change the value of the cell, just how the cell is displayed.

Let me know if that helps or if you need more info.

  • thx, how do i input the custom format? Just a background story of my problem here, so i am using this online excel from my onedrive, and suddenly the date format changed to American. Its fucked up all the dates stored in the cells (we use dd/mm/yyyy, but the American format uses mm/dd/yyy). I tried to change it to my country's format, but whenever i put a date in any date-formatted cell it would swap the day and the month (e.g. if i typed 01/02/2018, it'll change to 02/01/2018). Thats why I asked this question, maybe its safer to store them in text format. – Agil GA Nov 22 '18 at 8:09
  • No, don't store dates as text. That will even aggravate the situation. If you use Excel online, select the date cells and format them as you want them to display. If you want to remove ambiguity when entering dates, use 1-Feb-2019 or 2-Jan-2019 when you enter a date. That will make sure that the cell value will be the date you intend. – teylyn Nov 24 '18 at 5:20
0

This method should work without using VBA. Lets say your dates are in column A.

  • add a helper column to the sheet. Lets say it is column B.

  • add the formular =TEXT(A1,"dd/MM/yyyy") into cell B1, and fill the column downwards. (Adapt the format string if required.)

  • format column C as text

  • use "copy values" to copy the content of column B to column C

Now you can delete column B (and if you like, column A as well).

Not the answer you're looking for? Browse other questions tagged or ask your own question.