How to Calculate Time Difference in Excel (With Formulas)

Here is how to calculate time difference in Excel: put the start time in one cell and the end time in another, subtract the start from the end, and format the result as time. For shifts that cross midnight, use =MOD(end-start,1). To get decimal hours for payroll, multiply by 24. This guide covers every case, with copy-ready formulas for both Excel and Google Sheets.

How Excel stores time

Excel stores dates and times as numbers. One whole day is 1, so one hour is 1/24 (about 0.0417) and one minute is 1/1,440. 12:00 PM is stored as 0.5. This is why subtracting two times works, and why multiplying by 24 gives hours.

Step 1: Enter the times

Type times with a colon, such as 9:00 AM or 17:30. Excel recognizes them as times automatically. If a time shows up left-aligned, Excel treated it as text: check for spaces or use a format like 9:00 AM.

Step 2: Subtract the start from the end

Format C2 as time: Home → Number format → More number formats → Custom, and type h:mm. Without this, Excel may show a decimal like 0.354 or a clock time like 8:30 AM.

Step 3: Handle shifts that cross midnight

If a shift starts at 10:00 PM and ends at 6:00 AM, =B2-A2 gives a negative number and Excel shows ######. Use MOD, which adds a day to negative results and leaves positive ones alone:

=MOD(B2-A2,1)

10:00 PM to 6:00 AM returns 8:00. The same formula works for day shifts, so you can use it in every row. An alternative is =IF(B2<A2, B2+1-A2, B2-A2).

If your cells contain full dates and times (such as 3/14/2026 10:00 PM), plain subtraction already works, because the date shows which day each time falls on.

Step 4: Subtract breaks

Put the unpaid break in its own column as a time (0:30 for 30 minutes) and subtract it:

=MOD(B2-A2,1)-C2

Or subtract minutes directly with the TIME function: =MOD(B2-A2,1)-TIME(0,30,0).

Step 5: Total a week (over 24 hours)

Sum the daily hours with =SUM(D2:D8). If the total is 38 hours, Excel shows 14:00 with a normal time format, because it rolls over every 24 hours. Use the custom format [h]:mm: the square brackets let hours go past 24, so it shows 38:00.

Step 6: Convert to decimal hours

Multiply a time by 24 to get decimal hours, and format the cell as a number:

=MOD(B2-A2,1)*24 → 8.5 for 8 hours 30 minutes.

For pay, multiply decimal hours by the hourly rate: =D2*24*E2, where D2 is the time worked and E2 is the rate. For minutes, multiply by 1,440 instead; for seconds, by 86,400.

A complete timesheet

Put the hourly rate in J1, copy the formulas down, and total columns E, F and G at the bottom. To check overtime, =MAX(0,SUM(F2:F8)-40) gives the hours over 40.

Adding or subtracting time

• Add 2 hours 30 minutes: =A2+TIME(2,30,0)
• Subtract 45 minutes: =A2-TIME(0,45,0)
• Add 90 minutes as a number: =A2+90/1440
• Add 36 hours (TIME stops at 23 hours): =A2+36/24

Average time

=AVERAGE(E2:E8) averages durations, formatted as [h]:mm. For clock times that cross midnight, averaging needs an extra step; see how to calculate average time.

Time between two dates

Subtract dates to get days: =B2-A2, formatted as a number. For whole years, months and days, use =DATEDIF(A2,B2,"y"), "ym" and "md". For working days, use =NETWORKDAYS(A2,B2). Or use the date calculator.

How to calculate time difference in Excel in minutes or seconds

Sometimes you need the gap as a plain number rather than a time. Multiply the difference by the number of units in a day:

The HOUR, MINUTE and SECOND functions also extract parts of a time, but they ignore anything over 24 hours, so =HOUR(B2-A2) is only safe for gaps shorter than a day.

Rounding to the nearest 15 minutes

Many payroll policies round clock-in times to the nearest quarter hour. Use MROUND with a 15-minute time:

• Nearest 15 minutes: =MROUND(A2,"0:15")
• Always round up: =CEILING(A2,"0:15")
• Always round down: =FLOOR(A2,"0:15")

An 8:07 AM clock-in rounds to 8:00 AM, and 8:08 AM rounds to 8:15 AM. Check your local rules before you round: in the US, rounding must be neutral over time and cannot always favor the employer.

Elapsed time up to now

