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.

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...
-----------------------------------------------------------------------
"ferde" wrote in message
...
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.

On Sun, 2 Sep 2007 17:06:00 -0700, ferde
wrote:

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

Thank you.


"Ron Rosenfeld" wrote:

On Sun, 2 Sep 2007 17:06:00 -0700, ferde
wrote:

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

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

"Earl Kiosterud" wrote:

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...
-----------------------------------------------------------------------
"ferde" wrote in message
...
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)

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

=TEXT(A1,"am/pm")

Rick

On Sun, 2 Sep 2007 22:55:38 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

=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

Good. I never tried that.
--ron