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

  1. I need to figure out the total time worked by an employee. we have a program that time stamps every action they make. I need to subtract any time gaps over 15 minutes and then total the time. Please help.

    16:21:47 '06/25/2016
    16:21:43 '06/25/2016
    16:21:37 '06/25/2016
    16:21:37 '06/25/2016
    16:21:37 '06/25/2016
    16:21:34 '06/25/2016
    16:20:43 '06/25/2016
    16:19:57 '06/25/2016
    16:19:57 '06/25/2016
    16:19:57 '06/25/2016
    16:19:50 '06/25/2016
    16:19:48 '06/25/2016
    16:19:48 '06/25/2016
    16:19:47 '06/25/2016
    16:19:36 '06/25/2016
    16:19:35 '06/25/2016
    16:19:14 '06/25/2016
    16:18:58 '06/25/2016
    16:18:52 '06/25/2016
    16:18:46 '06/25/2016
    16:18:46 '06/25/2016
    16:18:21 '06/25/2016
    16:18:14 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016
    16:17:55 '06/25/2016

    1. Add column B for gaps of 15+ minutes:
      =IF(A1-A2>1/96,A1-A2,"")
      Then hours worked =LARGE(A:A,1)-SMALL(A:A,1)-SUM(B:B)
      {this presumes the same date for all timestamps}

  2. oops

    =IF(AND($Login = start time),1,0) which is
    =IF(AND($H3=M$2),1,0)

  3. I want to populate "1" for the time between login and logout
    currently Im using =IF(AND($Login=Start time),1,0) wich is =IF(AND($H3=M$2),1,0). however for the time login 10:30PM and loug out time is 7:00 am it does not give a required results . Could anyone help me on this

  4. hi,
    I want to subtract 11/09/2016 8:20:00 PM - 12/09/2016 2:00:00 AM

  5. i want to multiply time by currency with if statement like
    1:10 if time is 1:10 show me $150
    how i can make this astatement in excel

  6. Hello,

    I am Adam and just joined this forum. Could anyone help to calculate time durations in excel when data (In and Out) like in punch cards are in adjacent column and row. Is there any formula to handle this situation.

    Thank you

  7. Hello I m Sufiyan

    How to Calculate in Time and Out Time suppose here workers punching machine but machine showing this type timing 15:02:00 .but i need in one cell in time and other cell out time i need this type formula who came before 11:30:00 then will be in time and after 11:30:00 then out time
    =IF(AND(L2>TIME(11,30,0),L2<TIME(24,0,0)),"out time","in time") i m using this formula but not getting accurate result
    L2 is timing 15:02:00

    pls suggest formula

  8. A have several times in one column. first cell is starting time, last cell is ending time. Multiple combinations where number of cells between starting and ending time is not same number.
    I need to calculate durations for each.

    thanks in advance

  9. Please give me a positive feedback soon!!!!!!!.

  10. 8/22/2016 2:20:00 PM
    8/19/2016 4:35:00 PM
    how to calculate total time
    pls suggest formula and send to my mail ID

  11. in cell A1 : 26-08-2016 05:00:00 AM
    In cell B2 : 26-08-2016 10:00:00 AM

    we required starting time to ending time with a fixed interval like 00:15 or 00:30 or 01:00

    Suppose for 01:00 hr interval we required in another sheet
    A1: 05:00
    A2: 06:00
    A3: 07:00
    A4: 08:00
    A5: 09:00
    A6: 10:00

    Suppose for 00:30 hr interval
    A1: 05:00
    A2: 05:30
    A3: 06:00
    A4: 06:30
    A5: 07:00
    A6: 07:30
    A7: 08:00
    A8: 08:30
    A9:09:00
    A10:09:30
    A11:10:00

  12. Thank you so much.. its very helpful.

  13. 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/15/2016 10:00:00 AM.

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

  15. I need to convert time logged into a computer from logged in time on a time card.

    Example:
    A1=Logged on for 10(hours):04 (minutes):00
    B1=Logged working hours for 10.0
    needing Column C1 to be the difference...which would be =00:04:00

    Implying the person worked 4 minutes over the 10 logged hours on the time card.

    I tried converting to many different numbers but can't seem to figure it out. Help please (-:

  16. Hi Need Help!

    i have two time A1 = 9:30 AM B1 = 12:30 PM

    Difference of hours = B1 - A1 = 03:00 hours

    Total days is 19 - Now i need to calculate no of hours per day * total days
    ie: 3:00 hours *19 , answer should be 57 hours

    but i am unable to get this - please help

  17. Hi,

    I need a formula for working out my extra hours worked - I should work 37.5 hours but often do over this and would like to be able to show this on my time sheet - how do I subtract my contracted hours from my hours worked in hours and minutes?

    Thanks!

  18. I never found such a useful and coherent information at a single page .

    It helped me a lot in my long pending project.

    Greate Work.

  19. sunsunj j7 of sell 499. oder 2 phone

  20. hi have a good day ..
    how to solve this problem.. ?
    what is the formula of time difference

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

  22. Thanks so much ,so usefull

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

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

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

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

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

  28. Johnny says: for you

    Can you explain how to calculate dealy

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

  30. cheryl says: for you

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

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

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

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

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

  35. Really nice. Solved my problem.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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