Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to calculate time difference between two times or dates?

If you have two lists of time, such column A and column B (the time in column A is earlier than column B in the same row), as shown as below screenshot, and you cannot get the right time different with later time subtracting the early one. So now, I talk about the methods to calculate time difference in Excel.


Easily add hours/minutes/seconds to a datetime in Excel

Supposing you have a date time format data in a cell, such as 2015/5/3 17:53:20, and now you need to add a number of hours, minutes, or seconds to this date. Normally, using formula is the first method for all Excel users, but it’s hard to remember all formulas. With Kutools for Excel’s built-in Commonly-used Formulas you can easily add hours, minutes or seconds to a date time with formulas, and you don’t need to remember the formula at al. Click for 60 days free trial!
doc add hour minute second
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Calculate time difference between two times in Excel


In our case, we suppose there are only hours, minutes and seconds in two columns, and the time in column A is always earlier than Column B in the same row.

1. Select a blank cell, in this case, I select Cell C2, type this formula =B2-A2+(A2>B2) (the time in Cell A2 is earlier than the Cell B2, you can change them as you need), press Enter key and then drag the fill handle to fill the range needed to apply this formula. See screenshot:

2. Select the result range, in this case select the result range C2:C6, and right click > Formats Cells in the context menu. See screenshot:

3. In the Format Cells dialog, click Number tab > Time, and select the time format you need from the right section, click OK. See screenshot:

Then you can see the time difference has been calculated as shown below:


arrow blue right bubble Calculate time difference between two dates in Excel

If there are not only hours, minutes and seconds but also dates in the columns, you can do as follow:

1. Select a blank cell, in this case, I select Cell C2, type this formula =B2-A2 (the time in Cell A2 is earlier than the Cell B2, you can change them as you need), press Enter key and then drag the fill handle to fill the range needed to apply this formula. See screenshot:

2. Select the result range, in this case select the range C2:C6, and right click > Formats Cells in the context menu. See screenshot:

3. In the Format Cells dialog, click Number tab > Time, and select 37:30:55 from the Type section, click OK. See screenshot:

Then you can see the time difference has been calculated as shown below:


arrow blue right bubble Display the time difference as decimal hours, minutes or seconds with Kutools for Excel

If you want to display the calculated time difference as decimal hours, minutes or seconds, you can use the Convert Time utility of Kutools for Excel.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

1. Select the calculated result, see screenshot:
doc time difference kte 1

2. Click Kutools > Content > Convert Time to show the submenu, and you can choose Time to Hours, Time to Minutes or Time to Seconds as you need. See screenshot:
doc time difference kte 2

After you choose an option, the selected data has been converted decimal hours, minutes or seconds.

Displayed as decimal hours
Displayed as decimal minutes
Displayed as decimal seconds
doc time difference kte 3
doc time difference kte 4
doc time difference kte 5

Note:

If you want to save the converted result at another location, you can select the data and click Kutools > Content > Convert Time > Convert Time to enable the Convert Time dialog, and select a convert type, and check Save to another location and select a cell to put out result. Click Ok.

doc time difference kte 6doc time difference kte 7

arrow blue right bubble Convert time to decimal value


add same text to a specific location of each cell in Excel

How could you add text or characters to the beginning of multiple cells, or add text or characters to the end of cells or insert text or characters between existing text? With Add Text utility of Kutools for Excel, you can quickly apply the following operations: . Click for 60 days free trial!
doc add text 6
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

