Calculate the difference between two times

The way you calculate the difference between two times depends on the way you want to present your results:

1. Present the result in standard time format (hh:mm:ss).

Simply subtract the two times from each other and format the result cell as time.

Examples:

A1 = 9:15:00
B1 = 14:07:20
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

or if you are using a 12 hour clock then the same example will be like this:

A1 = 9:15:00 AM
B1 = 02:07:20 PM
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

If you want the difference in hours and minutes or even in hours only, then just customize the cell format for that purpose.

Notes:

When you use this option then:

1. Your result should always be positive. I.e. the time in B1 must be later than the time in A1.

2. Hours will not exceed 24 and minutes  or seconds will not exceed 60.

For more help on time formatting check this post:

How to create a custom time format

2. Present the result as a total of hours, minutes or seconds:

Example:

A3 = 1/25/2009  21:03

B3 = 1/26/2009  23:17

1. To get the total hours between the two times use the formula:

=INT((B3-A3)*24). The result is 26.

2. To get the total minutes between the two times use the formula:

=(B3-A3)*1440. The result is 1574.

3. To get the total seconds between the two times use the formula:

=(B3-A3)*86400. The result is 94462.

Notes:

1. In all three cases (B3-A3) will give the number of days. Then you multiply by the apprpriate constant to get either hours, minutes or seconds.

2. INT function is used in the first case to get whole number. If you are interested in the fraction then you can ignore the INT function.

Comments

  1. Dave says:

    The statements below are shown on your site. I believe you may want to show:

    “=(B3-A3))*1440. The result is 1574.” as
    =(B3-A3)*1440). The result is 1574.

    and

    “=(B3-A3))*86400. The result is 94462.” as
    =(B3-A3)*86400). The result is 94462.

    2. Present the result as a total of hours, minutes or seconds:

    Example:

    A3 = 1/25/2009 21:03

    B3 = 1/26/2009 23:17

    1. To get the total hours between the two times use the formula:

    =INT((B3-A3)*24). The result is 26.

    2. To get the total minutes between the two times use the formula:

    =(B3-A3))*1440. The result is 1574.

    3. To get the total seconds between the two times use the formula:

    =(B3-A3))*86400. The result is 94462.

  2. admin says:

    You are absolutely right.
    There was an extra “)” in both formulas.I have fixed those now.

    Thanks allot.

  3. Kathy says:

    I am trying to create a spreadsheet for my son to record the amount of time he wears his orthodontic head gear. He is supposed to wear it all night long. On paper he has been splitting his record keeping by the date. Thus he will list Friday 10:00pm to 11:59 pm and then Saturday 12:00am to 7:00am. So the start time is B2 and the end time is C2. The formula for the amount of time worn column is =c2-b2 . This gives me an error. I have the cells formatted as 12 hour time. I believe I am following your instructions but it doesn’t want to subtract even though 7am is later than midnight. I am using Excel 2007

    • admin says:

      The problem is In the second example (Saturday) because 12 is arithmetically Greater then 7. to get around the problem modify the formula as follows:

      =MOD(C2-B2;1)

      Replace “;” with “,” if required.