Turning numbers into Dates

  • Thread starter Thread starter Kbass
  • Start date Start date
K

Kbass

I would like to know how to turn a number such as "51319" into a date such as
"5/13/19". As of now, when I try to format the cell to a date format, it
completely changes the date from "52309" to a random date such as "7/11/35".
I have no idea why. Any ideas?
 
Changing the format of the cell doesn't change its value, but only the
method in which the value is displayed.
52309 is 52309 days from 0 Jan 1900, so is 19 March 2043.
If you want to change the number into a date, try =--TEXT(A20,"00\/00\/00")
and format the result as a date. That will work, providing that your
windows regional options use the same convention as you have used for short
date, which looks like m/dd/yy in your case.
 
That's OK providing that you just want to display it that way, and not
calculate as dates.
 
That doesn't change the underlying value of the cell. It's not a date it's
still the same number just with the slashes.
turn a number such as "51319" into a date such as "5/13/19".
Click to expand...

Ok, so what year is that? Is it 1919 or 2019?

Try this...

Select the cell in question
Goto the menu Data>Text to Columns
Click Next twice
In Step 3 of the wizard, in Column data format, select Date and select MDY
from the frop down.
Click Finish

Excel will interpret the year 19 as 2019

Then you can custom format as desired.
 
Back
Top