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

  1. 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"}

  2. Thanks so much ,so usefull

  3. Hi,

    I have query. How to I calculate the time difference of two given times as:

    A1= 23:40 & B1= 00:30+1

    I want to calculate the difference between B1-A1.

  4. Request Help Please.

    I need to calculate time elapsed if time is greater than 6:00pm.

    Cell 1: = 5:50
    Cell 2: = 7:50
    Answer: 1
    Logic: Only the 2nd hour was counted because the service rendered was beyond 6pm was equivalent to 1 hr only

    Cell 1: = 5:50
    Cell 2: 8:50
    Answer: 2
    Logic: 2 hours were beyond 6:00pm

    Basically what is being counted is the time that elapsed beyond 6:00pm only

    Thanks!

    Franz

  5. I need to find to total time work for a employee change clock in and out on a different times of the day where
    A1=5:00 am
    A2=9:52 am
    A3=11:15 am
    A4=2:00 pm

    What is the formula to find the total time?

  6. I am trying to enter time into Excel for MAC as minutes and seconds but no matter time format I select it doesn't seem to be correct. I've tried using the format [h]:mm:ss or [mm]:ss and others and the cell shows just hours and minutes but the formula bar shows it as time in a 12-hour format with AM.

    Is there a way to have the formula bar also just display minutes and seconds and not time as in a clock?

  7. I am trying to find the average of cells that includes (in one cell) the following information:

    [# days, # hours, # minutes, # seconds].

    The 'average' function built into Excel doesn't seem to be able to calculate that information. Does anyone have any advice? I am not savvy with custom functions.
    Thanks!

  8. Johnny says: for you

    Can you explain how to calculate dealy

  9. I am doing scheduling. I need to calculate shift time in a single cell. Example: 9-4 = 7 hours. I would like to be able to add hours horizontally and vertically, but still view the schedule as 9-4. Can this be done?
    Thanks for any help

  10. cheryl says: for you

    TOTAL =TEXT(SUM(F11:F17),"[hh]:mm") 62:40

  11. Anil says: for you

    D2=05/13/2016 01:20:39 PM
    E2=05/13/2016 12:00:00 AM
    =TEXT(D2-E2,"HH:MM")
    Result
    13:20
    OR
    =HOUR(D22-E22) & " Hrs " & MINUTE(D22-E22) & "Min"
    Result
    13 Hrs 20Min

  12. eb says for you

    ST ET Calculate
    07:00 AM 03:20 PM =E11-D11 08:20
    07:00 AM 05:00 PM =E12-D12 10:00
    12:00 AM 11:00 AM =E13-D13 11:00
    07:00 AM 03:30 PM =E14-D14 08:30
    07:00 AM 03:20 PM =E15-D15 08:20
    07:00 AM 03:20 PM =E16-D16 08:20
    07:00 AM 03:10 PM =E17-D17 08:10
    TOTAL =TEXT(SUM(F11:F17),"[hh]:mm") 62:40

  13. First all thanks a lot for doing such a great efforts for our query.

    I have an query for you.

    there are certain time 17:30 in evening when the production gets started and it stops everyday morning 8:00 AM break time is 60 mins . Please calculate how much total work done in the days and night separately in term time.So that i can pay the wages and night incentive accordingly data is to large.

  14. C D E F G H
    MP Start MP End Slow Miles Slow Speed Track Speed Delay
    894.40 894.10 0.30 10 25 6.68
    to calculate my delay, here is the formula below
    =(E4+1)*((60/F4)-(60/G4))+(G4/10+0.5)-0.1*F4
    6.68 should be 7min.08sec 7.08
    I have multiple lines of these and then I calculate the total time but its actually not counting time, just summing up the numbers which creates a false set of numbers for my report which I'm calculation total delay time

  15. Really nice. Solved my problem.

  16. time in time out
    07:00 AM 03:20 PM
    07:00 AM 05:00 PM
    12:00 AM 12:00 AM
    07:00 AM 03:30 PM
    07:00 AM 03:20 PM
    07:00 AM 03:20 PM
    07:00 AM 03:10 PM

    would you please help me to subtract fist and sum up second, and calculate over time of the above times.
    thanks

  17. Hi All,

    I am analyzing the time duration for 15 000 theatre cases for a busy anesthesiology practice.

    A theatre case has a set starting and finishing time. Most cases are conducted between 0800hrs and 1700hrs - working hours. It is easy to calculate the duration of these cases.

    I have two questions:

    1) Should a case finish after 1700hrs (but start during working hours) how can I calculate what portion of the case (in minutes) was before 1700hrs and what portion was after 1700hrs, displaying this information in separate columns.

    2) Is there any way of identifying all cases that start after 1700 without simply sorting the data.

    thanks

    Roger

  18. Hi All,

    How to find the difference between 2 dates in the below format in either hours/Min.

    5/13/2016 1:20:39 PM and 5/13/2016 12:00:00 AM.

    Thanks in Advance

  19. I'm trying to calculat the difference in a time to track how much I"m paying out staff costs.So, 11am-7pm is 8hours. I want to then add up the end of the week which would be 35...Ideally I want to view the hours in whole numbers. I can't get it to show a number only a time and I can't get it to add up at the end of the row to show me how many total hours for that employee. Thanks in advance for the help

  20. I have a estimated finish time and then a actual finish time, sometimes the finish time is early and sometimes it is late. Each minute either early or late is worth 5 points. how do I do a formulae to show whether it is early or late and the points dropped. Finishing on the estimated finish time would be zero...thanks

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

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

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

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

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

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

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

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

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

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

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

    Total Time Formula=?

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

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

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

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

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

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

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

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

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

  41. Thank you Ablebits,

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

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

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

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

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

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

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

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

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

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