Cellwise Try it free

Excel guide · 4 min read

How to add hours and minutes to a time in Excel

Quick answerDivide the hours by 24 and add them: =B2+C2/24 adds the hours in C2 to the time in B2, so 8:30 plus 2.5 hours gives 11:00. Excel stores times as fractions of a day, which is why 1 hour is 1/24. For minutes, divide by 1440 (the minutes in a day): =B2+D2/1440.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1JobStartHoursExtra min
2Boiler service8:302.515
3Kitchen rewire13:15845
4Night concrete pour21:009.250
5Server patch23:401.530
6Roof inspection10:053.7520

Download this sheet as an Excel practice file (.xlsx)

Add hours with /24

Excel stores a time as a fraction of one day: 12:00 is 0.5, 6:00 is 0.25 and 1 hour is 1/24. So to add a number of hours, turn them into a fraction of a day first by dividing by 24.

This works with decimal hours, which is how most job estimates and timesheets are written: 2.5 means two and a half hours, 0.75 means 45 minutes. If the answer shows as a decimal such as 0.458333, the cell has the General format; press Ctrl+1 and choose a Time format.

=B2+C2/24

Result: 11:00

11:00: the boiler service starts at 8:30 and takes 2.5 hours.

The most common mistake: adding hours as days

=B2+C2 looks reasonable, but Excel reads 2.5 as two and a half days. The result is 2.5 days after 8:30, and because a time format hides the date part, the cell shows a time that looks believable.

Here it shows 20:30, which is 8:30 plus 12 hours (the half day). Whole numbers hide it even better: 8:30 plus 3 shows 8:30, because 3 full days later is the same clock time. If an added time looks wrong, check you divided by 24.

=B2+C2

Result: 20:30

20:30, which is wrong: Excel added 2.5 days, not 2.5 hours.

Add hours and minutes together

When hours and minutes are in separate columns, convert each one and add them: hours divided by 24, minutes divided by 1440. The kitchen rewire is 8 hours plus 45 minutes.

TIME(hours, minutes, seconds) does the same job and reads clearly: =B3+TIME(C3,D3,0) also gives 22:00. TIME accepts minutes above 59 and rolls them into hours, so TIME(0,90,0) is 1:30.

=B3+C3/24+D3/1440

Result: 22:00

22:00: 13:15 plus 8 hours 45 minutes.

When TIME gives the wrong answer

TIME has two limits that catch people out. It ignores the decimal part of each argument, so TIME(2.5,0,0) is 2:00, not 2:30. And it never returns a full day or more: hours of 24 and above wrap around, so TIME(30,0,0) is 6:00 AM.

So use TIME for whole hours and minutes you type in, and /24 for decimal hours or anything that could reach 24 hours. The formula below adds the 2.5 hours in C2 through TIME and loses the half hour.

=B2+TIME(C2,0,0)

Result: 10:30

10:30 instead of 11:00: TIME dropped the .5 from 2.5 hours. =B2+C2/24 gives the correct 11:00.

Times that go past midnight

The night concrete pour starts at 21:00 and runs 9.25 hours. B4+C4/24 is really 1.26 days: a time format shows 6:15, but the value is greater than 1, and the [h]:mm format reveals it as 30:15. That matters if you compare it with other times, or look it up against a schedule: 6:15 the next morning is not less than 21:00.

MOD(value,1) keeps only the time of day and drops the whole days, so the finish time is a clean clock time. TEXT shows it here with AM/PM. If you need to know the day, INT(B4+C4/24) gives 1, meaning the job ends the next day. If your start cell holds a full date and time, skip MOD: adding hours to a date-time moves the date forward correctly by itself.

=TEXT(MOD(B4+C4/24,1),"h:mm AM/PM")

Result: 6:15 AM

6:15 AM, the finish time the next morning. The server patch, =TEXT(MOD(B5+C5/24+D5/1440,1),"h:mm AM/PM"), finishes at 1:40 AM.

Totals over 24 hours: [h]:mm

Adding up durations is where the normal h:mm format lies to you. These five jobs come to 25 hours plus 110 minutes, which is 26 hours 50 minutes. Formatted as h:mm, the total shows 2:50, because h:mm only shows the time of day and throws away whole days.

The square brackets in [h]:mm tell Excel to keep counting hours past 24. Set it in Format Cells (Ctrl+1) > Number > Custom and type [h]:mm in the Type box. For decimal hours instead, multiply by 24 or just add the hours and minutes as numbers: =SUM(C2:C6)+SUM(D2:D6)/60 gives 26.83.

=TEXT(SUM(C2:C6)/24+SUM(D2:D6)/1440,"[h]:mm")

Result: 26:50

26:50. The same value with "h:mm" shows 2:50, which is how weekly totals go missing.

Common mistakes

Adding hours directly to a time (=B2+3)

Excel adds 3 days, not 3 hours, and the time format hides it. Divide by 24: =B2+3/24.

Using TIME with decimal hours

TIME(2.5,0,0) drops the half hour. Use /24 for decimal hours, or split them first: TIME(INT(C2),MOD(C2,1)*60,0).

A total of hours shows a small number like 2:50

The cell is formatted h:mm, which resets every 24 hours. Use the custom format [h]:mm.

Subtracting hours gives #####

A time before midnight minus hours is negative, and Excel cannot show negative times. Wrap it in MOD: =MOD(B2-10/24,1) shows 10:30 PM for 8:30 minus 10 hours.

FAQ

How do I add 30 minutes to a time in Excel?

Use =A2+30/1440 or =A2+TIME(0,30,0). There are 1,440 minutes in a day, so dividing by 1440 turns minutes into the fraction Excel uses for time.

How do I add hours to a time that goes past midnight?

Add as normal, then wrap it in MOD to keep just the clock time: =MOD(A2+B2/24,1). If A2 holds a full date and time, you do not need MOD, because the date moves to the next day by itself.

Why does adding 8 hours in Excel not change the time?

You probably added 8 rather than 8/24, so Excel added eight days. The clock time stays the same and only the hidden date changes. Divide the hours by 24.

Can I add a time typed as text, like "1:30"?

Yes. =A2+"1:30" works because Excel converts the text to a time when you do arithmetic with it. A cell reference to a real time value or TIME(1,30,0) is easier to read and change later.

Functions used

Related guides

Updated