=NOW()-A2 gives the time since the date and time in A2, and it updates whenever the sheet recalculates. Format it as [h]:mm to see hours, or d "days" h:mm to see days as well. For a time today only, use =MOD(NOW(),1)-A2. The elapsed time calculator gives the same answer without a spreadsheet.

Turning decimal hours back into time

If a system exports hours as decimals (such as 7.75), divide by 24 and format as [h]:mm: =A2/24 shows 7:45. For minutes, divide by 1,440: =A2/1440 turns 135 into 2:15. The time to decimal converter shows the full table.

Overtime and night shift formulas

• Daily overtime over 8 hours: =MAX(0,F2-8), where F2 holds decimal hours.
• Regular hours only: =MIN(F2,8).
• Weekly overtime over 40 hours: =MAX(0,SUM(F2:F8)-40).
• Overtime pay: =regular_hours*rate + overtime_hours*rate*1.5.

Our guide on how to calculate time and a half explains the rules behind these formulas.

Time zones

Excel has no built-in time zone support, so add or subtract the offset as a fraction of a day. To convert 3:00 PM in New York to London (5 hours ahead), use =A2+5/24. Daylight saving changes on different dates in each country, so the offset is not always the same. For live times, use our time zone converter.

Worked example: a full week

Here is how to calculate time difference in Excel for a whole week, including one overnight shift, using the timesheet layout above.

The total cell uses =SUM(E2:E6) with the [h]:mm format. Friday's night shift works because of MOD. The overtime cell, =MAX(0,F7-40), returns 0.5 hours.

Google Sheets

All the formulas above work in Google Sheets. To format, use Format → Number → Duration for totals over 24 hours (the equivalent of [h]:mm), or Format → Number → Custom date and time for clock times. MOD, TIME, DATEDIF and NETWORKDAYS work the same way.

Troubleshooting

• ######: the result is negative (use MOD) or the column is too narrow.
• A total like 14:00 instead of 38:00: use the [h]:mm format.
• A decimal like 0.35 instead of a time: format the cell as time, or multiply by 24 for hours.
• Formula returns #VALUE!: one of the times is stored as text. Re-type it, or use =TIMEVALUE(A2).
• Avoid TEXT() for calculations: =TEXT(B2-A2,"h:mm") returns text that cannot be added up.

Summary

To recap how to calculate time difference in Excel: subtract start from end, wrap it in MOD for overnight shifts, format with [h]:mm for totals, and multiply by 24 for decimal hours. Those four habits solve almost every time problem in a spreadsheet.

For a quick answer without a spreadsheet, the time duration calculator and hours calculator do the same calculations in your browser.

How to calculate time difference in Excel between two dates and times

When the cells hold full dates and times, plain subtraction gives the gap in days, including fractions. With 3/14/2026 10:00 PM in A2 and 3/16/2026 7:30 AM in B2:

• =B2-A2 formatted as [h]:mm shows 33:30.
• =(B2-A2)*24 formatted as a number shows 33.5 hours.
• =INT(B2-A2)&" days "&TEXT(B2-A2,"h:mm") shows "1 days 9:30".
• The custom format d "days" h:mm shows the same thing without a formula, for gaps under a month.

This is the most reliable way to calculate time difference in Excel for anything longer than a day, because the date removes all doubt about midnight.

Negative time differences

Excel cannot display a negative time in its default date system, so you see ######. You have three choices:

• Use =MOD(B2-A2,1) if the end time is on the next day.
• Show the size of the gap with =ABS(B2-A2) and add a minus sign yourself with =IF(B2<A2,"-","")&TEXT(ABS(B2-A2),"h:mm").
• Switch the workbook to the 1904 date system (File → Options → Advanced → Use 1904 date system). Negative times then display, but every existing date in the workbook shifts by four years, so only do this in a new file.

Times imported as text

Data exported from other systems often arrives as text such as "09:00 AM". Subtraction then fails with #VALUE!. Convert first: =TIMEVALUE(B2)-TIMEVALUE(A2). For full date-time text, use =DATEVALUE(A2)+TIMEVALUE(A2), or Data → Text to Columns → Finish to convert a whole column at once.

Totals by person or project

To add up hours for one person in a long log, use SUMIF on the duration column: =SUMIF(A:A,"Ana",E:E), formatted as [h]:mm. For two conditions, such as one person in one week, use SUMIFS: =SUMIFS(E:E,A:A,"Ana",B:B,">="&H1,B:B,"<"&H1+7), where H1 holds the week's start date.

Whole working days

