Excel Formula for Time Clock Hours In and Out by Shift

For clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as decimal hours, including a shift that crosses midnight. For example, 10:00 PM to 6:00 AM returns 8; subtract any unpaid break separately if needed.

How Excel Calculates Time

Excel stores a time as a fraction of a 24-hour day: noon is one-half, and 6:00 AM is one-quarter. Subtracting a start time from an end time therefore returns a fraction of a day, not a number of hours. Multiplying the difference by 24 converts it to decimal hours, which are usually convenient for totaling time or using in a payroll worksheet.

The formula uses MOD to handle the clock rolling from 11:59 PM to 12:00 AM. With A2 containing 10:00 PM and B2 containing 6:00 AM, the direct subtraction B2-A2 would be negative. MOD wraps that result into the positive part of a 24-hour day; multiplying by 24 gives 8. The same formula also works for an ordinary daytime shift, such as 9:00 AM to 5:30 PM, which returns 8.5 hours.

This approach assumes a shift is shorter than 24 hours and that the cells contain valid Excel times. If a shift can last 24 hours or longer, use actual dates as well as times; the date makes the elapsed time unambiguous.

Set Up a Basic Time-Clock Sheet

In a simple worksheet, use one row per shift and label the columns so entries are easy to check. For example:

A1: Clock In
B1: Clock Out
C1: Hours

Enter the start and end times in A2 and B2, then put the formula below in C2. In this example, A2 is 9:00 AM and B2 is 5:30 PM, so C2 displays 8.5. Copy the formula down for additional shifts; Excel adjusts the row references automatically.

With the start time in A2 and end time in B2, use =MOD(B2-A2,1)*24.

Enter times with a colon, such as 9:00 AM or 17:00. Excel may display an entry in a different but equivalent style depending on the cell format. To check what Excel understood, select the cell and inspect the formula bar. A time that was entered as text may not subtract correctly, so correct the input rather than trying to repair the result with a more complicated formula.

Subtract an Unpaid Break

If you record an unpaid break as decimal hours in C2, subtract it from the elapsed shift. With A2 as 10:00 PM, B2 as 6:00 AM, and C2 as 0.5 for a 30-minute break, the result is 7.5 hours. This treats the break length as a number of hours, not as a time of day.

With clock-in in A2, clock-out in B2, and break length in decimal hours in C2, use =MOD(B2-A2,1)*24-C2.

Be consistent about how break time is entered. If workers enter a break duration as 0:30, Excel stores it as a fraction of a day; subtracting it directly from decimal hours would mix units. Convert that duration to hours by multiplying it by 24. With C2 containing a break duration entered as an Excel time, use =MOD(B2-A2,1)*24-C2*24. For a 30-minute break, the result is again 7.5. The decimal-hours method is generally simpler for a timesheet intended to total hours.

A break that has its own clock-in and clock-out pair is a separate interval. Calculate the break length from those two times, then subtract the resulting duration from the shift total. Verify that your sheet’s break-entry method matches the organization’s timekeeping rules; the spreadsheet formula does not decide which breaks count as paid time.

Choose the Right Formula for Overnight and Long Shifts

For shifts that may pass midnight but are always under 24 hours, the MOD formula is compact and practical. It assumes that an end time earlier than a start time means the shift continued into the next day. That assumption is common for a night shift, but it can conceal a mistaken entry: if someone accidentally types 6:00 AM instead of 6:00 PM, the formula still produces a plausible overnight duration.

When you need to distinguish dates, enter full date-and-time values in both cells. For example, A2 could contain November 2 at 10:00 PM and B2 November 3 at 6:00 AM. With full date-and-time values in A2 and B2, use =(B2-A2)*24. That returns 8, and it can also calculate elapsed time longer than 24 hours. Subtract a decimal break in hours by adding the break cell to the formula: with the same date-and-time entries and a decimal break in C2, use =(B2-A2)*24-C2.

To enter a date and time in one cell, use an unambiguous value such as 11/2/2026 10:00 PM, taking account of your computer’s regional date settings. For a recurring schedule, consider separate date, start-time, and end-time columns, or store complete start and end timestamps. A timestamp prevents a formula from having to guess which day an end time belongs to.

Format Results and Add Up Hours

The basic formula returns decimal hours. Set the result cells to Number if you want values such as 8.5, and choose the number of decimal places appropriate for your worksheet. To format a cell in Excel for Microsoft 365 on Windows, select it and press Ctrl+1 to open Format Cells; choose Number or General as appropriate. On Mac, Command+1 opens Format Cells. You can also use the Number Format controls on the Home tab.

