Excel Number Formatting

[ljg]

Excel XP - I have a cell with the following text:
20060421162529
currently formatted as "number." The text is actually date and time in the following format:
yyyymmddhhmmss
However any attempt in formatting that cell as a date/time field gives me the output of:
#############################

Can anyone assist?

Thx,
LJG

 

[coachdan]

I am assuming you have tried resizing the cell to make sure it isn't just too small to display the results?

coachdan32

http://www.malehsbaseball.com

 

[ljg]

Yes - the problem is formatting. Open an excel worksheet and type in the number string I indicated. When format is "general" the text will look like this:
2.00604E+13
I changed the cell format to "number" to get the correct information pulled from the event log: 20060421162529.
However I'd like to see that in date/time format - and I don't know how to do so, if it's even possible.

Thanks
LJG

 

[AnotherHiggins]

For a quick overview of how excel deals with dates and times, see faq68-5827, Why do Dates and Times seem to be so much trouble?.

Excel will see 20060421162529 as 20,060,421,162,529
days after 1/1/1900. Just to give you an idea of what we're talking about here, Dec31 of the year 9999 is represented as 2,958,465 in Excel. So you are getting the #'s because the number 20,060,421,162,529 as a date is hurting Excel's little head.

To represent that STRING as a date, use something like
[tab][COLOR=blue white]=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.