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

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
Asked:
MeowserM
  • 3
  • 2
  • 2
  • +1
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
To get the time:

=A1-TRUNC(A1)

Kevin
0
 
jppintoCommented:
Why don't you just format the entire column to Time?
Capturar.JPG
0
 
jppintoCommented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MeowserMAuthor Commented:
Thank you.  I was over thinking it......
0
 
jppintoCommented:
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
 
pony10usCommented:
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
 
MeowserMAuthor 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
 
pony10usCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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