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

  1. Hello,

    I need to substract the breaks from the gross working hours. I can't find the right formula.

    Example : if working hours 3.5 to below 6 hours (3.5 hours to 5.9 hours) substract 30 min break time, if hours 6 to overs, substract 60 minutes.

    Could you please hepl me for solve this?

    Thank you

      1. Hi! Sorry to bother you. For your info, I've tried this formula many times, but it's not working with my time. If you could please do my formula again with my time, I will be grateful to you.

        Thank you.

  2. Hello,

    How to create formula that calculate time in a single cell? For example Cell A1 (4:30pm - 7:30pm) Cell A2 ( 3 hours). Please advice.

    Thanks

    1. Hello!
      Please check the formula below, it should work for you:

      =(MID(A1,SEARCH("–",A1,1)+2,LEN(A1)-SEARCH("–",A1,1)-3)&" "&RIGHT(A1,2))-(LEFT(A1,SEARCH("–",A1,1)-4)&" "&MID(A1,SEARCH("–",A1,1)-3,2))

      1. dear alex,

        thanks for the formula, but i still couldn't get it. possible to assist me in more detail for the formula above?

        Thanks.

        2. with the formula you given, the answer i get at cell A2 was 0.13 instead of 3.

  3. Hello,

    I need to substract the breaks from the gross working hours. I just can't find the right formula even after reading so many comments.

    The conditions are: if working hours >=9 substract 30 min break time, if hours are between 9 and 10, substract 45 min break time, if hours over 10, substract 60 minutes.

    Could you please give me advice for this?

    Thank you

    1. Hello!
      The formula below will do the trick for you

      =IF(HOUR(A1)>=10,A1-60/1440,IF(HOUR(A1)>=9,A1-45/1440, A1-30/1440))

  4. 01-Jul-21 22:00 02-Jul-21 06:00

    how we can get working hours in this condition

  5. I am a swim coach and am trying to do an excel sheet where I take a swimmer's best time when they arrive and find the difference when they are done (did they drop time). I have an excel sheet pretty much done and it works well when the time is within the same minute (1:57.56 to 1:55.46 = -:2.10) I use #":"##"."00 as my cell formula. The issue come when I have a time in 2 different minutes (2:01.91 to 1:57.41) this should equal -:4.50 but I get -:44.50. Do you have guidance for me? Thank you in advance

      1. Thank you. My formula lets me put in the times without having to type the punctuations and it lets the time read correctly no matter if it is 10 minutes or 30 seconds. If you do that (type just numbers) you should get the formula to work.

        mm:ss.00 does get the correct number but it does not show a negative (I've played with how but can not make it happen) and it does not format well at least not aesthetically.

        Any other advice?

        1. Hi!
          You will not be able to subtract minutes, since there are 60 seconds in 1 minute. In your case, 1 minute is 100 seconds.

  6. I have an issue with a simple greater than calculation involving time (24hr) and can't seem to find a solution to show that:

    =IF(F1>J1,0,100)

    "23:00" is NOT greater than "0:00" but excel decides this is false.

    How could I determine this when the time rolls over?

    1. Hello!
      Why do you think 23:00 is no more than 0:00? What result do you want to get? Please describe your problem in more detail.

      1. Hi Alex,

        The problem is to figure out If a shipment was sent late, or on time, and then sum up the late ones...

        So with a simple formula, you can see results are not desirable when that midnight time rolls over.

        IF SUM(A1-B1) >0, Then Count it

        LEFT DUE DIFF
        18:30 20:00 -1:30
        20:45 23:00 -2:15
        19:50 21:00 -1:10
        20:45 22:00 -1:15
        22:10 0:00 22:10 <----Not really late
        23:48 1:00 22:48 <----Not really late
        22:40 23:30 -0:50

        Moving forward, we can add the date and that makes it accurate, but the issue is that I've got old entries to tally up and changing the time field to a date just makes it "1/1/1904" for every day.

        Hope that describes it better?

        1. Hi!
          Time 00:00 is less than 23:00 since 00:00 is the beginning of the next day. You can calculate the time difference correctly only within a day. If the start and end times are different days, then you need to use the date and time.
          You have not explained how to determine if the package was sent at the wrong time.

  7. Thank you for this, it was a true help. :)

  8. Hello

    I am trying the method of B2-A2 (Updated date/time - Created date/time) and then formatting the results cell with d "days," h "hours," m "minutes and" s "seconds" and all seems well until I get to a certain value and then it is not displaying results correctly. I worked around this by using =INT(B2-A2) & " days, " & HOUR(B2-A2) & " hours, " & MINUTE(B2-A2) & " minutes and " & SECOND(B2-A2) & " seconds" but now I'm trying to average the results of the original B2-A2 values and am getting a value of 61.26262 which when formatting the cell with d "days," h "hours," m "minutes and" s "seconds" I should get 61 days... but am getting 1 days, 6 hours, 18 minutes and 10 seconds. How can I format the cell values to return the correct 61 days? Is it as simple as changing the formatted value of 1 days, 6 hours, 18 minutes and 10 seconds to 61 days, 6 hours, 18 minutes and 10 seconds?

      1. Hello sir ,
        this formula is too good. And I want that same formula in Power BI . Can you please tell me what would be the formula for same purpose in Power BI ?

  9. Hi
    When I'm trying to sum the total time in HH:mm
    Getting the total wrong I have applied the formula =sum(b2:b5) but still getting the wrong total

  10. Hello,
    what if i need to calculate daily working hours (8) hrs if exceed 8 hrs to calculat the over time for the three shifts and the timing concidering timing from 19:00 to 07:00 as 1.5 rate and other hours will be in 1.25 rate

    means
    Example
    Exmp1-08:00 to 20:00 8 hrs duty 4 hrs Over time ( 3hrs @1.25 & 1hr @1.5)
    Exmp2-20:00 to 05:00 8 hrs duty 1 hr Over time (@1.5) (till 07:00 the rate will be 1.5)after 07:00 rate will be 1.25

    Please advise

  11. Looking to calculate the time difference excluding weekends and business hours for the time a user submits a support request. Weekends are sat/sun and business hours are 8-5.

    Example time of the support request:

    Submitted at 7:00am on Friday 5/7/21.

    Request completed at 10:30am on Monday 5/10/21.

    The answer when calculated manually would be 10.5 hours to complete the request.

    What would the formula be for this?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(NETWORKDAYS.INTL(A2,B2,1)=1,MIN(TIME(HOUR(B2),MINUTE(B2),0),TIME($C$1,0,0))-MAX(TIME(HOUR(A2),MINUTE(A2),0),TIME($B$1,0,0)),(NETWORKDAYS.INTL(A2,B2,1)-1)*($C$1-$B$1)+MAX(0,MIN(TIME(HOUR(B2),MINUTE(B2),0),TIME($C$1,0,0))-TIME(8,0,0))+TIME(18,0,0)-MAX(TIME(HOUR(A2),MINUTE(A2),0),TIME($B$1,0,0)))-IF(TIME(8,0,0)-(A2-INT(A2))>0,TIME(8,0,0)-(A2-INT(A2)),0)

      A2 - start time
      B2 - end time
      B1 -start workday (8)
      C1 - end workday (17)
      The answer calculated 9+2.5=11.5 hours
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alex,
        You formula helps a lot for me. thank you so much for that. pls guide me to exclude weekends (Saturday & Sunday) from the below formula.
        =INT(N374-H374) & " days, "&HOUR(N374-H374) & " hrs, " & MINUTE(N374-H374) & " mns"
        Output is 20 days, 2 hrs, 44 mns.

          1. Alex,
            This is brilliant. It's working. Thank you so much :-)

      2. What if not considering Start workday and end workday and instead just considering 8 hrs a day

    2. Sorry, 11.5.

  12. How to calculate difference of below time stamp

    Respone Time (Before Entry) Respone Time (After Entry)
    2021-04-20-17.05.26.661101 2021-04-20-17.05.27.149143
    2021-04-20-17.05.56.336787 2021-04-20-17.05.56.507989
    2021-04-20-17.05.57.052277 2021-04-20-17.05.57.129936
    2021-04-20-17.06.58.825463 2021-04-20-17.06.58.913969

  13. I have a question here. How to get the hour after minus from 73:50 - 70:91?

    my results show #value

    Hope to hear from you soon.

    Thanks.

    1. Hello!
      Time cannot be written as 70:91. There cannot be more than 60 minutes in an hour. If instead of 70:91 you write 71:31, then you will succeed.

  14. Thank you for the 'time in 1904 format' tip.
    In a Mac it's under Excel preferences, Calculation then tick 'use 1904 date system'

  15. Hello,

    how to write coming late formula for 3 shifts (24 H).

    1- 00:00 AM to 08:00 AM
    2- 08:00 AM to 04:00 PM
    3- 04:00 PM to 00:00 AM

  16. In normal excel sheet I am try to calculate manpower efficiency which need time starts and time end. What is the formula for time starts if the person starts work by pressing "Y" in beside column. I tried with now() formula but it's keep on changing. I need once the activity start time should not be changed when the activity is closed and centering the closing time. I need in if programme

  17. Hi Svetlana, you are obviously very skilled at using Excel. Your page on Excel time calculations came up in a search. I am hoping you can assist me in resolving a couple of issues within an existing Excel sheet i have made for home video logs of old videotapes.

    I have many home videos of my kids growing up and I am entering the date, location, person, place, etc. information of each video into an Excel sheet.

    I tried to copy and paste a sample of the heading of my sheet but the website would not allow me to do so. Let me know if there is a way to send you the sheet. If not here are the column headings:
    DVD # Tape# Source Type Date of Event Time Start Time End Length (HR:MN:SC) Subject Names Event Name Location Notes

    If you already have a sheet for my needs that would be great!

    I would like your expertise on two matters. The first involves time calculations.

    I found where I can format cells for Time/37:30:55 to help me calculate the time durations by entering the data in the Time Start and Time End columns to determine the duration in the Length column (this is calculated across a row. I also entered a formula to calculate the total duration in the Length column (going down) to determine the total length of the entire video tape or the total of all of the events.

    Since the rows could be multiple because of the number of persons and descriptions of one event, I have to enter these formulas manually. Is there a way to put a formula into the Length (duration) column so when I enter the date in Time Start and Time End it will automatically subtract the Start from the End. For instance, one Length could be cell G3 and G5 and may skip to G9. Of course the end point to total the event durations in the Length column for the entire videotape would vary also since I do not know what row will be the last one.

    The second request is hopefully more simple. I want to make sure that I am able to filter the logs if want to find all of the video clips in one video tape say of John or Disneyland or birthday. For instance in a cell naming characters within one event or row as I described above should I put a space or comma or colon between each word I want to search later? Before I start all of this data entry I want to make sure i have it formatted correctly. This will be important if I combine all of the individual video logs into one massive excel sheet to possible search for all of John's birthdays through the years to do a highlight movie.

    Sorry for the lengthy request but I am a novice to Excel and making requests on forums.

    Thank you in advance for your assistance and I look forward to your response.

    Tony

  18. Thank you very much
    Very nice

  19. Hi,

    I have a problem working with the excel formula to calculate the End Date & Time from Start Date & Time for given Duration.

    Its a 24/7 work but there is a chance for festival and natural disaster holidays so will maintain the holiday list separately. We have 2 shifts a day. each shift has below details

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    Example :1 ( Day Shift )
    ----------------------------------
    Start Time Stamp || Project Hours || Daily Shift Start || Break1 Start || Break1 End ||
    1/7/2021 11:45 AM 12 10:00 AM 11:15 AM 11:30 AM

    Lunch Start || Lunch End || Break2 Start || Break2 End || Daily Shift End || Dead Line
    1:00 PM 1:30 PM 3:00 PM 3:15 PM 6:00 PM ????

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    Example :2 (Night Shift)
    ----------------------------------
    Start Time Stamp || Project Hours || Daily Shift Start || Break1 Start || Break1 End ||
    1/7/2021 11:45 AM 28 6:30 PM 8:15 PM 8:30 PM

    Lunch Start || Lunch End || Break2 Start || Break2 End || Daily Shift End || Dead Line
    9:30 PM 10:00 PM 1:30 AM 1:15 AM 2:30 AM ????

    --------------------------------------------------------------------------------------------------------------------------------------------------------

    Conditions
    =======================
    Dead Line with Date & Time
    Excludes the Holidays List
    Calculate only the working hours
    Exclude the Break & Lunch hours

  20. hello dears i need help in my formula to give and get time from above or bellow if current cell have less value from G Cell
    i am using the Formula in E"=IF(F14-G14>0, F14-G14, TEXT(ABS(F14-G14),"-h:mm"))" it's show me how many hours it has less from 8 hours
    but now i want if
    E2 is less then G2 then get the hours/Minutes from above E1 if E1 has +value

    i am sorry i am not able to explain good it's because i don't know english much
    ___________________________________________
    | A B C D E F G
    |1 Sat 05 9:25 20:25 3:00 11:00 8:00
    |2 Sun 06 9:25 16:00 -1:25 6:35 8:00
    |3 Mon 07 9:25 20:25 3:00 11:00 8:00
    |4 Tue 08 9:25 17:00 -0:25 7:35 8:00

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the expected result.
      Explain the phrase in detail "E2 is less then G2 then get the hours/Minutes from above E1 if E1 has +value".
      It’ll help me understand it better and find a solution for you.

  21. One Hour 1000/- 1.30 hours How Many Amount Formals

  22. Hello!
    I am using =INT(K2-J2) to calculate the dates elapsed on for cells showing mm/dd/yyyy hh:mm:ss. In the cell the time is shown in military time, once I click into the cell the bar at the top shows the time in AM/PM. I am saying this because I think I have a data/formatting issue I can't resolve. I am also using =TEXT(K3-J3-INT(K3-J3),"HH:MM:SS"). These work okay although ideally I would like to not have to use two different cells to show the difference in days and times and have them combined in one cell... but I am picking my battles.

    The main issue that I am having is that I can't sum or average (i am really just trying to average here) the hour time lapse using =TEXT(K2-J2-INT(K2-J2),"HH:MM:SS"). I either get a div/0 error message or a 0. I have changed the formatting in these cells to various times, date and customs formats and nothing works.

    Additionally, I have another column with h:mm:ss formatting with values only and no calculation and I am able to average the data no problem. I am at a loss because I have copied the data and pasted in another cell so that no calculations were present in that column but still nothing...Im at a loss. Any help would be appreciated.

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result. It’ll help me understand it better and find a solution for you.

  23. Hello,
    I'm trying to create automatic production schedule. Work hours are from 7:00 to 23:00. I have current date and time. Work that requires 19 hours to complete.
    Need a formula that would pull up current date/time, add 19 hours and show end date/time but in respect to work hours. So if current time is 15:00 it should report end time to be next day at 18:00

    Any help?
    Thanks!

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF((HOUR(A2)+B2)>23,A2+TIME(B2,0,0)+TIME(8,0,0),A2+TIME(B2,0,0))

      A2 - current date/time, B2=19
      I hope my advice will help you solve your task.

  24. I need to get hours worked from a start time and end time. Then from that I need a new cell to add them up over a week and get total hours worked that week. Then I’m a new cell I need total hours for the week and hour pay

  25. I need to add data with how long it takes for an agent to complete a workflow. For example, an agent may take 3 mins and 51 seconds for 1 workflow. When I use the time format in Excel, even when I create custom ones in Excel, I'm having a hard time with it showing as 3:51, where 3 is the mins and 51 is the # of seconds. It always ends up showing as hours and mins or the time of day with AM/PM. How can I just get it to just show as 3:51, 3 mins, and 51 seconds. I need to add a lot of data and calculate the averages.

  26. login- 17:30

    Log out time - 3:00

    how to find the difference between these 2 timings

  27. Hello, I need to calculate the working time from a start time to an ending time and the report I run gives me the info like this. Do you know a formula that will help me ? fOLL
    Start End
    151517 152053
    152053 163714
    163714 165332
    165332 171444
    171444 173023
    173023 175441
    175441 180136
    180136 185950
    185950 193039
    193039 193636
    193636 201025
    201025 205052
    205052 212317
    212317 214440
    214440 220058
    220058 220152
    220152 222429
    222429 234425

  28. Hi!

    I'm looking a formula to find the time difference between 2 dates, but i don't want to use Cell A1-B1.
    Is there a formula which I can input date and time, and substract both in a single cell?

    So the 2 dates are:
    Commenced - 08/09 0330hrs
    Completed - 11/09 1510hrs

    I should get 3D 11H 40M, but i don't want to utilize 3 different cells to get the answer.

    Appreciate your help!

  29. hello. help needed,,, if possible pleasei have a run out time but what i need is
    if time is 3:30 (fixed ) computer clock say 2:45 (live) when clock reaches 3:30
    and though out the day 5:30, 6:14 etc;
    i would like for rows to totally turn red (as in late) and if i tick a box at end of row i would like it to turn green.
    (a2- a10)

    any help please

    1. Hi,
      Your task is not completely clear to me.
      Could you please describe it in more detail? What result do you want to get?
      Describe exactly the conditions that you want to use in conditional formatting. Give an example of the input and the desired result.
      Here is the article that may be helpful to you — Excel conditional formatting for dates & time.

      1. if somebody has to report in for work at i.e. 05:15 and they do not report in I want the excel row to auto highlight (red) when they report in and I tick a box at end of row hightlight to turn green,

        A1 (fixed number 05:15) , H1 now() so when now() passes A1 fixed time colour change (hightlight) also need to work out formula for this and when i tick the I1 it changes colour

        hopefully you can help!

  30. =IF(B2<A2,B2+1,B2)-A2 I AM USING THIS FORMULA TO FIND TOTAL WORKING HOURS OF AN EMPLOYEE.
    IF ONE EMPLOYEE ABSENT MEANS I ENTER ZERO VALUES IN TIME IN AND TIME OUT CELL .BUT ITS NOT WORK.
    MY DOUBT IS IF ONE EMPLOYEE ABSENT MEANS HOW TO AND VALUE ENTER IN TIME IN AND TIE OUT CELL.

    1. Hello!
      Sorry, I do not fully understand the task. If A2 and B2 are 0, the formula returns 0. Please describe your problem in more detail.
      Explain exactly what the problem is. Give an example when the error appears. It’ll help me understand the problem you faced better and help you.

  31. is it possible for the below formula to be working days not just days??

    =IF(INT(M5-I5)>0, INT(M5-I5) & " days, ","") & IF(HOUR(M5-I5)>0, HOUR(M5-I5) & " hours, ","") & IF(MINUTE(M5-I5)>0, MINUTE(M5-I5) & " minutes and ","")

    TIA
    Steph

    1. Hello!
      I believe the following formula will help you solve your task:

      {=SUM(--(WEEKDAY(ROW(INDIRECT(INT($A$1)&":"&INT($B$1))),2) < 6))-2 + B1-A1-INT(B1-A1)+(MOD(B1,INT(B1)) > MOD(A1,INT(A1)))}

      This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

      Use custom date format

      d "d." h "h."m" min."

      I hope I answered your question.

  32. Hello!

    I need a formula which counts the transaction from (Date, 00:00:01 to 23:59:59) 24 hours and the count after 23:59:59 should go to next date.

    1. Hi,
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
      Thank you!

      1. Hello Alexander,

        I am trying to figure out a formula, which will calculate the transaction counts for a day (example: total transaction count for 18-Nov-20 (00:00:01 to 23:59:59) will be 100nos). Also, the counts that are calculated after (18-Nov-20 (00:00:01 to 23:59:59)) should fall to next day (19-Nov-20).

        Is there any formula that you can suggest?

  33. Hello, I need a formula that will help me determine how many calls were happening at 3:00 PM on 11/16. I need to figure out if 11/16/2020 3:00 PM falls between two dates and times.

    For example in row 2 I have join time and leave tine columns with the following data:
    I2: 11/16/20 1:00 PM J2: 11/16/20 1:01 PM
    I3: 11/16/20 13:40 J3: 11/16/20 14:30

    I have about 200 rows with date and times to go through so I need to see if the 11/16/20 3:00 PM date and time falls in between the dates above as well as the other rows. I need an output for each row (whether its true or false).

    After I get this data I can use a count formula to see how many times I get yes which will determine how many calls were happening at 3:00 PM on 3/16.

    My data expands over 48 hours.

    1. Hello!
      The formula below will do the trick for you:

      =AND(M1 > I1,M1 < J1)

      After that you can copy this formula down along the column. I hope my advice will help you solve your task.

      1. Thank you for the information. Do I need to format the dates and time a certain way or format them as something? It's just returning no for everything when I most definitely have some Yes ones.

    2. I should have mentioned the date AND time are both in one column, it is not separated. I need to keep the date and time together.

  34. How do i subtract from (29 minute:17 second:13 millisecond - 28 minute:15 second :90 millisecond) in excel

    1. Hello!
      Write in cells

      00:29:17.013
      00:28:15.090

      Use custom format h:mm:ss.000
      Subtract the second from the first value.

  35. Good afternoon, I have some days where I clock in at 5:00 am out at 11:00 back in 30 minutes later and then out at 1:30pm. But then there are some days in which I will be in and out 3-4 days in a day. example would be Monday 5:00 - 11:00, back in at 11:30 and work till 1:30. But today because I have to go into the office it would be 5:00 - 8:30, 9:15 - 10:30, 12:00 - 2:00, 4:00 - 6:00 pm. I have tried using: =IF(OR(B2="",I2=""),"",(C2-B2)+(E2-D2)+(G2-F2)+(I2-H2)) but the time will not round and I'm still coming up short. Whatever suggestion you have it would be TRULY APPRECIATED because I hate to have to make up time when I'm short 1 day and over the next.
    Sincerely,
    Frustrated!!!

    1. Hi Alexander, I need a formula that tracks multiple times that I would clock in and out of work. Clock in the morning, then clock out for break, back in from break, then out for lunch, clock back in from lunch, then out for break, then return from break, then out for a second afternoon break then clock back in from that break until I clock out for the day.

      example:
      clock in at 5:00 AM, out at 7:00 for 30 minutes then i clock back in at 7:30, work from 7:30 until 11:00 then it's lunch from 11:00 am until 11:30 then i clock back in, then at 1:00 PM I clock out for 30 minutes and return at 1:30 until the end of my shift.

      so any formula you could share would be greatly appreciated.

      1. Hello!
        I don't know how the data is written in your table. If each arrival at work and breaks in work are written in one line, then the formula may be as follows:

        =C2-B2+E2-D2+G2-F2+I2-H2+K2-J2+M2-L2+O2-N2+Q2-P2

        If there is anything else I can help you with, please let me know.

        1. Alexander the formula works great. One last tweek if you don't mind. Our systems rounds up when keyed. I do truly appreciate your assistance, maybe i can stay out of trouble now. :)

          1. Hi,
            Please explain in more detail which results are being rounded. What exactly is the problem? Explain in more detail.

            1. the formula for time adds up across the cell and was wondering if there was a way to get the ending balance to round up? say i worked for 8 hours and 37 minutes, how to get this 37 minutes to round up to 45 minutes?

    2. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  36. 1st condition = should be after 8:30 AM
    2nd condition = should be before 4 PM
    Cannot find the right date. Struggle with AM/PM thing (confuse)
    for eg.

    =if(and(10:20 AM>=8:30 AM,10:20 AM<=4:30 PM),"within","out")

    note: 10:20 here is random time.
    Could you help whats wrong with formula and how can I get the right condition?

    1. Hello!
      If I understand your task correctly, you need to use TIME function to compare time

      =IF(AND(A10 >= TIME(8,30,0),A10 <= TIME(16,30,0)),"within","out")

      Hope this is what you need.

  37. hello, how to calculate times between 9AM-9PM and 9PM-9AM if I have '14-Jan-20 05:00' (F2) as a reported date and '15-Jan-20 13:00' (G2) as a resolved date? Thank you in advance :)

    1. and the output that I expected is 16:00 (means 16 hours) for 9AM-9PM and also 16:00 for 9PM-9AM :)

      1. Hello!
        I wrote this formula based on the description you provided in your original comment. My result is 12 hours. Explain your calculations. Does 9 PM-9AM mean 21:00 - 09:00? Are you subtracting time or date and time? It is impossible to understand from your question.

  38. John,
    Wouldn't that cause a circular reference?
    C9: 8:30
    B9: 19:30 (which baffles me to put the End time in B and Stat time in C...?)
    Then, in D9, we subtract those two AND subtract D9 itself from that?... ???

    1. Hello!
      I didn't quite understand your problem. But it might be useful.
      If start time is greater than end time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

      I hope this will help

  39. what's the added formula if 1 hour must be deducted if you accumulated 6 hrs of overtime everyday?

    1. Hello!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  40. I am making a spreadsheet to fully calculate how much pay I'm getting the only variable I'm stuck on is that I get paid %15 extra/hour after 6pm on weekdays. Is there a formula that checks for a time after 6pm and can return a value?

    Thank you if anyone can help.

    1. Hello!
      Without seeing your data it is difficult to give you any advice. If time is written in cell A1, then you can compare it using something like this formula:

      =IF(A1>TIME(18,0,0),B1,0)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  41. I need to figure out how can i get event time/run time of every account having multiple transactions, i need time duration between First till last transaction of every account, every account having multiple transactions count.

    2. All data is in one sheet, first column having account number, then amount then date time then type

  42. Hi there!

    I'm trying to track improvement in running times (e.g. how much faster someone is running 5km).
    So I was wondering if it is possible to subtract one running time (35 minutes 10 seconds) from a better running time (25 minutes 30 seconds) to see how much faster someone is running (-9 minutes 45 seconds).

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Pay attention to the following paragraph of the article above: Formula 3. Count hours, minutes or seconds between two times.

  43. Hey I have a timesheet that I would like to calculate the specific hours given is counted as overtime 1 which is from 22:00 PM - 06:00 AM, time after 06:00 AM will be calculated as overtime 2. I wanna get the hours worked in oveRtime 1 is how many hours? is that possible.
    For example:
    TIME IN (A1) = 12:00AM
    TIME OUT (B1) = 11:00 AM
    hours worked in overtime 1 is 8 hours, and in overtime 2 is 5 hours.

  44. hey so i have a timesheet for work i need it to calculate total hours, overtime 1 and overtime 2
    where hours from 07:00-15:00 is normal hours so no overtime counted
    hours from 15:00-17:00 is counted in overtime 1
    and all other hours is counted in overtime 2
    like so
    if A1(start time)=07:00 B1(end time)=19:00 C1(total hours)=12 D1(overtime1)=2 E1(overtime2)=2
    or A1(start time)=04:00 B1(end time)=16:00 C1(total hours)=12 D1(overtime1)=1 E1(overtime2)=3

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =B1-A1

      =IF(AND(B1>TIME(15,0,0),B1<TIME(17,0,0)),B1-TIME(15,0,0),TIME(2,0,0))

      =IF(B1>TIME(17,0,0),B1-TIME(17,0,0),0)

      I hope it’ll be helpful.

  45. I need to calculate sub of two times Eg: 06:00-09:00 in 24HRS
    Ans should be in 24HRS Format
    I want to get 21:00
    Can you help Me?

  46. Hello.
    I'm looking for a formula. I made one that gives the total worked hours. But my problem is, let's say the office time starts at 8am, the formula includes the time over or under 8am which the minutes for late and early in is computed.

    1. Hello!
      If start time is greater than end time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

      I hope this will help

      1. Thank you for help works well

  47. I want to deduct time with a frame of more than 15 mins. For eg

    13.00pm
    13.01pm
    13.45pm

    Now it should skip the middle and select the next one. This type of format can be with more than 3 numbers

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?

  48. Hy. The formula works. I have the same schedule format. But how do I sum up with this formula for multiple days to get the final time number?

  49. I have 12:10 formatted as [hh]:mm in cell A1. How can I separate the hours and minutes so that the hours appear in A2 and the minutes appear in A3?

  50. How to get diff in days,hours,minutes,seconds,milli seconds for below
    Min time stamp: 2019-06-14 09:16:13.013
    Max time stamp: 2019-09-14 09:16:12.310

    1. Hello!
      Find the difference between these dates using normal subtraction. To determine the difference in time in days, hours, minutes and seconds, apply the formula to the cell with the result:

      =INT(A1)&" days "&INT(MOD(A1,INT(A1))*24)&" hours and "&MINUTE(A1)&" minutes, "&SECOND(A1)&" seconds"

      I hope my advice will help you solve your task.

      1. I need for milliseconds as well. Above works until seconds only.

        1. Hi Alexander Trifuntov (Ablebits.com Team),

          =INT(A1)&" days "&INT(MOD(A1,INT(A1))*24)&" hours and "&MINUTE(A1)&" minutes, "&SECOND(A1)&" seconds"

          Can you also add calculation for milliseconds as well in above?

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