Excel numbers

  • Thread starter Thread starter gnola
  • Start date Start date
G

gnola

Does anyone know how to convert a number like 05251970 to
an actual date like 05/25/1970. I can not find a formula
to do this.

Thanks!
 
What is the format of the rest of the data?? You only have a single digit for
the month, so does that mean you only have single digits for days like the
7,8,9th etc?

For the example you have, it could be done with:-

=DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2,2))

But, if you have a date that has only a single digit for the day it will fail.
How would you differentiate between the following:-

1251970 - Is it the 25th Jan 1970, or the 5th Dec 1970??
 
If your "date" is a true number and always has 2 digits for month and 4 for
the year:

=DATE(MOD(C5,10000),INT(C5/1000000),INT(C5/10000)-INT(C5/1000000)*100)
 
Does anyone know how to convert a number like 05251970 to
an actual date like 05/25/1970. I can not find a formula
to do this.

Thanks!
Click to expand...


=DATE(MOD(A1,10^4),INT(A1/10^6),MOD(INT(A1/10^4),100))


--ron
 
One more:

=DATEVALUE(TEXT(A1,"00\/00\/0000"))

(format the cell as a date)

If you have a bunch of cells in a single column, you may want to look at
Data|Text to columns. You can specify mdy and do it all at once.
 
Why I thought you only had a single digit for the month I have no idea, but you
now have a whole plethora of working solutions to choose from.
 
Back
Top