If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:

TIME(hours,minutes,seconds)

Example:

We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:

=TIME(INT(A2);(A2-INT(A2))*60;0)

• The first parameter in this formula,  hours is equal to the integer part of the decimal value: INT(A2).
• The second parameter, minutes is equal to the fraction of the decimal value multiplied by 60:  (A2-INT(A2))*60.
• The last parameter seconds, is set to 0 in our example.

Below are different examples based on this formula:

Applies to Excel 2003

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

If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.

Example 1 : total hours less than 24.

Cells B1, B2, B3 and B4 contains the times.

Cell B5 (total) contains the formula:

=SUM(B1:B4)

The result of this sum is 23:05 (23 hours and 5 minutes).

Please note that cells B1to B5 are formatted as time (hh:mm).

Example 2 – total hours greater than 24.

Cells B1, B2, B3 and B4 contains the times.

The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).

To avoid this problem we use the following formula instead: =SUM(B1:B4)*24

We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).

Applies to Excel 2003