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