asked on

Excel Date and time

Hello:

I have a database dump in excel that has a field with date and time in the format 2/27/11 13:21. I am looking for a formula that will just give me the time. In this example 13:21. I am trying to determine if the record was created before or after 5 PM each day.

Any ideas are appreciated.

Thank you.

zorvek (Kevin Jones)

To get the time:

=A1-TRUNC(A1)

Kevin

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

On the formula that zorvek provided, don't forget to format the cells also like Time or you will get something like this:

0.645833333

instead of this:

15:30

MeowserM

ASKER

Thank you. I was over thinking it......

jppinto

Warning, the solution you accepted will show you just the time part but the real cell value will remain with the date and time! If you want to get just the time value as the real value, then zorvek's solution is the right one.

Steven Carnahan

Just for a fun exercise I took what both zorvek and jppinto provided and created a spreadsheet. I placed the time you want to compare to (17:00 or 5 PM) in Z1, zorvek's solution in column B, C and Z and a formula that I created in column C to determine if it was before 5 PM. You can place any date/time using the format that you provided in Colmun A and see what it does.

sample.xls

MeowserM

ASKER

pony10us:

Thank you for your sample. It is exactly what I am looking to do. I wish I could award points!

Thank you very much!

Steven Carnahan

Glad I could help. I wasn't in it for the points. I just wanted to see what I could do with what you had the parameters you laid out in your original question. It was a fun excercise and I couldn't have done it without the input of zorvek and jppinto so they deserve the points anyway. :)