Formating Large number as date

[BDT]

I have columns of numbers that represent date/time info. For instance I have
the number 20081029092245 which represents:

2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM

And I want it to display as one of the above formats.

I can parse the big number and concatenate the resulting columns, but is
there a less cumbersome way to do this with Format/Cells/Number/Custom or
something similar?

Many thanks.

 

[Dave Peterson]

You could use a helper column with a formula like:
=--TEXT(A1,"0000-00-00 00\:00\:00")

and give the column a numberformat of:
mmmm dd, yyyy hh:mm:ss
or
yyyy/mm/dd hh:mm:ss

 

[Gary''s Student]

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

rather than a re-format of A1.

Format the cell with the formula as Custom > mm/dd/yyyy hh:mm:ss

 

[Ron Rosenfeld]

I have columns of numbers that represent date/time info. For instance I have
the number 20081029092245 which represents:

2008/10/29 09:22:45 or October 29, 2008 at 9:22 AM

And I want it to display as one of the above formats.

I can parse the big number and concatenate the resulting columns, but is
there a less cumbersome way to do this with Format/Cells/Number/Custom or
something similar?

Many thanks.

No there is not. Formatting only affects how the data is displayed, not how it
is parsed on data entry.

With your big number in A1, you could use this formula to convert it to a value
Excel understands; and then format it however you like:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

OR, if this will be used on systems utilizing the US Date format, you could
convert it to a proper Excel date/time value with this formula:

=--TEXT(A1,"0000\-00\-00\ 00\:00\:00")

--ron

 

[BDT]

Thanks everybody.

The formula:

=--TEXT(A1,"0000-00-00 00\:00\:00")

worked fine when I deleted the 2 minus signs after the equal sign and the
other one:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

generated just the date which might be helpfull. But I couldn't get the
Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These
just return an infinite row of # signs as wide as you set the column width.

thanks again, BDT

 

[Dave Peterson]

The =text() will make the cell look like a date, but it's really text. You may
find that some of your date arithmetic may not work the way you want.

The -- stuff coerces the text that looks like a date to a real number. But
unless you apply a nice numberformat, you'll end up with something that looks
like:

39750.3908
or
39750.3907986111
when you look at lots of decimals.

Try changing the numberformat (format|cells|number tab in xl2003 menus) and
you'll probably be happier.

 

[Ron Rosenfeld]

Thanks everybody.

The formula:

=--TEXT(A1,"0000-00-00 00\:00\:00")

worked fine when I deleted the 2 minus signs after the equal sign and the
other one:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

generated just the date which might be helpfull. But I couldn't get the
Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These
just return an infinite row of # signs as wide as you set the column width.

thanks again, BDT

Your doing something unexpected, or your data is not as you describe.

The TEXT function, after you removed the double unary, will return a text
string and not a true date. So formatting will predictably not have any
affect.

The DATE function, generates both time and date -- you probably didn't format
the result it properly.

Getting an infinite row of # signs in response to your date/time formatting
will occur if there is a negative number in that cell. Possibly you only
removed one of the minus signs, or your value is somehow negative.

Post back with more details of the precise problem, as well as copies (using
the Windows copy/paste functions) of the information that is present in the
FORMULA BAR when you select the source cell and the formula cells.
--ron

 

[BDT]

Thanks everybody, your suggestions worked fine and I am able to format my
sheet fine.

I appreciate the help. BDT

 

[Ron Rosenfeld]

Thanks everybody, your suggestions worked fine and I am able to format my
sheet fine.

I appreciate the help. BDT

Glad you got it figured out.
--ron