AM /PM formula help please


G

Guest

A B
10:29 AM
11:30 AM
18:05 PM
14:45 PM
0:37 AM

I would like to create a formula in column B which looks at column A
and answers the question ..... AM or PM . I have tried some IF formulas
but with no success. Column A is formated in military time (i.e 13:30). I
know I can simply format Column A differently but that wont help me with
further sorting I need to do at a later time.

Any help with this formula would be greatly appreciated.
 
Ad

Advertisements

E

Earl Kiosterud

Ferde,

=IF(A2<=0.5, "AM", "PM")

You can change <= to < if you want 12:00 noon to be considered AM.

This is based on the fact that date-time values have the time in the fractional part. 0.5
is exactly half of a day, i.e.: 12:00 noon. It doesn't matter what the time formatting.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
R

Ron Rosenfeld

A B
10:29 AM
11:30 AM
18:05 PM
14:45 PM
0:37 AM

I would like to create a formula in column B which looks at column A
and answers the question ..... AM or PM . I have tried some IF formulas
but with no success. Column A is formated in military time (i.e 13:30). I
know I can simply format Column A differently but that wont help me with
further sorting I need to do at a later time.

Any help with this formula would be greatly appreciated.


=RIGHT(TEXT(A1,"h:mm am/pm"),2)


--ron
 
G

Guest

Thank you so much for the reply.... it not only works and now I can
understand how and why for the future.
 
R

Rick Rothstein \(MVP - VB\)

=RIGHT(TEXT(A1,"h:mm am/pm"),2)

Unless I am missing something, I believe you can shorten that to this...

=TEXT(A1,"am/pm")

Rick
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top