Converting string of numbers to date but get wrong date

Copper Contributor

I am trying to sort a sheet by dates but first I need to convert a string of numbers to date format. When I do that I get a date format but the date is all wrong. Example: I have 020818 which stands for ddmmyy and I want the outcome to be "2. august 2018" in icelandic format. But when I change cell format to date the result is 29. desember 1956. what can I do?

1 Reply

@AnnaErla 

Excel internally stores dates as numbers. For example, today (31 May 2022) is stored as 44712 (roughly the number of days since the beginning of the 20th century).

The number 20818 corresponds to  29 December 1956, as you have found. So simply changing the number format won't work.

You can use a formula in another cell instead. For example, with values such as 020818 in A2 and down, enter the following formula in B2, then fill down:

=DATEVALUE(TEXT(Y16;"00\/00\/00"))

Format the cells with the formulas as a date.