# 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.

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
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.

Ron Rosenfeld said:
=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

R

#### Ron Rosenfeld

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

=TEXT(A1,"am/pm")

Rick

Good. I never tried that.
--ron