Comments on: How to calculate time in Excel - time difference, adding / subtracting times

The tutorial explains different ways to calculate times in Excel and demonstrates several methods of adding times and calculating time difference. You will learn a few useful formulas to sum times and add hours, minutes or seconds to a given time. Continue reading →

Comments page 16. Total comments: 1023

  1. pls help me to calculate ETA, for example one has its ETA of 10 hours, and it comes back within 12 hours, second if vehicle is moving at 2300hrs and comes back next day at 0300 hrs, how will I calculate its ETA?

  2. Hi All,
    Good Day!
    Need some help, i want to subtract two time and result in ms? Is there any possibility?
    Start: 24/06/2019 6:37:06:06
    End: 24/06/2019 6:37:06:06
    currently i using this formula:
    =IF(INT(H74-G74)>0, INT(H74-G74) & " days, ","") & IF(HOUR(H74-G74)>0, HOUR(H74-G74) & " hours, ","") & IF(MINUTE(H74-G74)>0, MINUTE(H74-G74) & " minutes and ","") & IF(SECOND(H74-G74)>0, SECOND(H74-G74) & " seconds","")

  3. Date 1/5/2019 02/5/2019
    Durations 01:10 03:58
    any simple formula for above if exceed 3 hrs than burst SLA

  4. Desperately need formula to calculate hours and minutes worked in a day. Also need formula for total hours and minutes worked in a week for payroll.

  5. I have 2 times column
    start time
    end time
    Now I want to calculate
    How much hour is there between (6 A.M to 18 P.M) from those 2 starts and end column
    How much hour is there between (18 P.M to 00:00 A.M) from those 2 starts and end column
    How much hour is there between (00:00 A.M to 6:00 A.M) from those 2 starts and end column

  6. a clock was correctly set at 12pm.when the exact time was 10:00 p.m. the clock shows 1910 p.m. find the correctly time when the clock shows at 11:00 p.m.?

  7. How to Find in between time (12:32:00 PM to 01:12:00 AM)

  8. Need a formula that compares a date and time stamp between two columns. If 1 column is below the date and time in the 2nd column then it would say "Yes","No" based on the snapshot. Any ideas? Date and time stamp are shown in the same column. Does this need to be broken down?

  9. Hi,
    I tried to used all possible formula to calculate time difference , but the return is Error
    I have used formula =$I2-$H2 or =INT(I4-H4) & " days, " & HOUR(I4-H4) & " hours, " & MINUTE(I4-H4) & " minutes and " & SECOND(I4-H4) & " seconds"
    Both gave me Error
    Here is example:
    Start Time End Time MTBF
    4/29/19 18:24:10 4/29/19 23:19:56 #VALUE! (use formula =$I2-$H2 or

    1. I think I find the answer...
      I use simple combine formula =A2+B2 , instead of formula =TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm:ss") , then the time difference formula =INT(D2-C2) & " days, " & HOUR(D2-C2) & " hours, " & MINUTE(D2-C2) & " minutes and " & SECOND(D2-C2) & " seconds" is working perfectly.

  10. I want to calculate difference in time in different dates please help me

  11. hi,
    how to calculate the target time is 7 hours and start time is 7 am end time is 12:30 pm.
    in this case how to calculate the how is complete the below 7 hrs and how is complete above 7hrs

  12. Downtime in % per day = Down time of affected machine / Total Available time in a Day
    =G5*H5*0.5/(47*24*60)

    now please let me understand what is this 47
    24 is hr,60 is min but what is 47 ?

  13. Hi,
    How can i calculate differnce in a tasks (Start date and end date) considering only Business hrs in that task (10 Am to 7 Pm)

    Task Start Date: 2-3-2019 20:00
    Task End Date: 3-3-2019 12:00
    I need to calculate it under only BH

  14. Sum of hours is not correctly shown: 09:20 + 08:45 + 09:22 + 08:56 + 06:08 should come to 42:31 but using the SUM function it calculates as 18:31. Why & How?

      1. Thank you Svetlana, it worked.

  15. Hi,
    Thank you, it was interesting and easy to learn. I shall come up some others doubts in Future.
    Regards,
    Sujay.BS

  16. Hi all, I'm trying to make a document calculating amount of time spent over a work-day, in different time periods. An example could be an over-all time period from 18.30 to 05.30. Cell A should define time spent between 18.00 and 21.00, Cell B define time spent between 21.00 and 00.00 and Cell C define time spent between 00.00 and 06.00. The result will be Cell A = 2,5 hours, Cell B = 3 hours and Cell C = 5,5 hours. But what formula should I use to get to the results? In advance thanks a lot! Best, Sune.

  17. Hi Team,
    Can anyone resolve my problem, it would be great. Actually my shift timing is "6 pm to 3:30 am" IST. I am facing problem in weekend days (Saturday morning after 12:00 am when i m getting any request, then it is not calculating because saturday is a weekend but my shift timing is 6 pm to 3:30 am till saturday morning.
    Curently i am using one formula for Day shift which is working 100% accurate if shift timing is 12 pm to 9:30 pm and G2 = Start date, J2 = End date.

    =(((NETWORKDAYS(G2,J2)-1)*("21:30:00"-"12:00:00")+IF(NETWORKDAYS(J2,J2),MEDIAN(MOD(J2,1),"21:30:00","12:00:00"),"21:30:00")-MEDIAN(NETWORKDAYS(G2,G2)*MOD(G2,1),"21:30:00","12:00:00")))

  18. I have a cell calculating time remaining until a task is completed. Is there a way for when hours remaining = 0 to hide the hour digit and only show minutes remaining?

  19. Trying to create a formula to calculate total time in hours and minutes.

    Then the total number of hours and minutes converted into 15 min increments.

    So for example, total mins & hours 8.40 (8 hours 40 mins)
    In units 8.75

    Help!!!

  20. Kindly provide me a formula for from time - 9:45:00 PM & To time 1:15:00 AM, want time difference?? Using =xX9-XX9 formula. getting results ########

    1. Charles,
      Hash symbols are displayed if either the formula cell is not wide enough to accommodate the result or the result is a negative value. If the former, just make the cell wider. If the latter, please see How to calculate negative times in Excel

  21. Hi,
    Please can someone tell how to calculate total hours.
    Starting Time - 23:05:00
    End Time - 00:10:00

    Thank you.

  22. The examples and illustrations are really very helpful. Thanks for your kind help.

  23. Dear Ms. Svetlana Cheusheva,
    So many thanks for your nice article.
    Thanks & Regards
    Sunil Kumar Pandey

  24. Hi,
    I need to subtract minutes or hours from a given time with date change.

    Example: given time: 5:00 (am), needed time: 6 hours earlier.
    Problem: Excel returns -1 instead of 23 (11 pm). How can I go around it?

    Thanks a lot!
    Yan

  25. Hello. I have a spreadsheet with 40,000 rows of 2 columns each. StartTime and DurationInSeconds. I am trying to figure out a way to show how many of them happened at the same time. The StartTime is formatted as "mm/dd/yyyy hh:mm:ss" and the duration is just the number of seconds. I've already added a 3rd column =A1+TIME(0,0,B1) to show the StopTime so I have StartTime and StopTime. Now I need to see how many times they coincide.

  26. I need to average times for several days using military time. I have day 1=00:45, day 2=00:20, Day 3= 23:59. When I run the formula +Average(A1:A3) Excel gives me 08:21 as my answer. If I change 23:50 to 00:01 it gives me 00:22 Which is more realistic. I am averaging over a 24 hour day prior to mid-night is causing my problems. Any Ideas????

  27. Hi,
    In one cell, say (A1) I have time, and so in another cell, i applied the Formula (=A1+1). Why do i get the same time?
    What does that numeric 1 imply?

  28. Hi, I need to calculate the time between certain hours. So for example, the below shows start and end times, and I need to calculate the hours between 10am and 2pm only.
    Start time 3/12/2018 9:30:00 am
    End Time 3/12/2018 11:30:00am
    Answer 1.5 hours
    If end time was 16:00:00pm answer would be 4 hours (so, only counting time that falls between 10am and 2pm).
    Please help with a formula!

  29. hi, i need to calculate the hours reading for longer intervels ;
    for ex. day 1 day 2
    hrs:mins hrs:mins
    loading- 22564:48 loading- 22575:05
    unloading-22444:35 unloading-22450:25
    Above is the reading values are in hours and minutes how to find the difference of days if it carried out similarly in all days of month.

  30. Hi !

    Anyone help me ! How to find out the Max time while matching two scenarios

  31. I am unable to calculate 24 hours time in excel if anybody knows please help me...

  32. Hello,
    I have a start time and end time for hundreds of people, some people multiple start and end times since they clock in and out multiple times per time. I also have a week's worth of data. How can I extract the total amount of time that falls between two specific times. So I only want to know how many hours, minutes, seconds each person worked between 10AM - 12PM specifically for each day. Also is there a way to customize, make the dates interchangeable, times interchangeable? I may need to look up total hours for multiple days/time periods. My Data looks like this:

    Last Name Date St art End Total Hours
    Chiang 12/9/18 0.00
    Cota 12/9/18 0.00
    Cuevas Chairez 12/12/18 11:05:00 AM 6:15:00 PM 7.17
    Cuevas Chairez 12/9/18 5:48:00 AM 10:35:00 AM 4.78
    Davila 12/9/18 0.00
    Flores 12/9/18 10:21:00 AM 5:17:00 PM 6.93
    Flores 12/10/18 5:00:00 AM 9:50:00 AM 4.83
    Holguin 12/11/18 8:54:00 AM 5:24:00 PM 8.50

    THANK YOU for any help you can provide. I have been stumped on this for weeks!!

  33. if i use a single cell to to schedule a shift such as: 8:00AM-4:00PM is there a way to calculate the total hours worked minus a half hour break in the next cell? EG /7.50

  34. Beginning Time = 8:20 PM
    Ending time = 4:32 AM
    (Over Nite Sleep in hours/minutes)
    I'm a beginner. Thanks
    Bob

    1. Hello, Bob,
      If your cells contain the time values only, please try to use the formula like the one below:

      =IF(Ending Time < Beginning Time, Ending Time+1, Ending Time) - Beginning Time

      Just change the Beginning and Ending Time to your cell references.
      Hope this is what you need.

  35. I plot out the wildlife sightings from my trial cam. Specifically, I obtain the time and date attributes of each .jpg photo and plot them out. But, after daylight savings time I have to subtract one hour from the time. I have that figured out, but when the time and date are 11/11/2018 0:38, for example, subtracting one hour should be 11/10/2018 23:38. But instead I get ########… Also, the correct response needs to be in a plottable format, not text. I'm stumped. Please help.

    1. Hi Dave,

      Assuming the original time is in A2, you can use this formula to subtract 1 hour:
      =A2-(1/24)

      If the formula cell displays ########, just make the column a little wider to accommodate the date/time value.

  36. my formula is giving abrupt values. don't know why?
    suppose ...
    A1= 08:00 Hrs.
    A2= 07:00 Hrs.
    A3 =01:00 Hrs.
    A4= IF(A1-(A2+A3)<0,"ERR",(A1-(A2+A3)))

    with current values of A2 & A3 the result is "ERR" but it should be 0 i think.

  37. Hi,

    check in check out
    28/10/2018 08:03 28/10/2018 19:57
    29/10/2018 19:58 30/10/2018 07:54
    30/10/2018 19:55 31/10/2018 07:56

    I'm looking for a formula that calculate how many hours i have been in between 19;00 and 22:00.

    Thanks

  38. I am looking for a formula that will populate fields for me automatically to count down seconds. Starting at 10:00, counting down to 9:00. 10:00...9:59...9:58 and so on.

    Thanks

  39. Hi,

    I am using these formulas to calculate slary cost. I have now been ble to make A2 the start of the shift, B2 the end of the shift, C2 the duration of the shift. How can I make D2 = Total salary cost for the shift (based of C2)

    Thanks

  40. Hi,
    I have the time sheet, In that i have to segregate the 1-5 min, 5-10 min, 10-15 min ect.. to calculate the no of 1-5 min and 5-10 min and 10-15 min. Kindly help me.

  41. Caption under figure in Formula 4 should say "less than" it seems.

    1. Hi Dave,

      Absolutely so. Fixed, thank you!

  42. Is there a way to calculate time with a format where you just use 7-4. Meaning that the 7 would in the AM and the 4 bing in the PM

  43. Outstanding explanations!! This (=IF(INT(B2-A2)>0…) was exactly what I needed. That you for the excellent explanations and demos.

  44. Hi All,

    I'd really appreciate some help with this. I'm trying to calculate a time cost per spend for a restaurant split between Pre 17:00 & post 17:00.

    I have in column A start time and column B finishing time.

    So it could be: 09:00 18:59 or 19:03 01:15 or 09:30 16:11

    How can I calculate the hours pre 17:00 without it adding straight up to 17:00 and calculate the the ones post 17:00 without it adding in extra time or going negative due to going past the midnight threshold.

    I'd really appreciate an answer because it is racking my brains something rotten.

    Kind regards

    Nick

  45. I have successfully used formula:

    =IF(INT(W47-V47)>0,INT(W47-V47)&" days, ","")&IF(HOUR(W47-V47)>0,HOUR(W47-V47)&" hours, ","")&IF(MINUTE(W47-V47)>0,MINUTE(W47-V47)&" minutes")

    but am getting FALSE for some Minutes and #NUM when time is set to same day.
    Any ideas?

    How fields Look

    Previous Current Time between Events
    End Event
    Time Start

    3/13/18 16:48 4/20/18 05:31 37 days, 12 hours, 43 minutes
    4/20/18 05:31 4/24/18 18:43 4 days, 13 hours, 12 minutes
    4/24/18 18:43 6/16/18 00:00 52 days, 5 hours, 17 minutes
    6/16/18 00:00 6/21/18 00:43 5 days, 43 minutes
    6/21/18 00:43 7/02/18 06:43 11 days, 6 hours, FALSE
    7/02/18 06:43 7/10/18 06:14 7 days, 23 hours, 31 minutes
    7/10/18 06:14 7/10/18 19:55 13 hours, 41 minutes
    7/10/18 19:55 7/31/18 06:00 20 days, 10 hours, 5 minutes
    7/31/18 06:00 8/03/18 08:47 3 days, 2 hours, 47 minutes
    8/03/18 08:47 8/08/18 03:36 4 days, 18 hours, 49 minutes
    8/08/18 03:36 8/10/18 10:48 2 days, 7 hours, 12 minutes
    8/10/18 10:48 8/14/18 04:48 3 days, 18 hours, FALSE
    8/14/18 04:48 8/14/18 01:12 #NUM!
    8/14/18 01:12 8/17/18 17:31 3 days, 16 hours, 19 minutes
    3/13/18 16:48 4/20/18 05:31 37 days, 12 hours, 43 minutes
    4/20/18 05:31 4/24/18 18:43 4 days, 13 hours, 12 minutes
    4/24/18 18:43 6/16/18 00:00 52 days, 5 hours, 17 minutes
    6/16/18 00:00 6/21/18 00:43 5 days, 43 minutes
    6/21/18 00:43 7/02/18 06:43 11 days, 6 hours, FALSE
    7/02/18 06:43 7/10/18 06:14 7 days, 23 hours, 31 minutes
    7/10/18 06:14 7/10/18 19:55 13 hours, 41 minutes
    7/10/18 19:55 7/31/18 06:00 20 days, 10 hours, 5 minutes
    7/31/18 06:00 8/03/18 08:47 3 days, 2 hours, 47 minutes
    8/03/18 08:47 8/08/18 03:36 4 days, 18 hours, 49 minutes
    8/08/18 03:36 8/10/18 10:48 2 days, 7 hours, 12 minutes
    8/10/18 10:48 8/14/18 04:48 3 days, 18 hours, FALSE
    8/14/18 04:48 8/14/18 01:12 #NUM!
    8/14/18 01:12 8/17/18 17:31 3 days, 16 hours, 19 minutes
    8/17/18 17:31 8/30/18 09:11 12 days, 15 hours, 40 minutes

    V column is calculated as =TEXT(A60,"m/dd/yy ")&TEXT(R60,"HH:MM")
    W column is calculated as =TEXT(A61,"m/dd/yy ")&TEXT(R61,"HH:MM")

    where
    A60 is a manually entered field say 8/30/2018
    and
    A61 is a manually entered field say 8/17/2018
    and
    R60 and R61 is a manual entered field for outage time Say 5.5 representing 5 hours and 30 minutes.

    Thanks

  46. Cell A1 is having 20-09-2018 18:14:10
    Cell B1 is having 20-09-2018 20:00:30
    Objective is to find the time difference and used B1-A1.
    But error is coming like this #VALUE!
    Appreciate if you can suggest solution to get the time difference.

    1. Siva:
      I believe this is a formatting issue. Your Excel doesn't recognize 20-09-2018 as a date.
      So, then the idea is to get the date formatted in a way your Excel sees as a date. The way it stands, Excel sees it as text.
      Try changing the two dates to 9/20/2018, leave the time as is and see if you can then simply subtract the cells using B1-A1. If that works then you need to work on the formatting issue.
      Try changing the formatting of the cells to the date using the location option. In the Date there's the option to specify a locale or location. Find yours and change it there.
      You can try replacing the "-" with "/" and see if that works.
      If these approaches don't work, then you can split the dates and times into two columns, then split the dates into three columns and concatenate them into one cell using DATEVALUE, format the cell as Date subtract the cells and Bob's your uncle. This sounds harder than it is, in fact I used this last approach many times with data sets containing thousands of rows of data.
      Let me know how it goes.

      1. Cell A1 is having 20-09-2018 18:14:10
        Cell B1 is having 20-09-2018 20:00:30
        Objective is to find the time difference and used B1-A1
        As per your suggestion, I changed the date part to 09/20/2018 and applied B1-A1, it worked. But it is difficult to change it manually in all cells. Tried to format the cell using the location option. It didn't work if I replace the "-" with "/". Please suggest workable solution. My file is having thousands of rows of data with this format.

  47. I have a start_date and end_date, wants to calculate the number of hours & minutes between the date excluding weekends as well as time from 12:00 AM to 8:00 AM.

    Start_date End_Date Hour spend (excluding weekends & time between 12AM to 8AM)
    6/5/2018 6:54:15 PM 6/10/2018 11:28:46 PM

    Your prompt response is highly appreciated.

    Thanks & Regards
    Chander

  48. Hello !
    I´m struggeling with making a calculation between two times that consists of minute, seconds, hundredths
    ex 01:54,36

    The calculation should be
    00:47,26 - 00:46,58 = DIFF
    or
    01:14,29 -01:16,32 = DIFF
    or
    01:13,12 - 00:56,24 = DIFF

    The examples shows that the first time could be either faster och slower then the second one.

    Any ideas ?

    Thank you.

  49. 1935 to 0135 how many hours

  50. time in time out total hrs
    6:29:41 AM 5:28:07 PM ?
    6:44:17 AM 4:57:45 PM ?

    this is the case
    - our time in should be 7am and hrs should be counted from 7am to 11:30 for the morning time and from 1:30 pm onwards whatever the time is.
    - i want to know the formula for this.
    - lunch break should be less from 11:30am to 1:00pm same with 6:00pm to 7:00pm

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)