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

  1. hi,
    I want to subtract 5/13/2016 1:20:39 AM - 5/13/2016 12:00:00 AM
    in min

  2. Hi,
    I want to calculate time difference in 24-hours format and then I wanna use if function. For example, if the difference is 1:02 I want to display 1.5, if the difference is 2:01 I want to display 2.0. I do have 14 nested if does it work? I need help please.

    Thanks,

  3. hi,

    I'm using this formula to track OT time for my group:
    =INT(C4/480)&" days "&INT(MOD(C4/480,1)*8)&" hours "&INT(MOD(MOD(C4/480,1)*8,1)*60)&" minutes"

    Since we are salaried employees, we have a deal with our manager that any time spent after 40 hours will be converted to comp time. One of the guys i work with asked if it was possible to have a separate cell so we can deduct from the total value from the formula above.

  4. I want calculate only cell value convert into minute like:- cell Value "1.30" convert in minute as 90. Please Share any Solution

  5. scenario:
    Cell A1 : 5/1/2016
    Cell A2 : Time 22:00:00
    Cell A3 : ........
    Cell A4 : =IF(A1="","",A2+TIME(9,0,0))

    if 9 is added to A2, it moves to next day. I want to display next day in A3 when it crosses 24 hrs. Can anyone help me?

  6. can anyone help me how to convert 24 hours to hh/mm/ss?

    13:59:59 >> 00:00:01
    14:00:00 >> 00:00:02
    14:00:01 >> 00:00:03

  7. I WANT TO FIND THE TIME DIFFERENCE WITH OFFICE TIMINGS
    EXAMPLE ( WORKING HOURS 09 AM TO 05:30 PM)

    COMPLAINT TIME : 30/05/2016 11:00
    RESPONSE TIME : 30/05/2016 16:30
    COMPLETED TIME : 31/05/2016 12:00
    In the below example the working hours is 4 hrs

    is there any formula to calculate this , pl help

  8. Dear Sir/Madam,
    Can u please help me how to minus for 1 Hour break for example a1 8:00AM & b1 17:00Pm.

  9. Joe, do you have enough examples??? any you can use???? any you understand???

  10. Kindly i need your help in providing me with a formula explaining how can i set the execl to tell me what happen in a certain date from 6- to 10 and from 10 to 2 am in the morning i have the dates for 3 days and as well all the times in 24 hours
    I need your help urgently as i need the performance for one agent from 6 pm 10pm and then from 10pm to 2am in the morning and the cirteria is percentage and as well the code of the agent
    Many thanks in advance

  11. Time In: 19:00
    Time Out: 01:00

    Total Time Formula=?

  12. Hi I would like to add a formula to a cell where there would be a difference of time to when a ticket has started "Start Time" to when it was finished "End Time" however I would like the "End Time" to display at the current time to when it was finished. So example lets say i start at 9:52am the "Start Time" Cell should display "9:52am" and lets say I finish at 11:12" the "End Time" should display that. I have tried a few things but the "End Time" just doesn't change. Can someone please assist me with this?

    Thanks for your time

  13. Please try this formula: =IF(B11-A11 > 0,MINUTE(B11-A11) &" minutes "& SECOND(B11-A11)& " seconds","minus "& MINUTE(A11-B11) &" minutes "& SECOND(A11-B11)& " seconds")

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

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

  16. Hi,

    I have a problem in calculating the total hours.

    My start time is 10.30 pm and end time is 7.30 am. so my billable time would be 9.00 hrs. Lets say if i worked for 5 days, and my total billable time would be 45.00 hrs. But i have time with -ve format. I have used the below formula:
    Col D2: Start Time
    Col E2: End Time
    Col F2: D-F
    Col G2: SUM(F2:F)

    Please advise!

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

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

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

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

  21. Thank you Ablebits,

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

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

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

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

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

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

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

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

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

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

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

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

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

  35. 11:30
    What time will it be in forty minutes? Write your answer using numbers

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  50. Hi,

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

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