=NETWORKDAYS(A2,B2) counts weekdays between two dates, including both ends. Add a holiday list as a third argument to skip public holidays. For working hours on full days, multiply by the length of the day: =NETWORKDAYS(A2,B2)*8. For other weekend patterns, such as Friday and Saturday, use NETWORKDAYS.INTL with a weekend code.

Shortcuts that save time

• Ctrl + Shift + ; enters the current time as a fixed value, ideal for clocking in.
• Ctrl + ; enters today's date.
• Press both, separated by a space, to stamp a full date and time.
• Ctrl + 1 opens Format Cells, where you set h:mm or [h]:mm.

Time format cheat sheet

Whichever method you choose to calculate time difference in Excel, check the format first: most "wrong" answers are correct numbers shown in the wrong format.

How to calculate time difference in Excel with seconds

Lap times, call logs and machine data often include seconds. Enter them as h:mm:ss (for example 0:04:35 for 4 minutes 35 seconds) and subtract as usual. Format the result as [h]:mm:ss for totals, or mm:ss for short gaps. To get the result as a number of seconds, multiply by 86,400: a gap of 0:04:35 returns 275.

Watch out when typing minutes and seconds only. Excel reads 4:35 as 4 hours 35 minutes, not 4 minutes 35 seconds. Type 0:4:35 instead.

Rounding the result

Decimal hours often come out with long tails, such as 7.916666. Round them for payroll with =ROUND((B2-A2)*24,2), which gives 7.92. To round a time to the nearest minute while keeping it as a time, use =MROUND(B2-A2,"0:01"). Round once, at the end, rather than in every row; rounding each row separately can add or lose minutes over a long sheet.

Example: a project time log

Here is how to calculate time difference in Excel for a simple project log, with a total in hours and a cost at $45 an hour.

The cost is =E5*45, or $326.25. Notice that the rounded column adds up to 7.25 exactly; with other times, a sum of rounded values can differ from the rounded sum by 0.01, so total the time column first and convert the total.

Which method should you use?

If you are still unsure how to calculate time difference in Excel for your sheet, pick by the data you have. Times only, same day: =B2-A2. Times only, possibly overnight: =MOD(B2-A2,1). Full dates and times: =B2-A2 with [h]:mm. Need a number for pay or charts: multiply by 24. Those four cases cover almost every spreadsheet you will build.

Five mistakes to avoid

• Typing times with a period (9.30 instead of 9:30). Excel reads 9.30 as the number 9.3, not a time.
• Mixing text and real times in one column after copying data from email or a PDF. Left-aligned cells are the warning sign.
• Forgetting the brackets in [h]:mm, so weekly totals wrap at 24 hours.
• Multiplying a time by the hourly rate without multiplying by 24 first. 8:00 × $20 gives $6.67, because 8:00 is stored as 0.333.
• Using HOUR() for long gaps. It returns only the hours part of the clock, so a 26-hour gap shows as 2.

Fix those five and almost every question about how to calculate time difference in Excel answers itself. When a result looks wrong, select the cell, press Ctrl + 1, and check the format before changing the formula. If you would rather skip the spreadsheet, the time calculator adds and subtracts times, and the date calculator counts days, weeks and working days between dates.

Frequently asked questions

How do I calculate time difference in Excel?

Subtract the start time from the end time, for example =B2-A2, and format the result as h:mm. For shifts that cross midnight, use =MOD(B2-A2,1).

How do I convert time to decimal hours in Excel?

Multiply the time by 24 and format the cell as a number. =MOD(B2-A2,1)*24 gives 8.5 for 8 hours 30 minutes.

Why does my time total reset after 24 hours?

The h:mm format shows the time of day. Use the custom format [h]:mm so hours can go past 24.

How do I calculate hours worked in Google Sheets?

Use =MOD(end-start,1)-break, and format the cell with Format, Number, Duration. Multiply by 24 for decimal hours.

How do I calculate time difference in Excel in minutes?

Subtract the times and multiply by 1,440, the number of minutes in a day: =(B2-A2)*1440. Format the result as a number.

How do I calculate the time between two dates and times in Excel?

Subtract the earlier date-time from the later one, =B2-A2, and format the result as [h]:mm for hours or d "days" h:mm for days and hours.

How do I calculate time difference in Excel with seconds?

Enter times as h:mm:ss, subtract them, and format the result as [h]:mm:ss. Multiply by 86,400 to get the difference as a number of seconds.