Calculate Work Hours from First and Last Punch in Excel
Question details
Calculate daily work duration from the first and last punches while ignoring intermediate punches and flagging missing punches.

- 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.
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.
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.
Enter =IF(TRIM(A2)="","",IFERROR(TIMEVALUE(TRIM(LEFT(A2,FIND(",",A2&",")-1))),"")) in B2.
Enter =IF(OR(TRIM(A2)="",ISERROR(FIND(",",A2))),"",IFERROR(TIMEVALUE(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)))),"")) in C2.
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.
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 when start and end punches are in separate cells
Use this approach when the first punch is already in I4 and the last punch is in J4.
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. 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. Extract the first punch: In B2, enter =IF(TRIM(A2)="","",IFERROR(TIMEVALUE(TRIM(LEFT(A2,FIND(",",A2&",")-1))),"")).
- 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. 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. 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.

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.




