For clock-in time in A2 and clock-out time in B2, calculate attendance duration with =MOD(B2-A2,1) and format the result as [h]:mm. The formula handles shifts that cross midnight; subtract an unpaid break duration as well when you need net working time.
The basic attendance time formula
Excel stores times as fractions of a 24-hour day. Subtracting a start time from an end time therefore returns the elapsed portion of a day: for example, eight and a half hours is stored as 8.5 divided by 24. The MOD function makes the subtraction work for a clock-only overnight shift by returning the remainder within one day.
With the start time in A2 and the end time in B2, enter this in C2:
=MOD(B2-A2,1)
If A2 is 8:15 AM and B2 is 4:45 PM, the result is 8:30, or eight hours and 30 minutes. Format C2 as [h]:mm so Excel displays the elapsed time as hours and minutes. The formula returns a time value, not text, so you can add it to other durations or use it in further calculations.
Set up times as real Excel values
Enter clock times in separate columns, with one attendance record per row. A simple layout is employee name in column A, clock-in time in B, clock-out time in C, and calculated duration in D. For example, enter 8:15 AM and 4:45 PM as times, not as words such as eight fifteen. Excel usually recognizes common time entries automatically, but check the cell alignment and number format if an entry behaves like text.
For consistency, apply a time format to the clock-in and clock-out columns. Select the cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose the Number tab, then Time and an appropriate display. Excel for the web has a similar Format Cells pane, though its controls may appear in a side panel. Formatting changes how a value is shown; it does not convert text into a valid time value.
To enter a time in the afternoon, you can type a value such as 4:45 PM or use the 24-hour form 16:45. Be consistent within a worksheet, particularly when several people enter attendance data. A value displayed as 8:00 could mean morning or evening depending on the underlying value and format, so use a clear convention when recording punches.
Calculate overnight shifts
When the entries contain only clock times, ordinary subtraction can return a negative value if a shift ends after midnight. For instance, a shift from 10:00 PM to 6:00 AM crosses the day boundary. With the start time in A2 and the end time in B2, use =MOD(B2-A2,1); the result is 8:00.
This is a practical solution when every shift is shorter than 24 hours. Because the cells contain times of day but no dates, Excel cannot tell whether an end time belongs to the next day or whether the shift lasted more than one full day. For longer periods or records spanning dates, enter full date-and-time values instead.
If A2 contains the complete start date and time and B2 contains the complete end date and time, calculate elapsed time with:
=B2-A2
For example, a start value of 3 June at 10:00 PM and an end value of 4 June at 6:00 AM produces 8:00. Do not apply MOD to date-and-time values when a duration might exceed 24 hours: wrapping the result within one day would discard the extra days. Use the same [h]:mm display format to show durations longer than a day, such as 27:30.
Subtract unpaid breaks
To calculate net attendance time, subtract the break duration from the elapsed shift. Put the start time in A2, the end time in B2, and the unpaid break duration in C2. Then enter this formula in D2:
=MOD(B2-A2,1)-C2
For a shift from 8:15 AM to 4:45 PM with a 30-minute unpaid break, the elapsed time is 8:30 and the net duration is 8:00. Enter the break as an Excel duration, such as 0:30, rather than as the number 30; a plain 30 is interpreted as 30 days in a time calculation. A blank break cell is treated as zero by this subtraction.
If records may be incomplete, avoid showing a calculated duration before both punches have been entered. With start in A2, end in B2, and break duration in C2, this guarded version returns a blank until both time cells have values:
=IF(OR(A2="",B2=""),"",MOD(B2-A2,1)-C2)
Copy the formula down the attendance table. Check that the break is not longer than the shift; otherwise the net result will be negative. Also decide whether the break column represents unpaid time only. Subtracting paid breaks would reduce the calculated attendance total even when the organization intends to count that time.
Show totals as hours or decimal values
For a daily timesheet, hours and minutes are often easiest to review. Format the duration column as [h]:mm. The square brackets tell Excel to keep accumulating hours rather than restarting the hour display at 24. Without brackets, a total of 27 hours and 30 minutes can display as 3:30, which looks like a much shorter duration.
Some payroll or reporting systems request decimal hours. With start time in A2 and end time in B2, calculate decimal hours using:
=MOD(B2-A2,1)*24
A duration of 8:30 becomes 8.5 hours because 30 minutes is half an hour. If the break in C2 should be excluded, use:
=(MOD(B2-A2,1)-C2)*24
For the earlier example, an 8:30 shift less a 0:30 break returns 8.0. Format the decimal result as Number with a suitable number of decimal places. Do not multiply by 24 and then format the result as a time; choose either a time duration or decimal hours according to the destination report.
Round durations carefully
Rounding is a separate decision from calculating elapsed time. First calculate the actual duration, then apply only the rounding method required for the report or workplace process. For example, if you need to display a duration rounded to the nearest 15 minutes, and start, end, and break are in A2, B2, and C2, use:
=ROUND((MOD(B2-A2,1)-C2)*96,0)/96
There are 96 fifteen-minute intervals in a day, so multiplying a time value by 96 expresses it in quarter-hour units; rounding to a whole unit and dividing by 96 converts it back to a time value. A net duration of 8:07 rounds to 8:00, while 8:08 rounds to 8:15. This example rounds the total duration, not the individual clock punches. Rounding rules can affect recorded pay time, so use the applicable workplace policy and requirements rather than assuming this example is appropriate for payroll.
Add attendance across days
Calculate each day’s duration in its own row, then total the duration column. If daily results are in D2:D8, enter this in D9:
=SUM(D2:D8)
Format D9 as [h]:mm. For example, daily values of 8:00, 7:30, 8:15, 8:00, and 7:45 sum to 39:30. The total is a duration, not a time of day, so a standard clock format can wrap the display after 24 hours. If you need a decimal total and the duration sum is in D9, use =D9*24 in another cell; 39:30 becomes 39.5 hours.
When using a date-and-time layout, calculate each row’s duration by subtracting the full start timestamp from the full end timestamp, then sum those results. Keep dates with their punches so that a shift crossing midnight is unambiguous. A consistent row-per-shift design also makes it easier to identify missing punches and compare one day with another.
Common errors and how to fix them
- The result shows a time such as 12:00 AM. The result cell may be formatted as a clock time or as a date. Apply a duration format such as
[h]:mm. - The total looks too small after 24 hours. Use
[h]:mm, not a format that resets the hour display at 24. - The result is negative or shows hash marks. Check that the entries are valid times and that the formula matches the data. For clock-only overnight shifts, use
MOD; for timestamp values, use end minus start and include the correct dates. - A break subtraction gives an unexpected answer. Store the break as a duration such as 0:30, not as 30 minutes entered as the number 30. Confirm that the break column is not text.
- The result is zero or a formula appears as text. Check for an apostrophe or text formatting in the formula cell, and verify that the source entries are actual Excel time values rather than text strings.
When diagnosing a questionable result, temporarily format the source and result cells as General or inspect them in the formula bar. A time value is stored numerically even though it is displayed as a clock reading. If an imported attendance file stores punches as text, convert the entries into recognized Excel times before relying on subtraction; changing the display format alone may not repair the underlying data.
Choose the formula for your records
The right formula depends on how the punches are stored. Use clock times plus MOD for simple shifts that may cross midnight. Use full date-and-time stamps plus direct subtraction when dates are available or when elapsed time can exceed 24 hours. Add a break column only when you need net time, and multiply the result by 24 only when a report requires decimal hours.
Before filling a formula down, test it against a few known examples: a daytime shift, an overnight shift, and a shift with a break. Confirm the duration and the display format separately. Excel may calculate the right numeric duration while showing a confusing result because of formatting, or show a plausible-looking number when the underlying punches were entered incorrectly.
Frequently Asked Questions
What is the formula for hours worked between two times in Excel?
With start time in A2 and end time in B2, use =MOD(B2-A2,1) for clock-only punches, then format the result as [h]:mm. If the entries include full dates and times, use end minus start instead.
How do I calculate attendance time when a shift ends after midnight?
For a shift recorded as clock times only, use =MOD(B2-A2,1), where A2 is the start and B2 is the end. If the cells include dates, enter the next calendar date with the end punch and subtract the full timestamps.
How do I deduct a 30-minute lunch break?
With start in A2, end in B2, and the break duration in C2 as 0:30, use =MOD(B2-A2,1)-C2. Format the result as [h]:mm; the example break value is a duration, not the number 30.
How can I convert attendance time to decimal hours?
With a clock-in time in A2 and clock-out time in B2, use =MOD(B2-A2,1)*24. For an 8:30 duration, the result is 8.5. Subtract any unpaid break before multiplying by 24.
Why does my Excel total reset after 24 hours?
The cell is probably using a clock-time format that wraps at one day. Apply the custom duration format [h]:mm to the total so that Excel displays accumulated hours, such as 27:30, instead of 3:30.
Related
- Excel Export CSV Comma or Semicolon? Change the Delimiter
- Excel Export to CSV: Options and What They Mean
- Excel Export CSV: Can a CSV Have Multiple Sheets?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Arguments: What Each One DoesExcel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and…
- Excel Filter Duplicates: Keep the First RecordTo keep the first occurrence of each value in Excel, select the full data range and choose Data…
- Excel Formula for Date Minus Date: Days BetweenThe core Excel formula for subtracting one date from another is simply =B2-A2, where B2 holds the later…
- Excel Workday Difference Between Two DatesIn Excel for Microsoft 365 on Windows, use =NETWORKDAYS(A2,B2) to count working days from the start date in…
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- Concat Excel Cells with Dates: Formulas, Fixes, and TipsTo concatenate text and a date in Excel, use the TEXT function to format the date before joining…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.