0

So I have this data - November 9, 2000 in Excel (the column type is Date). After hours of trying I cannot format it to something like 09.11.2000. I've tried:

  1. Column to text transformation
  2. Format cells

Please help

user2620644
  • 521
  • 1
  • 12
  • 25
  • What locale is your machine in? Per default, when formatting a cell, excel only displays date formats used in the default locale. Select for instance "German" as a locale for that cell, then it will let you select your desired format ... – derpirscher Apr 29 '23 at 14:45
  • Or you can always do a "userdefined" format and use `DD.MM.YYYY` as a formatstring, if you have the english version of excel. If you are in any other language consult the docs, which placeholders are appropriate in your language ... – derpirscher Apr 29 '23 at 14:48
  • https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e – derpirscher Apr 29 '23 at 15:04

2 Answers2

0

Assuming the cell is correctly identified as Date, right-click on the cells and select Format Cells, then click on Custom category which you can pass in dd.mm.yyyy as the value to display the value in the format as you have requested.

AngYC
  • 3,051
  • 6
  • 20
0

It sounds like your date is currently in text format. Setting the column type to 'Date' won't affect the display of text, even if it looks like a date.

Unfortunately, Excel can't directly interpret "November 9, 2000" as a date.

Three steps are needed:

  1. Convert the text date into a format which Excel can interpret as a date, such as "9 November 2000".
  2. Apply Excel's VALUE() function to convert the text date to a real date (which means a number in Excel).
  3. Apply date formatting to display the date in your desired format.

To achieve steps #1 and #2 in one go, you could apply the following formula (replace each instance of A1 with the cell containing your text date):

=VALUE(INDEX(TEXTSPLIT(A1, {" ",","}), 2) & " " & INDEX(TEXTSPLIT(A1, {" ",","}), 1) & " " & INDEX(TEXTSPLIT(A1, {" ",","}), 4))

To explain how this works:

  1. TEXTSPLIT(A1, {" ", ","}) splits the input date into an array of four elements, splitting on every space and comma. The four elements are November, 9, a blank element and 2000.
  2. INDEX(..., n) selects the nth element of an array. We ask for elements 2, 1 and 4, in that order (i.e. 9, November and 2000).
  3. We then join with spaces to give the text string 9 November 2000.
  4. Excel's VALUE() function then converts this text to a real date (i.e. the Excel number 36839).

Finally, to display the date as required (step #3 above), select the cell and apply custom formatting of dd.mm.yyyy.

(Note that if you are using locale settings other than English, some of the above steps may not work.)

This approach will work for any dates in the same format as "November 9, 2000". If you have dates in the same column in a different format, you might need to apply additional logic.

Neil T
  • 1,794
  • 1
  • 12
  • 21