Add hours or minutes to a standard time

If you have a standard time, current time for example, and you want to add to it hours, minutes or seconds to get to a new time, then what you need to do is to convert the hours or minutes to a standard time format using the TIME function. After that you can add the two values together using the “+” sign.

A generic formula will look like this:

New time = current time + Time(hours,minutes,seconds)

In the examples below we have a standard time (07:15) in column A.

1. In row 2 we are adding 3 hours (B2) to 07:15 (A2). The formula in D2 will be:  =A2+TIME(B2;C2;0). The new time is 10:15.

2. In row 3 we are adding 4 hours (B3) and 15 minutes (C3) to 07:15 (A3). The formula in D3 will be:  =A3+TIME(B3;C3;0). The new time is 11:30.

3. In row 4 we are adding 20 minutes (C4) to 07:15 (A4). The formula in D4 will be:  =A4+TIME(B4;C4;0). The new time is 07:35.

4. In row 5 we are adding 12 hours (B5) to 07:15 (A5). The formula in D5 will be:  =A5+TIME(B5;C5;0). The new time is 19:15.

5. The last example in row six is slightly different. The result of the addition is more than 24 hours. If we use a formula similar to the ones shown above the result will be 01:15 while the actual value is 25 hours and 15 minutes. I.e. anything more than 24 hours will be lost. To avoid this problem we modify the formula in cell D6 slightly to look like this:

=(A6+TIME(B6;0;0))*24

We will also change the format of cell D6 to number instead of time. The result of this formula is 25.25 hours(.25 to the right of the decimal point is a decimal value and not minutes).

Notes:

1. Cells that contain time (Columns A and D should be formatted as TIME, cells that contain Hours or minutes (columns B an C) should be formatted as NUMBER).

2. I did not include seconds in these examples. If you want to add seconds then you can specify it as the third parameter of the TIME function.

3. The examples use a 24 hour clock. You can use 12 hour clock with AM/PM without problems.

addtime4

Applies to Excel 2003

Comments

  1. Douglas Lupo says:

    How do I calculate a new date and time by adding, let’s say for example, 56 hours to todays date and time .
    Thanks for the help.

  2. admin says:

    Try this approach:

    Suppose you have:

    A1 = 09/06/2009 11:27
    B1 = 56

    You can break the hours into days and hours. In your example it will be 2 days and 8 hours, then use the formula like this in C1 (or any other cell):

    =A1 + 2 + TIME(08;00;00)

    OR

    Divide the hours by 24 directly in the formula like this:

    = A1 + B1/24

    Regards.

  3. Aseem Chawla says:

    I have a bit of time problem which you may be able to help with.

    1. How to disable the date when one enters the time in Excel 2003?

    2. I enter hours and then want to total them – both row and column-wise… this happens accurately but the problem is something like this:

    I want to add to the already displayed hours in the spreadsheet but cannot since the date is always displayed. Therefore I always have to add the hours on a piece of paper and then enter it on the spread sheet.

    Case in point:

    1. A pilot is entering his flying hours in the spreadsheet and the pilot wants hours and minutes (hh:mm) no seconds required and no decimals as well.

    2. He has already flown say 500 Hrs 35 mins… therefore : 500:35 is already in the cell… now he wants to enter 8 hours 45 mins… how does he do that in excel spreadsheet.

    Can you please help in this little problem.

  4. Ryan says:

    I cannot get this formula to work. I even made the exact same chart that you made and every time i drop the formula in it always tells me there is an error. It seems to always reference B2. Please help. thanks

  5. NOM says:

    please show me for know formula I try to it but it can’t add, example your column A, I press =A2+time(B2;C2;0) but program can’t accept (;) please give me clear your example