Relative Articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    prasad nalluri · 6 months ago
    how to calculate difference for the below scenario


    4/8/2019 10:18:22 AM

    4/8/2019 6:10:01 PM

    please help me..
    • To post as a guest, your comment is unpublished.
      Sunny · 6 months ago
      Hi, prasad nalliri, just use one date to minus another date, then format the result (a number string) as a date or time format as you need:
      A1 4/8/2019 10:18:22 AM

      A2 4/8/2019 6:10:01 PM
      Using this formula in B1: ==ABS(A1-A2)
      Result: 0.327534722
      Format the result as time or date
  • To post as a guest, your comment is unpublished.
    Rlicmoan · 2 years ago
    Hi there, I know how to calculate the difference between a start and finish time. However, I would like to know what is the formula when I also want to minus a target date Is there such a formula?



    example: start date: 10/29/17 12:00 AM

    A. 1st transaction date: 10/28/17 03:22 PM
    B. end of 1st transaction date: 10/30/17 01:13 PM


    if Column A is not equal to 10/29/17 12:00am then excess hours should be subtracted from the accumulated hrs from A and B
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry, Could you give a compliacted instance? Through your description, I do not understand clearly.
  • To post as a guest, your comment is unpublished.
    Julie Fuller · 2 years ago
    Hi there, I know how to calculate the difference between a start and finish time. However, I would like to know what the formula is when I also want to minus 30minutes. Is there such a formula?
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Thanks for your leaving massage, you can try this formula =A1-TIME(0,30,0). See screenshot:
  • To post as a guest, your comment is unpublished.
    Rats · 2 years ago
    Hi team,

    i want to caluculate the time difference in the same cell ans the working hours should be come in next colum
    for example under timings 9:00am-06:00pm is there i want to know the working time in hours for example like 8 hrs or 9 hours like that.

    Sample:
    Timings Hours
    09:15 AM - 06:15 PM
  • To post as a guest, your comment is unpublished.
    Prabhu · 3 years ago
    I want to highlight the late comers time by getting the difference of exceed time(late time) with the actual time
    For ex:
    if the actual time is 8.00AM if anyone comes by 8.30AM ,the time difference be 0.30minutes ..
    How can i highlight that 30 minutes using conditional formating in excel? or is there any options to highlight that?
  • To post as a guest, your comment is unpublished.
    DESHRAJ CHAUHAN · 3 years ago
    I am having A1 cell : 04/14/2016 11:15:43 and B2 cell : 03/31/2016 10:41:23 Please let me know the time duration in hrs of between two difference date.
    exmple
    refno reg_code status cpv_incoming_date CPV Out CPV Out Time WH CPV IN WH CPV IN time Range CPV In Time
    ABC A B 09-MAY-2016,14:00:50 10-May-16 16:06:59 10-May-16 9:30:00 >6 hrs and < 8 Hrs 9-May-16 14:00:50
  • To post as a guest, your comment is unpublished.
    YOgesh · 3 years ago
    I am having A1 cell : 04/14/2016 11:15:43 AM and B2 cell : 03/31/2016 10:41:23 AM. Please let me know the time duration in minutes of between two difference date.
  • To post as a guest, your comment is unpublished.
    alan · 3 years ago
    Scenario,
    Compare the h:mm:ss from the column A1 and display if the value in A2 cell is greater that 15 minutes then display the timing in proper column

    data condition
    There is a value in a cell A2, 0:16:38(h:mm:ss) and column B name "
  • To post as a guest, your comment is unpublished.
    Benedicta Acquah · 4 years ago
    ENTER EXIT TURN OVER
    825 847
    913 939 26
    1038 1109 59
    1133 1153 24

    Please can you help me find a formulate to calculate turnover. The difference between Exit to Enter. Thanks.
  • To post as a guest, your comment is unpublished.
    Shamim · 4 years ago
    Please advise how to convert time result 7.30 to 7.5 hours. Example result shows 7.30 hours different between two that should be total hours 7.5 in the time sheet.
  • To post as a guest, your comment is unpublished.
    MissR12345 · 4 years ago
    please assist
    how to calculate time difference and add to the next estimated time
    ie.

    A1 =27/10/2015 11:00PM B1 = 28/10/2015 02:30 AM C1 will be the (gained or lost time) then the next estimated start time will be C1 +B1+2hours
    B1 is actual start time and 2 hours is the expected time between the start times.
  • To post as a guest, your comment is unpublished.
    saqib · 4 years ago
    Hi dear,

    Our duty time is 8:00 to 5:00
    If user allowed time to complete task is 5 Hrs
    he start task at as 30th May, 2015 03:00 pm and finish on 1st June, 2015 10:00am,
    Now calculate total spending hours but exclude Sunday off day
    result should be as 4 Hrs
  • To post as a guest, your comment is unpublished.
    vinay · 4 years ago
    hi i want to Calculate time difference between two dates in Excel and also I have to exclude weekend and holidays

    pls suggest
  • To post as a guest, your comment is unpublished.
    Rajesh.MM · 4 years ago
    Dear All,

    by using IF formula time to be calculated if time crossed after 4 PM the formula should say late in warding...
  • To post as a guest, your comment is unpublished.
    shaunak · 5 years ago
    i having column A and B 's cell format is as 1:30 PM then which formula i have to apply to find difference in Hours...
  • To post as a guest, your comment is unpublished.
    khuliso · 5 years ago
    [quote]how do i calculate time different betwen A3 & A4 total display in b3[/quote][img]example.xlsx[/img]
  • To post as a guest, your comment is unpublished.
    Tamal · 5 years ago
    Any suggestion, if for the last case, in column C, I want to display the time difference to be displayed as total no. of minutes, how to do that?
    • To post as a guest, your comment is unpublished.
      Jimmy · 5 years ago
      Times (*) the Decimal answer by 24 eg;

      If c2 had your answer ie, 0.4375. It would be c2*24 = 10.5
      (0.4375*24 = 10.5)