Tom’s Tutorials For Excel: Verifying a Time as AM or PM
Here’s how you can verify a cell’s time value as either morning (AM), or afternoon or evening (PM). This is useful if you encounter a cell whose formatting does not show the time itself, such as in the pictured example below.
The formula in cell B2 is =IF(MOD(A2,1)>=0.5,"PM","AM").
The “>0.5” portion of the formula sets the condition of the half-day mark, that being 12:00 noon, which separates the AM and PM halves of the 24-hour day.



Many Thanks
Thanks for this – an eloquent and simple solution!
…but 12:00:00 PM is then given as “AM”!
Thanks. I just edited the page to show the formula as
=IF(MOD(A2,1)>=0.5,"PM","AM")Thank you so much. Helped me big time.
Thanks, Chip!
A more intuitive alternative is `=TIMEVALUE()`.
TIMEVALUE(00:00) = 0
TIMEVALUE(06:00) = 0.25
TIMEVALUE(12:00) = 0.5
TIMEVALUE(18:00) = 0.75
Therefore, =IF(TIMEVALUE(A1)>=0.5,”PM”,”AM”)
LOGIN TIME SHIFT
Between 06: 30 AM and 07: 05 AM TOO EARLY
Between 15: 30 PM and 16: 05 PM TOO EARLY
Between 23: 30 AM and 00: 55 AM TOO EARLY
Between 07: 06 AM and 07: 10 AM EARLY
Between 16: 06 PM and 16: 10 PM EARLY
Between 0: 56 AM and 01: 00 AM EARLY
Between 07: 11 AM and 07: 15 AM ON TIME
Between 16: 11 PM and 16: 15 PM ON TIME
Between 01: 01 AM and 01: 05 AM ON TIME
Between 07: 16 AM and 07: 55 AM DELAY
Between 16: 16 PM and 16: 55 PM DELAY
Between 01: 06 AM and 01: 55 AM DELAY
i NEED A FORMULA FOR THIS CAN ANYONE HELP ON THIS
It would help if you would say where the cells are that contain the in and out times, and what cells shall contain the formulas.