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 28. Total comments: 1023

  1. DEAR SIR/MAM

    IF DATA IS LIKE THIS
    8:30 6:30 HOURS SHOULD SHOW LIKE 10:00 IN EXCEL FILE PLEASE TELL FOIRMULA FOR THIS

  2. How to calculate night duty hours from following times. NDA hours=22:00 to 06:00.
    A1=31-01-16 21:00
    B1=01-02-16 08:00

  3. Hi

    I need help creating a formula in excel that counts the number of transactions between certain time zones in a day. Each transaction is timed, I want to breakdown the daily list I get into 2 hourly segments counting actions between 09:00-11:00,11:00-13:00,13:00-15:00 etc.

    Thanks

  4. gud morning madam
    would please help me for getting the sum total of time and minutes in horizontal type
    (ie:8:15(15times) & 6:45(3times)i got the total in vertical but horizontally how please )

  5. Hi,

    I have a spreadsheet that works out the difference between 2 times (I just manually type in the time; 15:27)
    Then for the difference I just use a simple subtraction, which if 1 is 11:30 and the other is 16:00, it says 4:30, which is great.
    I want the difference to only be between 09:00-17:00 so if the first time is 15:30 and the second is 10:30 I want the time difference to be 3hours.
    Is this possible as I don't use dates in the spreadsheet?

    Thank you

  6. How to calculate two different row time is more than 4 hrs.fail or less than 4hrs pass
    01/01/15 10:00
    02/01/15 14:00

  7. I need to calculate the difference between two times in the same column, in a timetable. I need the difference between each time in the column. Say, the time between B5 and B4 is 40 minutes. The times go down to B94.

    I used =TEXT(B5-B4,"h:mm") which was simple enough and worked, in the format I needed, (:40) in the first cell, but it didn't work if I extended the selection to all the cells in the column. The subsequent cells are showing the same answer, but are not correct. I have the cells in the time format, "1:30 PM". How can I get it to work for each iteration?

    The second part of the question-if the previous time is larger that the following time, like 1:30 - 12:45. I get a VALUE alert. I know this is because you can't have a negative number. I tried to write an if statement: If(B4>B5), but I couldn't get it to work.

  8. Hi,

    Firstly this page is very useful, thank you for creating it.

    I am working on a project where I need to deal with time. So in between time I need to remove the regular intervals.

    Example:
    If I am performing a certain tasks
    Task 1: 6:00 AM to 9:00 PM
    Task 2: 9:00 AM to 12.30 PM
    Task 3: 12.30 PM to 2.30 PM

    I need time difference between them and also I want the break time to be removed.

    Break 1: 8:00 AM to 8:15 AM
    Break 2: 11.00 AM to 11.30 AM
    Break 3: 12.50 Pm to 1.00 PM

  9. For anyone that runs into this, I fixed it using MOD((TIME(HOUR(L5),MINUTE(L5),)-TIME(HOUR(J5),MINUTE(J5),)),1))-S5 where s5 is my 30 min lunch formatted as Time(0,30,0). Cells L5 and J5 are my preformatted cells that export from our time clock and they look like this:
    J5 L5
    01/04/2016 07:00 AM 01/04/2016 04:32 PM

  10. Hi,

    How do you subtract total Hours:miniutes ex. 37873:34 - 37152:57 to return xxxxx:xx as your answer.

  11. Hi, I am having an issue with times and dates that go from PM to AM. If one work shift is 3:30 PM - 12:00 AM and another is 3:30 PM to 01:00 AM, I cannot get the hours to calculate correctly using the 24 hour format. If I subtract the times and multiply by 24, I get -15.75. At first I thought I should multiply by -12 to correct for this error, and that gets me close, but it is still incorrect. Can you help me with this?

  12. hi,

    can you please help me!
    I am trying to figure out how to calculate my average running times, for example, I do a lot of 5km runs and my times vary between 25 and 32 minutes, there would be seconds as well, I am using excel 2016 and I am new to excel, I tried doing it myself already and I am only getting average times, as in actual times, for example, if I have a time of 35:07 it comes up in excel as 00:35:07 so it looks like normal time in 24 hour clock? how can I do this please? your advice is very much appreciated.

    1. Hello, Eddy,

      Please use the formula =AVERAGE(A1:A4), where A1, A2, A3, A4 are times like 0:35:07, 0:25:15 and so on.

  13. This has been so useful to me in helping me with accounting and management ...THANKS SO MUCH!! :)

    This is PERFECT !!

  14. Hi Guys,

    I Have data in hh:mm:ss format but some cells starts with only " : " eg :44:00

    i have to daily download the dump and I correct it manually is there any formulae to correct this...

    1. Hello, Asaad,

      To correct these values you can add a helper column with this formula:
      =IF(LEFT(A1; 1) = ":"; TIMEVALUE("0" & A1); A1)

  15. i Am looking formula that if the both the time formats are different then how can we subtract.
    Ie:Start time 18:59:59 and end time 07:15PM

    1. Hello, Prasanna,

      Please use the following formula
      =IF(OR(IFERROR(FIND(A2; "AM"); FALSE); IFERROR(FIND(A2; "PM"); FALSE)); TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2; "PM"; " PM"); "AM"; " AM")) - A1; A2-A1)

      where A1 = 18:59:59 and A2 = 07:15 PM

  16. So Clever. this blog give me solution. many thanks for help

  17. I am looking for a formula that calculates minutes or hours when timeframe extends from PM to AM. For example, 9:00 AM to 12:30 AM should equal 3.5 hours or 210 minutes.
    Thanks!

    1. Hello, Farhan,

      Please use the formula below:
      =HOUR(A2-A1)*60 + MINUTE(A2-A1)

      It will return the number of minutes between A2 and A1.

  18. I work at a car factory and i created a spread sheet that calculates how many vehicles an hours can be made with a certain amount of parts that we have on hand. I wanting my spread sheet to automatically deduct the number of vehicles that can be made with the part on hand every hour. Can anyone help me out.

  19. How do you add two dates together that are greater than 31 days and still keep it as a number, not a text?

    I am using the following formula:

    =(H4-B4)-((INT(H4)-INT(B4))-(NETWORKDAYS(B4,H4,$M$6:$M$15))+1)

    This formula works fine on all dates that are lower than 31 days.

    I have one date that starts on 6/5/2015 11:29:28 AM and completes on 10/16/2015 3:29:09 PM, the formula should be 95 days total time and the answer the formula gives me is: 2 d, 3 h, 59 m.

    How do it get it to give me the correct timelapse of 95 days in days, hours, minutes format. It has to be in a numerical format so I can average out the completion time for the total year.

    1. Hello, Michelle,

      Please use the formula below. If will return the difference in the needed format between A2 and A1:
      =INT(A2-A1) & " d, " & HOUR(A2-A1) & " h, " & MINUTE(A2-A1) & " m"

  20. I need to calculate am and pm start and finish times
    1700-0500
    0500-1700
    1000-1800
    1800-0500 etc

  21. Hi Svetlana,

    Could you tell me how I could perform a subtraction for multiple days at different times each. For instance,

    Start date and time : 5th April 2015 10 am

    Data:
    5th April 2015 11.30 am
    5th April 2015 4.30pm
    6th April 2015 3.30am
    8th april 2015 8.30am
    9th April 2015 10am

    I would like to calculate the total cumulative time at the end and possibly in between each interval by subtracting the starting date and time. I could manually do it using the arithmetic method but if I have 100 data it will take a really long time.

    Would be great if the output could be delivered in a single cell.
    Appreciate your thoughts on it.

    1. Hello, Sean,

      Please insert a helper column with this formula:
      =(DATE(INT(MID(A2;FIND(" ";A2;FIND(" ";A2)+1)+1;4));MONTH(1&LEFT(MID(A2;FIND(" ";A2)+1;FIND(" ";A2;FIND(" ";A2)+1)-FIND(" ";A2)-1);3));INT(LEFT(A2;FIND("th";A2)-1)))+TIMEVALUE(SUBSTITUTE(MID(A2;FIND(" ";A2;FIND(" ";A2;FIND(" ";A2)+1)+1)+1;2);".";"")&":"&IF(ISERROR(FIND(".";A2));"00";MID(A2;FIND(".";A2)+1;2))&" "&IF(ISERROR(FIND("am";A2));"PM";"AM")))-(DATE(INT(MID($A$1;FIND(" ";$A$1;FIND(" ";$A$1)+1)+1;4));MONTH(1&LEFT(MID($A$1;FIND(" ";$A$1)+1;FIND(" ";$A$1;FIND(" ";$A$1)+1)-FIND(" ";$A$1)-1);3));INT(LEFT($A$1;FIND("th";$A$1)-1)))+TIMEVALUE(SUBSTITUTE(MID($A$1;FIND(" ";$A$1;FIND(" ";$A$1;FIND(" ";$A$1)+1)+1)+1;2);".";"")&":"&IF(ISERROR(FIND(".";$A$1));"00";MID($A$1;FIND(".";$A$1)+1;2))&" "&IF(ISERROR(FIND("am";$A$1));"PM";"AM")))

      Here A1 = 5th April 2015 10 am
      A2 = 5th April 2015 11.30 am
      A3 = 5th April 2015 4.30pm
      and so on

  22. Hi,

    This page is very helpful, but I have one question.

    Is there a way to do a time calculation in a single cell?
    I would like to enter something like this, which is similar to what you can do with regular numbers:

    =14:03:14 - 13:40:40

    And have the cell display the time difference, it seems a waste to use three cells to do time math.

    Any help would be great,

    Paul

    1. Hi Paul,

      Please try to enter the times in quotes:
      ="14:03:14" - "13:40:40"

      1. Cell A1(14:03:14) Cell A2(13:40:40) and this formula =MOD(A1,A2)*24 RESULT 9:01:36 i DON'T KNOW if this will help you!

  23. i need some help i have these two dates(11/16/2015 02:32:19 PM 11/16/2015 02:32:19 PM),when i substract them to get the differences, the result is (#VALUE!)
    Any solution i tried everything but the result didnt change best regards

    1. Hi H.K.

      when I entered those dates in my test worksheet (cells A1 and A2), the formula =A1-A2 returns 0:00:00 exactly as it should because the dates are identical up to a second. What formula do you use?

      1. The best formula i use all the time is =(A2-A1)*24 or the best one is =IF($A1="","",(MOD(A2-A1,1))*24) IF cel A1, 2, 3, and so on is empty i said give me empty other wise subtract A2 to A1 and Multiply 24. This formula will never give you a negative. Example. if you have started working 4:00 PM and ended 1:00 AM without using MOD, it will give you -15 but Mod will give you exactly hours of work which is 9 hours. Good Luck.
        Also if you want to subtract 2 date i use this =DATEDIF(A1,A2,"D")+1 Will give me exactly how many days. if you need Years, change D to Y. Month use YM.

        Good Luck!

        1. hello i have a problem , i have 2 time-in & time-out ex.time-in A1=9:05 AM ,time-out A2= 11:45 AM , time-in A3= 12:00 PM , time-out A4=6:00 AM . how to calculate this in 1 sheet or 1 Code ? Every body Can Help me !!!???

          1. i mean in A5 or in 1 code solution ??

            1. in 1 formula ?

      2. hello i am using the same one you used and other ones but it gave me the same results

        1. any answers???!!!!

          1. H.K,

            Sorry, it's hard to determine the cause of the problem without seeing your data and formulas. The most obvious thing that comes to mind are dates formatted as text.

            To check your formula, you can type those 2 dates manually in a new worksheet, and then enter the formula. If the formula works there, then the problem is most likely with the formatting of your source data.

  24. Hi,
    i have a Problem in calculation of Employee Time-in and Time-Out Time-in is 9:00am and Time Out is 24:30 Am then how to get result in proper format
    Thanks

  25. I want to enter swimmers times, eg. 1.26.00, 1.20.00 1.18.00 etc and find the fastest time which would be 1.18.00 The formula for MIN works alright if it is just in seconds, 56.45 and 54.25 but soon as I enter minute it comes up with error.

    Also to show the improvement from slowest time to fastest.

    Hope you can help. Thankyou

  26. My result in Excel is in fol format
    45:30 i-e (45 Hours 30 Mins)
    Now i want to convert it into days and minutes i-e 1 day 21 hours and 30 mins.my start time is 1800 hrs, so result may be shown like d+2, 1530 hrs.
    Kindly help me in this regard

  27. I am looking for a formula that gives me a difference of time,
    For eg. A1 = 8:00 PM
    B1 = 5:00 AM

    I want the difference of hours in C1 cell.

    And same should be applied for:
    A2 = 8:00 AM
    B2 = 5:00 PM

    Kindly help me with this

  28. hey, thank you for such a detailed explanation of using various methods to calculate time... really informative.

    i have date and time in two different columns. Which is actually the Login and logout time & i want to calculate the difference. In some cases the log out is after 11.59pm and i'm not getting the exact difference.

    If i need to combine both the date and time column and then find the difference, how do i do it?

    Or if there another way!

  29. If the data is something like this, how do we calculate the (1) actual time taken (2) average time taken for a month.

    01.07.2014 00:00 10:26:16 AM
    01.07.2014 00:00 11:55:09 PM
    02.07.2014 00:00 11:40:59 AM
    02.07.2014 00:00 2:54:54 PM
    02.07.2014 00:00 3:15:37 PM
    02.07.2014 00:00 11:57:43 PM
    03.07.2014 00:00 12:11:55 PM
    03.07.2014 00:00 2:58:01 PM
    03.07.2014 00:00 3:25:53 PM
    03.07.2014 00:00 8:45:34 PM
    04.07.2014 00:00 2:42:27 PM
    04.07.2014 00:00 9:16:39 PM
    04.07.2014 00:00 9:17:40 PM
    04.07.2014 00:00 9:18:03 PM

  30. Need help with time/date data

    I need to compare two excel sheets. These two excel sheets will have date/time data. I want to ignore the date/time during comparison. The date/time will display in the bottom of the sheet but not specific to one particular cell.(since the excel is based out of the report).

    Any help is appreciated.

  31. hi

    my question is
    how to make sheet for automatic change amount change in time on my computer time ???

    for example
    >>>>> bank charges time 11:00 am to 3:00 pm is 17
    And after 3:00 Pm bank charge is 17+6 = 23
    How to use formula and condition if or other please prescribe

    How I make this type sheet time to time change sheet

  32. need help on the formula for the difference in minutes:

    ex:
    A1 - expected time
    B1 - delivery starts
    C1 - time delivered

    What if the time is

    18:00, 17:30, 17:45 // what is get is 23:45

  33. I am looking for a formula that would give me the time interval between a start time and different time intervals every hour ... for example cell B2 start time is 6:00am, in cell C1 I want to enter 1.5 and in cell B2 I want excel to calculate the formula as indication 1 and one half hour interval was worked.

  34. I am looking for a formula that calculates minutes or hours when timeframe extends from PM to AM. For example, 9:00 PM to 12:30 AM should equal 3.5 hours or 210 minutes.
    Thanks!

    1. Greg, please let me know if you have ever solved this issue, i too am trying to figure this out. Thanks.

      1. Same:- Sells C27= 06:52:00 PM and D27= 03:14:00 AM. =TEXT(D27-C27,"h:mm") Result:- #VALUE!

      2. I am also trying to solve this problem. If anyone could help it would be greatly appreciated.

        1. Hi there,

          This can be calculated as: (12:30am-9:00pm)*24= -20.5 hours.

          Or: (12:30am-9:00pm)*1440= -1230 minutes

  35. I Need Formula for Calculate To different Time To numbers.
    (i.e Cell A1= 11 Hour, Cell B1= 30 min) I need Ans is 11.5
    Please give me salotion

    1. Hi Ramesh,

      If A1 and B1 are numbers, 11 and 30 respectively, you can use the following formula: =A1+(B1/60)

      1. Thanks

  36. Hi,

    I have a time in negative as -135:45 and want this to convvert in day by in negative balance

    Kindly help

  37. Hi,

    I need a formula to calculate the time difference with sum subtraction on time

    for example:

    7/25/2015 08:42 PM - 7/26/2015 07:42 PM = 23:00

    but i need to avoid the time of 13 Hrs ( 7/25/2015 9:00PM to 7/26/2015 8:00 AM) to show me only 10 hrs

    Kindly help me on this

    Thanks in advance on your kind help.

    1. I need a formula to calculate the time difference with sum subtraction on time

      for example:

      19:57 PM -8:10 AM = 12:13 HRS

      but 19:57 PM -8:10 AM to show me only 11:47 hrs

      Kindly help me on this

  38. If the result is a negative number, the TEXT formula returns the #VALUE! error.

    How can I avoid getting this error message? I am not able of calculating the average time in a range that was previously calculated as time difference.

    So if a cell was previously calculated as a time difference and the results was negative, I am getting #VALUE!

    1. Hi Derek,

      If you want to display negative time differences as well, like -1:00, then you can use either of the following formulas:
      =IFERROR(TEXT(B2-A2, "h:mm"), CONCATENATE("-",TEXT(ABS(B2-A2), "h:mm")))

      =IF(B2>=A2,(TEXT(B2-A2,"h:mm")), CONCATENATE("-",TEXT(A2-B2, "h:mm")))

  39. That's so useful.
    Thanks a lot.

    1. Very useful! Thanks.

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 :)