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:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
barry houdiniConnect With a Mentor Commented:
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
0
 
h3nnysCommented:
could you upload the sourse file so that I can format it and see where the issue lies
0
 
Seamus2626Author Commented:
here is the code that is creating the data in G+H

Thanks
Seamus
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
 
MINDSUPERBConnect With a Mentor Commented:
Use this formula in G2 and H2
=VALUE(LEFT(C2,9))

Format Column G into Date and Column H into Time.

Sincerely,

Ed
0
 
MINDSUPERBCommented:
It must be only =VALUE(C2)

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


Ed
0
 
sah18Connect With a Mentor Commented:
There are several ways to accomplish this.  I've attached one possible solution.
Example-with-corrected-date-time.xls
0
 
barry houdiniCommented:
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
0
 
Seamus2626Author Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.