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.

*Applies to Excel 2003*

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.

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.

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.

I don’t see any reason why the date is displayed. Make sure that all cells are formatted as TIME (HH:MM), then enter the required time in the same format. For example 05:20 for 5 hours and 20 minutes.

Check this post on how to format a cell as TIME:

http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/

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

Please type the formula through the keyboard instead of copy and paste. See what you get. Also you may have to change “;” to “,” in the formula, depending on your installation.

It works perfectley. thanks for the help

You are welcome.

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

Just change “;” to “,”.

I am sorry but I don’t have Excel 2003 anymore.

Anyhow I don’t think you need ‘Paste Special’ in your case, just do ‘Paste’