Tom’s Tutorials For Excel: Verifying a Time as AM or PM

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
9 comments on “Tom’s Tutorials For Excel: Verifying a Time as AM or PM
  1. Zafar Quaseem says:

    Many Thanks

  2. Richard says:

    Thanks for this – an eloquent and simple solution!

  3. Peter says:

    …but 12:00:00 PM is then given as “AM”!

  4. Chip says:

    Thank you so much. Helped me big time.

  5. Syd Salmon says:

    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”)

  6. EPSI says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

*