Odd number labeled as Date and Time

G

Guest

I have a column in a spreadsheet that I was given. some of the values
read like this:

130539061305 or when not in the cell, like this: 1.30539E+11

I found this formula from someone who answered a post, and it works to
change them to date and time:

=DATE((RIGHT(A1,2)+2000),MID(A1,7,2),MID(A1,9,2))+(LEFT(A1,2)& ":"
&MID(A1,3,2)& ":" &MID(A1,5,2))+0

The issue is that I also have some that are formatted like this:

95241022205

The formula is not working for this, and others are not as well. Can
anyone help?

Thanks!
 
C

CLR

The problem appears to be the number of characters in your date/numbers.
Your new number 95241022205 appears to be missing a leading zero......change
it to 095241022205 and it works fine with your original formula.


Vaya con Dios,
Chuck, CABGx3
 
G

Guest

I have to say, I keep coming back to this post (mostly to grab my
formula lol) but your help has been so useful to me. Thank you for
taking the time to answer my question and help me out.
 
C

CLR

You're very welcome, and thank you for your kind words. It's very nice also
when someone takes the time to express their appreciation.

Vaya con Dios,
Chuck, CABGx3
 
Top