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

  1. Thank you Ablebits,

  2. Hi can you help me make a formula .that compute the time..

    For ex:

    Time in:8:00am
    Time out:8:00pm

    What i want is to automatically calculate the time exactly 8 hours only and the excess time will be put into overtime...

  3. 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]

  4. I need help to split no. of spots time bands in % as below

    St. Time End Time spots 0600-1200 1200-1800 1800-2000 2000-2200 2200-2400
    08:00 00:00 66 17 25 8.3 8.3 8.3
    07:00 23:59 12 3.5 4.2 1.4 1.4 1.4
    08:30 13:00 8 6 2

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

  6. 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]

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

  8. Count of ISCI Column Labels
    Row Labels iNet-J iNet-V satellite Grand Total
    12 AM 907 371 188 1466
    1 AM 3 3
    Grand Total 907 374 188 1469

    This is what my pivot looks like once I select my value. It should just display my TTD value as 00:00:00, 00:01:28 etc, but instead breaks the value out by Hour, Min.

  9. I have a calculate time value that is the result of this formula: =IF((R2-P2)*24>24,0,(R2-P2)) -- all works well, but when I bring this into a pivot table and select the product value, Excel keeps breaking it out to TTD(my calculated value formatted as [h]:mm:ss) to add Hours, Minutes to my value so my pivot doesn't just show the 24hr value I want. So now the chart I want isn't just the straight 00:00:00 value.

    Any thoughts on how to stop pivot from changing my value?

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

  11. This is quite useful.

    I was looking something similar to NETWORKDAYS(), which ignores the non working days between two dates.
    Is there anything similar for time as well.
    e.g Start time: 3/11/2016 4:00:00 PM
    End Time: 3/14/2016 4:00:00 PM
    If we do (ET-ST)*1440 = 4320 min
    My expectation is 1440 min (As 3/12 & 3/13 is Sat & Sun)

    Is there a way to calculate this

  12. When adding over time in a weekly sheet if a person have worked 3 and half hrs. Should i use 3.5 or 3:30 and what formula should i use for the total

  13. please anyone reply ,that how to sum time this forula,(0days,0hour,0minute and 0second)

  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. 11:30
    What time will it be in forty minutes? Write your answer using numbers

  16. hello,

    A B C
    1:22:17 AM 1:29:16 AM 00:06:59

    suppose time is 6 minutes 59 seconds

    Is it was displayed as 7 minutes

    I want a formula pls help us

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

  18. Hi I just want to do an analysis for orders received for every 30 mins. in an Hour. I have the data in the following format:

    12/02/2016 00:01:13
    12/02/2016 00:02:30
    12/02/2016 00:02:56
    12/02/2016 00:03:16
    12/02/2016 00:04:15
    12/02/2016 00:03:34
    12/02/2016 00:49:07
    12/02/2016 00:49:31
    12/02/2016 00:49:38
    12/02/2016 00:50:23
    12/02/2016 00:50:48
    12/02/2016 01:03:56
    12/02/2016 01:03:53
    12/02/2016 01:04:12
    12/02/2016 01:33:26
    12/02/2016 01:34:52
    12/02/2016 01:35:51
    12/02/2016 01:37:15
    12/02/2016 01:37:46
    12/02/2016 01:39:18

    i want the above data to be like:

    12/02/2016 00:01:13 12:00-12:30 am
    12/02/2016 00:49:31 12:30-01:00 am

    Please let me know if this is feasible.

  19. Dear mam,

    How to exclude sunday when we have calcuated time with below format.
    12/02/2016 1:30 PM -12/02/2016 10:00 AM =

    Reg.
    Shashi

  20. If I have to work 37.5 hrs in a week (5 days), how do I calculate :

    a) how many hours I have to do in one day for 9 days (to get one day off a fortnight).

    b) How many hours I have to do in one day for 19 days (to get one day off a month).

    THEREFORE 37.5 HRS PER WEEK = 7HRS AND 30MINS PER DAY FOR 1 WEEK.

    I GUESS I JUST NEED THE FORMULAE TO CALCULATE (37.5*2)/9 (IN TIME) AND 37.5*4/19 (IN TIME) PLEASE!

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

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

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

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

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

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

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

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

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

  30. Hi,

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

  31. 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?

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

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

    This is PERFECT !!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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