Home › Excel basics › Time difference & hours worked

Calculate Time Difference and Hours Worked in Excel

Q: How do I convert 7:45 to 7.75 hours?

Multiply by 24: =A2*24, and format the result as a number.

Q: How do I convert 7.75 hours back to 7:45?

Divide by 24: =A2/24, and format the cell as h:mm.

Q: How do I calculate overtime above 8 hours a day?

With decimal hours in E2: =MAX(E2-8, 0) gives the overtime, and =MIN(E2, 8) the regular hours.

Q: How do I enter the time quickly?

Press Ctrl+Shift+; to insert the current time as a fixed value. Typing 9:00 or 9:00 PM also works.

To calculate the time difference in Excel, subtract the start time from the end time: =B2-A2. Multiply by 24 to get hours as a number you can use for pay. This guide builds a small timesheet, including breaks, night shifts and weekly totals above 24 hours.

Excel CSE · 3 min read

In this guide

How Excel stores time
Hours worked minus breaks
Past midnight
Totals over 24 hours
Useful time functions
Common problems
FAQ

How Excel stores time

Excel stores a day as 1, so an hour is 1/24 and 12:00 is 0.5. That's why subtracting times works, and why multiplying by 24 turns a time into hours:

Formula	Shows	Format
`=B2-A2`	8:30	Time (h:mm)
`=(B2-A2)*24`	8.5	Number (decimal hours)
`=(B2-A2)*1440`	510	Number (minutes)

With 9:00 in A2 and 17:30 in B2. If =(B2-A2)*24 shows a time like 12:00, set the cell format to Number.

Timesheet: hours worked minus breaks

	A	B	C	D	E
1	Day	Start	End	Break	Hours
2	Mon	9:00	17:30	0:30	8.00
3	Tue	8:30	17:00	0:45	7.75
4	Wed	22:00	6:00	0:30	7.50
5	Total				23.25

In E2, then fill down:

=(MOD(C2-B2, 1) - D2) * 24

C2-B2 is the time between start and end.
MOD(…, 1) handles shifts past midnight (see below). For day shifts it changes nothing.
- D2 deducts the break, entered as a time (0:30).
* 24 gives decimal hours: 7:45 becomes 7.75.

The total in E5 is =SUM(E2:E4) = 23.25 hours. With an hourly rate of 15 in G1, the pay is =E5*G1 = 348.75.

Night shifts past midnight

For 22:00 to 6:00, =C4-B4 is negative, and Excel shows #### because it can't display negative times. Two fixes:

=MOD(C4-B4, 1) =C4-B4+(C4<B4)

Both add a day when the end time is earlier than the start time, giving 8:00. The MOD function version is the most common. If your shifts can last longer than 24 hours, enter full date and time in both cells (e.g. 3-11-2026 22:00) and simply subtract.

Totals over 24 hours

Add up times in h:mm format and a weekly total of 38:30 can show as 14:30: Excel rolls over at each full day. Fix the format:

Select the total cell and press Ctrl+1.
Choose Custom and enter [h]:mm.

The square brackets tell Excel to keep counting hours past 24. Use [mm] for total minutes.

Useful time functions

You want	Formula
Hours part of a duration	`=HOUR(B2-A2)`
Minutes part	`=MINUTE(B2-A2)`
Build a time from numbers	`=TIME(8, 30, 0)`
Current time	`=NOW()-TODAY()`
Show a duration as text	`=TEXT(B2-A2, "h:mm")`
Round to the nearest 15 minutes	`=MROUND(B2-A2, "0:15")`

HOUR only returns 0–23, so don't use it for totals above a day; multiply by 24 instead. For days between dates, see DATEDIF and NETWORKDAYS.

Common problems

#### in the cell. The result is negative (shift past midnight) or the column is too narrow.
#VALUE! One of the times is text. Retype it, or see convert text to numbers.
Pay is way too low. You multiplied a time (0.33 of a day) by the hourly rate. Multiply by 24 first.
Break typed as 30. That means 30 days. Type 0:30, or divide minutes by 1440.

Frequently asked questions

How do I convert 7:45 to 7.75 hours?

Multiply by 24: =A2*24, and format the result as a number.

How do I convert 7.75 hours back to 7:45?

Divide by 24: =A2/24, and format the cell as h:mm.

How do I calculate overtime above 8 hours a day?

With decimal hours in E2: =MAX(E2-8, 0) gives the overtime, and =MIN(E2, 8) the regular hours.

How do I enter the time quickly?

Press Ctrl+Shift+; to insert the current time as a fixed value. Typing 9:00 or 9:00 PM also works.