Microsoft Excel
Microsoft Excel

Formatting

Can anyone formatt Col G+H? I have tried but am failing

Just want them in date and time format

Thanks
Seamus
Example.xls

Seamus2626Asked: 2011-02-23

Who is Participating?

LVL 50

barry houdiniCommented: 2011-02-23

Hello Seamus,

To get the date in G2 use

=INT(C2)

and for the time in H2

=MOD(C2,1)

format in date and time format respectively

regards, barry

LVL 4

h3nnysCommented: 2011-02-23

could you upload the sourse file so that I can format it and see where the issue lies

Seamus2626Author Commented: 2011-02-23

here is the code that is creating the data in G+H

Thanks
Seamus

LVL 19

MINDSUPERBCommented: 2011-02-23

Use this formula in G2 and H2
=VALUE(LEFT(C2,9))

Format Column G into Date and Column H into Time.

Sincerely,

Ed

LVL 19

MINDSUPERBCommented: 2011-02-23

It must be only =VALUE(C2)

instead of =VALUE(LEFT(C2,9))

Ed

LVL 9

sah18Commented: 2011-02-23

There are several ways to accomplish this. I've attached one possible solution.
Example-with-corrected-date-time.xls

LVL 50

barry houdiniCommented: 2011-02-23

Using LEFT(C2,9) is a mistake, I think. That would be fine if C2 was a text value.....but it's not so by extracting the first 9 characters you are actuallt taking the date serial number (5 digits) plus decimal point and then 3 decimal places. If you use VALUE function on that you can format as a date but the underlying value still contains the decimals (effectively a time) which might cause problems if you want to do other calculations with the value returned.

INT, as per my suggestion, just takes the integer portion of C2 (the date) and MOD(C2,1) takes the decimal part (the time)

regards, barry