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 6. Total comments: 622

  1. I have a time set as 12:00 AM in A2.
    In B2 I try =A2-(1/24) and =A2-TIME(5,0,0)
    both give me ################################

    1. Hi Derek,

      Excel shows hash marks because your formula returns a negative value, and Excel cannot display negative times. If instead of 12:00 AM, you had, say, 12:00 PM, both formulas would work fine.

  2. Hi, I'm wondering if you can help, and I'm sorry if you have covered this already. I looked bit couldn't find exactly what I was looking for.
    Basically, I work 44 hrs a week. I'm wanting to work out if my hours are in the plus or minus
    My dates are in A2 to A53. Hours worked in that week B2 to B53, and total will be in D2. I need D2 to show me if I'm at zero or plus/minus in my hours
    Hope you can help.
    Thanks

    1. D3 =AVERAGE(B:B)-44
      [format as "[Green]0.###;[Red]0.###;0"}

  3. I'm working on a spreadsheet and have two columns where I have a Scheduled start time and an Actual Start time for an event. What I want to calculate is the amount of time - in minutes : seconds I am over or under the Scheduled start time vs Actual start time.
    Example:

    Sch Start time Actual Start time Difference
    09:00 09:03 +3:00
    09:05 09:04 -1:00

    Your help would be greatly appreciated -

    1. That formula works fine except for the condition when the Actual Start time is BEFORE the Scheduled Start time.

      Example:

      Scheduled start time is: 09:00
      Actual Start time is: 08:55

      The result is: 55 minutes 0 seconds

      What I'd like to see is +5 minutes (showing the actual start time was 5 minutes early)

      In the case where the
      Scheduled start time is 09:00
      Actual start time is 09:05

      The result is: 5 minutes 0 seconds.

      This works fine -- it's when the actual start time is before the scheduled start is the problem.

      Hope this helps.

      Thanks,

  4. i want 0 in case of error In negative values in calculating difference of time

    In Time Out Time Duty Hrs. Over Time
    09:00 17:00 08:30
    #############

    09:00 19:00 08:30 01:30
    09:00 16:00 08:30
    #############

    09:00 18:30 08:30 01:00

  5. Hi,

    How can I create a formula to format a cell if the date/time entered is within a specific time range, like between 2-4 hours after the time in the 1st cell.

    I need cell B1 to be highlighted if the time entered there is between 2-4 hours after the time entered in A1.

    For example:
    cell A1 - target time = 4/28/2016 9:00am
    cell B1 - actual time = 4/28/2016 12:00pm

    1. Hello Nor,
      I'd recommend using Conditional formatting with this formula for cell B1:
      =AND(((B1 - A1) * 24) > 2, ((B1 - A1) * 24) < 4)

  6. For example, I have two times series(column A and B), the difference between them is calculated in minutes(column c). Now I want to have excel tell me how many entries I have in the last column (C) which fall between 0-60 min, 60-120 min etc. I have tried COUNTIF formulas but the result is not correct. I then attempted to enter range I want to count for but the sheet adds Am or Pm making the calculation unfeasible. Any solutions/ ideas? I am attempting to ultimately make a frequency bar graph with the time data I have- any input is appreciated

    1. Hello Dolly,
      For us to be able to assist you better, please send us a sample workbook. Please add the link to this page and the number of your comment.

  7. I am trying to get the formula to calculate both the sum and average of timdif. I used:

    =DATEDIF(C2, TODAY(), "y")&" years,"&DATEDIF(C2, TODAY(), "ym")&" month(s),"&DATEDIF(C2, TODAY(), "md")&" Days"

    to give me my timedif (12 years,1 month(s),26 Days)
    . I have six cells D2:d7. I want two more cell that give me a sum of timedif, and an average of timedif. can someone help me out?

    Also I am looking for a formula that give me time left on a contract. Example:

    If a person has a contract that ends on May 1 2018, and I want a value that gives me time left from todays date. And in that value if the time left is less that 12 months I want the cell to be red.

    Can someone please help me out?

    1. Hello Ray,
      To calculate sum of time, please use the following formula:
      =SUM([array_with_timedif])
      To calculate average of time, please use the following formula:
      =AVERAGE([array_with_timedif])
      You can use this formula to calculate the number of days till the end of the contract:
      =DAYS([date_contract_ends], NOW())

      Please use Conditional Formatting to highlight the value in red.

  8. How do I calculate the hours and the breaks? For example
    Start time 8:00AM (Col.A), Break 1 12:15PM (colum B) End break 12:45PM (colum C) out time 5:30PM (colum D)

    Thank you!

    1. I'm trying to calculate the time sheet where they round up hours by quarters. For example if start time is between 07:08-07:22 it will automatically be calculated as .25 late or 07:23-0737=.5 and so forth .....
      Now how do I calculate the time sheet:
      Start time 8:38AM (Col.A), Break 1 12:15PM (colum B) End break 12:45PM (colum C) out time 5:38PM (colum D) where I also need to break the overtime?

      Thank you!!!!!

      1. Hello Viktoria,
        Please use the following formula to calculate working hours excluding breaks:
        =D4-A4-(C4-B4)
        To calculate the breaks time, please use the formula below:
        =C4-B4

        For us to be able to assist you better, please send us a sample workbook. Please add the link to this page and the number of your comment.

    2. and also, how do I add up the total hours in the end?
      Thank you!

  9. I need help calculating the transit time between time left loading point to time arrived and offloading point the next day.

    1. Hello Thandeka,
      Assuming first time is in A2 and the later time is in B2 put this in C2
      =B2-A2+(B2<A2) and format the cell as [Time]

  10. Thank you for this blog.

    I have 150+ rows of time durations in hh:mm:ss (ex:00:07:31) format that I cannot get to sum. I can get two rows to add together just fine, but not a sum of more than two rows. What am I doing wrong?
    Thanks

    1. Hello MikeM,
      For us to be able to assist you better, please send us a sample workbook. Please add the link to this page and the number of your comment.

  11. HI
    I HAVE PROBLEM WHEN THE EMPLOYEE
    WORK FROM 9:00 PM TO 3 AM NEXT DAY THE EXCEL SHEET CANT CALULATED
    PLEASE HELP ME

    1. Hello NADER NABIL,
      Assuming first time is in A2 and the later time is in B2 put this in C2
      =B2-A2+(B2<A2)
      and format the cell as [Time]

  12. Hii
    I have a problem. I have entered the start time and end time in month date year, hh:mm:ss format and now I have to calculate how many subjects in my study worked during the hours of 9am and 6pm and How many instances in the sample violated this rule? So I need to use what if function 6 pm is it? Plz help I am confused

    1. For us to be able to assist you better, please send us a sample workbook. Please add the link to this page and the number of your comment.

  13. I am unable to find the difference between two time given below:
    Last time is 22:59
    Current time is 07:45
    Pls help me.

  14. I need to count times which are in 1/100 seconds. Like start and finish times from actual running time (12:00:00:00) and (12:03:21:12)
    My Excel 2007 doesnt seem to do this?

    1. Sorry, rushed a little...solved...you can write your own format to custom, like h:mm:ss,00

  15. Start time End time
    1/31/2016 21:41 1/31/2016 22:32
    1/31/2016 19:59 2/1/2016 10:01
    1/31/2016 18:25 2/3/2016 16:58
    1/30/2016 18:59 1/30/2016 19:34
    1/30/2016 14:11 2/3/2016 8:22
    1/30/2016 13:08 1/30/2016 15:19
    1/30/2016 12:26 1/30/2016 13:28
    1/30/2016 11:45 2/2/2016 7:35
    1/30/2016 11:42 1/30/2016 12:44
    1/30/2016 9:23 1/30/2016 9:29
    1/29/2016 18:06 1/29/2016 18:47
    1/29/2016 17:55 1/29/2016 18:12
    1/29/2016 17:36 1/29/2016 18:45
    1/29/2016 17:28 2/1/2016 13:42
    1/29/2016 17:18 2/2/2016 18:08
    1/29/2016 16:26 2/3/2016 11:59
    1/29/2016 16:02 1/29/2016 17:54
    1/29/2016 15:36 1/30/2016 19:37
    1/29/2016 14:58 2/1/2016 8:12

    looking at the above, i want to calculate how many hours/minutes/seconds it takes ignoring Saturday's and Sunday's and any time between 5pm and 8am.Is it possible to have a formula like this?

    1. Hello, Barbara,

      To get the correct result you need a VBA script. Even if there may be a formula for this, it will be too complicated.

  16. 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

  17. 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.

  18. 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)

  19. 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

  20. 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.

  21. 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"

  22. 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

  23. 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!

  24. 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.

  25. 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

  26. 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

  27. Hi,

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

    Kindly help

  28. 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

  29. 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")))

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

    1. Very useful! Thanks.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)