Formating Large number as date

  • Thread starter Thread starter BDT
  • Start date Start date
B

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.
 
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
 
=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
 
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.
Click to expand...

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
 
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
 
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.
 
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
Click to expand...

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
 
Thanks everybody, your suggestions worked fine and I am able to format my
sheet fine.

I appreciate the help. BDT
 
Back
Top