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:
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).
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.
Applies to Excel 2003