Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
=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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
AM /PM formula help please
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|