How to Add and Subtract Time in Excel

Grab Your Free Workbook Now! 📥

Follow step-by-step and boost your learning.

It’s practical, easy, and totally free. Get started today!

🔔 SUBSCRIBE if you’d like more tips and tutorials like this

https://bit.ly/3HHWQME

We respect your privacy. Unsubscribe at any time.

Watch: time calculations in 10 minutes

Prefer reading? The same steps and formulas are written out below the video.

Step-by-step

1. Understand how Excel stores time

Excel treats time as a fraction of a 24-hour day. 12:00 PM is stored as 0.5 (halfway through the day) and 6:00 AM as 0.25. Formatting decides how that fraction is displayed: hours and minutes (8:35), with seconds (8:35:30), with AM/PM, in 24-hour time, or as elapsed time for totals over 24 hours.

2. Add times together

You can add times like ordinary numbers, with + or SUM. The catch: by default the clock resets after 24 hours, so a total of 27 hours shows as 3:00. To fix it, right-click the cell, choose Format Cells > Custom and type:

[h]:mm

The square brackets let the hours keep counting past 24. That’s essential for weekly hours or long project totals.

3. Add a fixed amount of time

To add 2 hours 30 minutes to a start time in B10, use the TIME function, which takes hours, minutes and seconds:

=B10+TIME(2,30,0)

A 10:00 AM start becomes 12:30 PM.

4. Subtract one time from another

For the time between a start (C3) and an end (D3), subtract: =D3-C3. 8:00 AM to 4:00 PM gives 8:00.

If you format that result as a number, you’ll see 0.33, a third of a day. To get hours as a decimal (handy for payroll), multiply by 24:

=(D3-C3)*24

Now it shows 8.00.

5. Subtract a fixed amount of time

Same idea as adding: =B9-TIME(2,30,0).

6. Build a simple timesheet

With clock-in in B4, clock-out in C4, break start in D4 and break end in E4, total hours worked for the day are:

=((C4-B4)-(E4-D4))*24

That’s the time on site, minus the break, converted to decimal hours. Format the cell as a number and copy it down for the month. Multiply by an hourly rate to get earnings.

Key takeaways

If a time calculation looks wrong, check the formatting first.
Use [h]:mm for totals over 24 hours.
Multiply by 24 when you need hours as a decimal.
Use TIME(hours, minutes, seconds) to add or subtract exact amounts.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred small techniques like these that add up to hours saved every week.