• Status: Solved
• Priority: Medium
• Security: Public
• Views: 270

# 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.
0
MeowserM
• 3
• 2
• 2
• +1
1 Solution

ConsultantCommented:
To get the time:

=A1-TRUNC(A1)

Kevin
0

Commented:
Why don't you just format the entire column to Time?
Capturar.JPG
0

Commented:
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

15:30
0

Author Commented:
Thank you.  I was over thinking it......
0

Commented:
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.
0

Commented:
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
0

Author Commented:
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!
0

Commented:
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.   :)
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.