Forum Discussion
Converting string of numbers to date but get wrong date
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 ...
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.