Formula Errors

How to Classify AM/PM Attendance Entries in Excel

Ayan Masood Oct 13, 2026 869 views

Question details

The user needs to classify employee attendance time logs sequentially into specific shifts: AM IN, AM OUT, PM IN, and PM OUT.

How to Classify AM/PM Attendance Entries in Excel

Product: Excel
Device & OS: not provided
Scenario: Processing employee time logs where multiple timestamps per day need to be automatically labeled to distinguish between morning and afternoon check-ins and check-outs.
Observed behavior: Users require a reliable formula that tracks the chronological order of time punches for each employee on a given date to assign the correct shift label, avoiding complicated nested IF statements for noon-range overlaps.

Before you start

Ensure your attendance data is sorted chronologically by date and time, and formatted as an Excel Table to ensure structured references update automatically.

Solution 1Recommended

Use CHOOSE and COUNTIFS for Sequential Attendance Tracking

This method dynamically assigns shift labels based on the occurrence count of an employee's time punch for a specific date.

Instead of complex nested IF statements that rely on specific time ranges, you can count how many times an employee's date entry appears up to the current row. The CHOOSE function then assigns the corresponding shift label based on this count (the first punch is AM IN, the second is AM OUT, etc.).

Format data as an Excel Table

Select your attendance data range, navigate to 'Insert' > 'Table' (or press Ctrl+T), and ensure your date column is named 'DATE'.

Apply the sequential formula

In a new column next to your time logs, enter the following formula: =CHOOSE(COUNTIFS(Table1[[#Headers],[DATE]]:[@DATE],[@DATE]),"1. AM IN","2. AM OUT","3. PM IN","4. PM OUT").

Account for multiple employees

If tracking multiple staff members, add the Employee ID or Name column as an extra condition to the COUNTIFS function, like this: =CHOOSE(COUNTIFS(Table1[[#Headers],[DATE]]:[@DATE],[@DATE], Table1[[#Headers],[EmpID]]:[@EmpID],[@EmpID]),"1. AM IN","2. AM OUT","3. PM IN","4. PM OUT").

Apply to all rows

Press Enter and drag the fill handle down to apply the formula to the entire column, automatically classifying all time entries.

Use CHOOSE and COUNTIFS for Sequential Attendance Tracking

Data Sorting Requirement: For this formula to assign the labels in the correct sequence, your time logs must be sorted from earliest to latest for each date and employee.

Automate Timesheets with WPS Spreadsheet

You can easily track, calculate, and classify complex attendance logs using WPS Spreadsheet. It fully supports advanced Excel functions like CHOOSE and COUNTIFS to manage employee schedules efficiently without compatibility issues.

1. Open your attendance data: Launch WPS Spreadsheet and open your employee time log file.
2. Format as Table: Highlight your dataset and press Ctrl+T to convert it into a structured Table.
3. Apply the CHOOSE formula: Input the CHOOSE and COUNTIFS formula into the classification column to instantly generate your AM/PM labels.

100% compatible with Microsoft Excel formulas and functionsFree and lightweight spreadsheet tool for data analysisBuilt-in templates for attendance and time trackingSupports large datasets and complex conditional formatting

microsoft office alternative - wps office

Frequently Asked Questions

Why does the CHOOSE function return a #VALUE! error?

This error occurs if the COUNTIFS formula returns a number greater than the options provided in the CHOOSE function. For instance, if an employee clocks in 5 times but you only defined 4 labels, it will error out. Ensure employees do not have duplicate or accidental time punches.

Can I use nested IFs instead of the CHOOSE function?

Yes, you can use nested IF formulas based on specific time values (e.g., IF time < 12:00 PM). However, CHOOSE with COUNTIFS is much more reliable for sequential punches, especially when an AM checkout and PM check-in both occur around noon.

How do I calculate total working hours after classifying the entries?

Once your entries are classified as IN and OUT, you can use a Pivot Table or the SUMIFS function to separate the data, then subtract the 'IN' time from the 'OUT' time for each employee per day.

Excel & Data Problems