Calculation Issues

Calculate Work Hours from First and Last Punch in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 869 views

Question details

Calculate daily work duration from the first and last punches while ignoring intermediate punches and flagging missing punches.

Calculate Work Hours from First and Last Punch in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Daily punch records contain one or more times, and the user wants to apply morning and evening cutoff times.
Observed behavior
The desired result is a duration or a message identifying a missing morning or evening punch.
Before you start

Check whether each day's punches are comma-separated in one cell or already in separate start and end cells. These examples assume a same-day schedule, a noon morning cutoff, and a 5:00 p.m. evening cutoff.

Solution 1Recommended

Calculate hours from a comma-separated punch list

Use helper cells to extract the first and last entries and ignore any punches between them.

The example uses a comma-separated list of recognizable time strings in A2, such as 8:30 AM, 12:00 PM, 1:00 PM, 6:00 PM. The first and last entries must represent the start and end of the same workday.

1
Extract the first punch

Enter =IF(TRIM(A2)="","",IFERROR(TIMEVALUE(TRIM(LEFT(A2,FIND(",",A2&",")-1))),"")) in B2.

2
Extract the last punch

Enter =IF(OR(TRIM(A2)="",ISERROR(FIND(",",A2))),"",IFERROR(TIMEVALUE(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))),"")) in C2.

3
Calculate or flag the result

Enter =IF(TRIM(A2)="","No punches",IF(B2="","Morning Not Punched",IF(B2>=TIME(12,0,0),"Morning Not Punched",IF(C2="","Evening Not Punched",IF(C2<=TIME(17,0,0),"Evening Not Punched",C2-B2))))) in D2.

4
Format and copy the formulas

Select D2 and choose Home > Number Format > More Number Formats > Custom, then enter [h]:mm. Copy B2:D2 down for the remaining records.

Calculate hours from a comma-separated punch list
Check ambiguous or invalid records: A single punch cannot reliably establish which punch is missing. Invalid or unrecognized time strings can also appear as missing punches, so review flagged records and use time strings recognized by Excel.
Calculate punch durations in WPS

Calculate First-to-Last Punch Hours in WPS Spreadsheets

Use WPS Spreadsheets to extract the first and last comma-separated punches, check the cutoff times, and calculate the daily duration.

  1. 1. Open the punch list: Open your workbook in WPS Spreadsheets and place each comma-separated daily punch list in column A, starting at A2.
  2. 2. Extract the first punch: In B2, enter =IF(TRIM(A2)="","",IFERROR(TIMEVALUE(TRIM(LEFT(A2,FIND(",",A2&",")-1))),"")).
  3. 3. Extract the last punch: In C2, enter =IF(OR(TRIM(A2)="",ISERROR(FIND(",",A2))),"",IFERROR(TIMEVALUE(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))),"")).
  4. 4. Calculate the result: In D2, enter =IF(TRIM(A2)="","No punches",IF(B2="","Morning Not Punched",IF(B2>=TIME(12,0,0),"Morning Not Punched",IF(C2="","Evening Not Punched",IF(C2<=TIME(17,0,0),"Evening Not Punched",C2-B2))))).
  5. 5. Format and fill down: Select D2, open Format Cells, and apply the custom number format [h]:mm. Copy B2:D2 down for the other days.
Ignore intermediate punches when calculating daily hoursFlag punches that do not meet your morning or evening cutoffOpen and save Microsoft Excel XLSX filesUse familiar spreadsheet formulas and number formatting
microsoft office alternative - wps office

Frequently Asked Questions

How do I change the morning and evening cutoff times?

Edit TIME(12,0,0) and TIME(17,0,0) in the result formula. For example, TIME(18,0,0) sets the evening cutoff to 6:00 p.m.; a punch at or before that time is flagged.

Are lunch and other intermediate punches included in the calculation?

The formula ignores intermediate punches and subtracts the first punch from the last. It does not deduct unpaid breaks.

What happens if a record contains only one punch?

The list formula treats a single entry as the first punch and leaves the last-punch helper cell blank. Check the original record because one punch alone may not establish which punch is missing.

Can these formulas calculate an overnight shift?

No. The examples assume punches occur on the same day and use daytime cutoff rules. Overnight shifts need dates or a separate formula and different validation rules.

Why does a valid-looking punch show as missing?

TIMEVALUE must recognize the text as a time. Check the entry in A2 for invalid text or a time format incompatible with your spreadsheet's regional settings.