Function Problems

How to Count AM and PM Shifts in Excel Using COUNTIFS

Bushra Parveen Sep 30, 2026 868 views

Question details

The user needs an Excel formula to count the number of employees working AM and PM shifts based on their start times, while specifically ignoring blank cells for employees who are not scheduled.

How to Count AM and PM Shifts in Excel Using COUNTIFS

Product: Excel
Device & OS: not provided
Scenario: Calculating daily employee schedule totals by categorizing shift start times into AM (before 12:00) and PM (12:00 or later).
Observed behavior: Requires a function that correctly evaluates time values and strictly excludes empty cells from being counted as morning shifts.

Before you start

Ensure that your shift schedule data is properly formatted as Time values (e.g., 08:00 AM or 13:00) rather than standard text so the counting formula can accurately evaluate the thresholds.

Solution 1Recommended

Use COUNTIFS and TIME Functions to Tally Shifts

Apply the COUNTIFS function to evaluate your shift time range against the 12:00 threshold, using an explicit criteria to ignore empty cells.

In Excel, blank cells are often evaluated as zero, which equates to 12:00 AM in time formatting. To prevent off-duty employees from inflating your AM shift count, the formula must explicitly require the cell to not be blank.

Identify your data range

Locate the column containing your employee shift start times. For this example, we will assume the times are listed in cells B2 through B20.

Enter the AM shift formula

Click on an empty cell where you want the AM total to appear. Type the formula =COUNTIFS(B2:B20,"<>",B2:B20,"<"&TIME(12,0,0)) and press Enter. This counts cells that are not empty AND contain a time before 12:00 PM.

Enter the PM shift formula

Select a different cell for the PM total. Type =COUNTIFS(B2:B20,">="&TIME(12,0,0)) and press Enter. This counts all cells with a time of 12:00 PM or later.

Adjust the range for your worksheet

If your data spans a different range (e.g., C5:C50), modify the "B2:B20" portion of both formulas to match your actual dataset layout.

Use COUNTIFS and TIME Functions to Tally Shifts

Why the PM formula doesn't need a blank check: Because empty cells default to 0 (which is less than 12:00 PM), they inherently fail the ">=" condition in the PM formula. Therefore, you only need to explicitly exclude blanks in your AM formula.

Manage Spreadsheets Efficiently

Easily Calculate Shift Times with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical functions like COUNTIFS and TIME. You can seamlessly calculate schedules, track employee hours, and format time cells without compatibility issues.

1. Open your schedule: Launch WPS Spreadsheet and open your employee schedule document.
2. Select the target cell: Click the cell where you want your shift totals displayed.
3. Apply the formula: Type the exact COUNTIFS formula provided above and press Enter to instantly calculate your AM or PM shifts.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasSupports advanced time, date, and logical counting functionsLightweight application with a clean, user-friendly interfaceBuilt-in formatting tools for customized schedule layouts

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning zero for all shifts?

This usually occurs if your shift times are stored as plain text rather than time values. Highlight your data, right-click, select 'Format Cells', and change the category to 'Time'. Re-enter the time values if they do not automatically update.

How can I count AM and PM shifts for a specific date only?

You can add date criteria to your COUNTIFS formula. If dates are in column A and times in column B, use =COUNTIFS(B2:B20,"<>",B2:B20,"<"&TIME(12,0,0), A2:A20, "10/25/2023") for the AM count of that specific day.

Can I use this formula to count shifts ending after midnight?

Counting shifts that cross midnight requires a different approach since the end time is numerically smaller than the start time. You would need to use a formula that adds a day (1) to the end time, such as =IF(End<Start, End+1-Start, End-Start), before applying your COUNTIFS criteria.

Excel & Data Problems