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

by Tom Urtis 7 Comments ↓

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.

‹ Tom’s Tutorials For Excel: Summing Every Nth Cell

Tom’s Tutorials For Excel: Summing Cells in Even or Odd Numbered Rows Only ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Data Science, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

7 comments on “Tom’s Tutorials For Excel: Verifying a Time as AM or PM”

1. Zafar Quaseem says:

   April 15, 2014 at 12:01 am

   Many Thanks

   Reply
2. Richard says:

   July 20, 2018 at 1:39 am

   Thanks for this – an eloquent and simple solution!

   Reply
3. Peter says:

   September 22, 2018 at 9:25 am

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

   Reply
   • Tom Urtis says:

     September 22, 2018 at 9:56 am

     Thanks. I just edited the page to show the formula as
     =IF(MOD(A2,1)>=0.5,"PM","AM")

     Reply
4. Chip says:

   December 1, 2018 at 8:14 am

   Thank you so much. Helped me big time.

   Reply
   • Tom Urtis says:

     December 1, 2018 at 12:09 pm

     Thanks, Chip!

     Reply
5. Syd Salmon says:

   September 13, 2022 at 8:15 am

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

   Reply