Decimal hours and elapsed-time displays are not interchangeable. A result of 8.5 means eight and a half hours, not eight hours and five minutes. If you prefer a duration display such as 8:30, omit the multiplication by 24 and format the result as elapsed hours. With clock-in in A2 and clock-out in B2, use =MOD(B2-A2,1), then apply the custom number format [h]:mm. The square brackets are important: they allow totals above 24 hours to display as accumulated hours rather than restarting at zero after a day.

For a weekly total, put each shift’s decimal-hours result in D2:D6. With those calculated shift hours in D2:D6, use =SUM(D2:D6) in the total cell. If the results are elapsed-time values instead, sum those duration cells and format the total as [h]:mm. Applying a normal time format to a total can make hours appear to wrap after 24, which is a display issue rather than lost data.

Round Only When Your Rules Require It

Keep full precision in the underlying calculation unless a defined timekeeping policy requires rounding. Formatting a number to show two decimal places changes only what is displayed; it does not necessarily change the stored value. Using ROUND in a formula changes the calculated result itself, so it can affect a later total.

For instance, if D2 contains decimal shift hours and a specified process requires rounding each shift to two decimal places, use =ROUND(D2,2). If the required rule is to round to the nearest quarter-hour, a formula can express that calculation, but the appropriate rounding method depends on the applicable workplace policy. Do not assume that rounding each shift is equivalent to rounding a weekly total: rounding at different stages can produce different totals.

A spreadsheet can calculate elapsed time, but it does not determine how a particular employer should treat breaks, rounding, overtime, or corrections. Confirm the applicable timekeeping procedure before using a workbook to prepare official records or pay calculations.

Prevent and Troubleshoot Common Errors

A negative or unexpected result: Check whether the cells contain actual times and whether an overnight shift is intended. The MOD formula interprets an earlier end time as the following day.
A result that looks like a clock time: The calculation may be right but the cell may have a Time format. For decimal hours, change it to Number or General.
A total that returns to zero after 24 hours: The cells may be formatted as ordinary time. Use the elapsed-hours format [h]:mm for duration totals.
A break makes the answer look wrong: Check the units. Subtract decimal hours from decimal hours, or convert a time-formatted duration to hours by multiplying it by 24.
A very long shift is calculated incorrectly: MOD cannot tell whether an end time belongs to the next day or a later date. Record full dates and times and subtract the timestamps.
Formula errors after copying: Verify the references still point to the intended row and that no input cell contains text or an empty value that should have been filled in.

For cleaner data entry, label units in the headers, such as “Break (hours)” and “Shift hours,” and use one row per shift. You can also select an input range and choose Data > Data Validation to restrict entries to times. Validation can reduce accidental entries, but it does not replace checking the recorded date, overnight status, or break information.

When a Spreadsheet Is Enough—and When It Is Not

A basic formula works well for a personal log, a small schedule, or a quick estimate when the rules are simple and entries can be reviewed. A larger team may need a dedicated timekeeping system for approvals, audit trails, schedule changes, missed punches, and controlled access. Excel formulas calculate from the values entered; they do not verify who made an entry or whether it is complete.

If you use a shared workbook, keep a clear record of edits and protect formulas from accidental overwriting. In Microsoft 365 for Windows, you can select formula cells and use Review > Protect Sheet to limit changes, after deciding which cells should remain editable. The menu labels and available options can differ somewhat by platform and workbook settings. Protection helps prevent casual edits, but it is not a substitute for access controls or a formal recordkeeping process.

Frequently Asked Questions

What is the formula for hours between two times in Excel?
With the start time in A2 and end time in B2, use =MOD(B2-A2,1)*24 for decimal hours. It handles a shift that crosses midnight when the shift is less than 24 hours.

How do I calculate an overnight shift in Excel?
Enter the start and end times, then use the MOD formula above. For a shift from 10:00 PM to 6:00 AM, it returns 8. If the shift can be 24 hours or longer, enter complete dates and times instead.

How do I subtract a 30-minute break?
If the break is entered as 0.5 decimal hours in C2, use =MOD(B2-A2,1)*24-C2. If the break is entered as 0:30, use =MOD(B2-A2,1)*24-C2*24 to convert it to hours before subtracting.

Why does my total show a time instead of a number of hours?
The result cell may have a Time format. Choose Number or General for decimal hours. For a duration display, use a format such as [h]:mm so accumulated hours above 24 remain visible.

Can Excel calculate hours over multiple days?
Yes. Include the actual date and time in both the start and end cells, then subtract the full timestamps and multiply by 24. A formula using only clock times cannot identify which date an entry belongs to when a shift spans a day or lasts 24 hours or more.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.