Counting entries in an Excel spreadsheet by AM or PM

Microsoft Excel supports formatting timestamps in a workbook as a date followed by a time with an AM or PM value, i.e., the times are in 12-hour clock format where noon is 12:00 PM and midnight is 12:00 AM. You can select that format for a cell or column in a worksheet by highlighting the cell(s) or column (a column can be selected by clicking on the letter at the top of the column) and then clicking on Format then Cells and then clicking on Date and selecting a type of "3/14/15 1:30 PM."

Excel Format Cells - Date and Time

If you wanted to extract just the hour from the timestamp, you can use the HOUR function, e.g. =HOUR(A1) to extract the hour value from cell A1. The hour will be displayed in 24-hour clock format, aka military time, i.e., 7:00 AM is 7 while 7:00 PM is 12 plus 7, i.e., 19; 12:00 AM (midnight) is 0 and 12:00 PM (noon) is 12. For instance, give timestamps in column A with the time displayed as AM or PM values and using the HOUR function in a formula for the cells in column B, I would see the following:

Excel: Learn basic and advanced formulas quick and easy
Excel: Learn basic and advanced
formulas quick and easy

  A B
17/1/17 1:30 PM13
27/7/17 7:42 AM7
37/12/17 2:40 PM14
47/13/17 4:45 PM16
57/14/17 12:00 AM0
67/15/17 8:20 AM8
77/16/17 12:00 PM12

Excel - column B hour formula

To count the number of cells that have an AM value and the number of cells that have a PM value, you can use an array formula. Again, assuming that column A holds the time stamps and as in the example above, cells A1 through A7 contain the timestamp entries, I could use the following formulas:


Note: you have to hit control-shift-enter, i.e., hit the control, shift, and enter keys simultanesouly, after typing the formulas to make them array formulas. When you do so, you will see the formulas get curly brackets, aka braces, around them as shown below:


For the example above, you would see the following:

Excel array formula

Example workbook: Count_By_AM_PM.xlsx


  1. Guidelines and examples of array formulas
    Microsoft Office help and training - Office Support
  2. MS Excel 2003: Use an array formula to count ranges of times
    Excel Tutorial