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

  1. Hello Alex, thankyou for your post, can you help me with my problems? I really hope you can assist me

    I want to know how to subtract the video time duration from the video remaining time duration

    for example, I want to convert 01:10/01:38:30 (video duration time) to the 01:37:20/01:38:30 (video remaining time)\

    all I want to do is just substracting 01:38:30 to 01:10 but the excel format keeps converting my time duration to AM and PM format

    I really hope you can answer me, thank you Alex

  2. Hello, can you help me with my problem?
    I have database where is a lot of cells with contains time in the format for example 1h 30m
    Is it possible to automatically convert that format to something like that: 1:30 ?
    I will be very thankful for your help

  3. can you help to calculate time between 2 dates but you remove non working hours of 18H00 to 07h00

    Thank you

    1. Hi!
      Perhaps this answer will be useful to you. If this is not what you wanted, please describe the problem in more detail. Give an example of the source data and the expected result.

  4. I have data where the initial clock was set wrong e.g., 1/9/1930 11:00 but should have been 9/14/2021 1:15. I can get the date corrected using =DATE(YEAR(B3)+91, MONTH(B3)+8,DAY(B3)+5) and time corrected using =B3+TIME(2,15,0). Is there a way to correct them both using one formula and keeping the original format? When I tried =B3+(47829600/1440) it returns only the date and no time. Thanks

    1. Hello!
      Time is a fraction of a number. You can get it using the INT function.

      =DATE(YEAR(B3)+91, MONTH(B3)+8, DAY(B3)+5)+B3-INT(B3)+TIME(2,15,0)

      Hope this is what you need.

  5. Hi! Thank you so much for this instruction! I didint found one more thing i was looking for.
    So,
    Im working between 7.00am - 3:30pm monday - friday. I have these "saldo hours". You can collect 1 saldo hour before 7.00am and 3 saldo hours after 3.30pm.

    Example

    Working between 6:45am - 3:35pm. Get paid normal 8h (30min not paid lunch break) and collect 20minutes of saldo.

    Now my saldo is +0:20h

    Next day working 6:00am - 3:15pm Get paid normal 8h (30min not paid lunch break) and collect 45minutes of saldo. (coming 60min before and leaving 15min early - total 45min)

    Now my saldo is +1:05h

    So how to create this kind of function or formula, which can trakt the saldo hours?

    Thank you so much!!

    1. Hi!
      Determine the time using the TIME function. Use the IFERROR function to avoid errors in the calculation result.

      =IFERROR(TIME(7,0,0)-A1,0)+IFERROR(B1-TIME(15,30,0),0)

      I hope my advice will help you solve your task.

      1. Hi!

        Thank you for fast answer!

        That is not working.

        I use one column for time i go to work, next column for time i leave. Third column calculate the total time with this formula =(C3-B3)-TIME(0;30;0).

        But how i can create the forumula calculate from that time what is normal work hours (8h) and after that rest goes to saldo.

        Thank you!

        1. Hi!
          The formula I sent to you was created based on the description you provided in your first request.
          The formula calculates the saldo you wanted to calculate. Explain what exactly doesn't work.

          1. Hi!

            Now its working, i had some typo with , and ;.

            Thank you so much!

            I have one another problem, what i cant solve, in another thing.

            So i use this device to measure things and i use excel to collect data. Somehow this device sending data in "wrong" format. So if the decive show negative value excel turn it positive with + and if value is positive excel shows also positive value without +. And i need to keep number format in "text" otherwise there is problem with formula.

            Example:

            Device value is -0,12 -> excel shows +0,12
            Device value is 0,13 -> excel shows 0,13

            Is there anything i can do in excel? From device side there is nothing i can do.

              1. Hi!

                Now everything is working right! Thank you very much!

            2. One more thing.. sorry to replay so many times..

              What if the saldo is negative? that formula is not working for that. Sometimes i do short day and use saldos.

              example

              working between 06:00 - 14:00, so saldo go negative 0:30h.

              thank you!

              1. Hi!
                The formula I sent to you was created based on the description you provided in your first request.
                If you had specified all the terms at once, we would have saved a lot of time.

                =IF((((TIME(7,0,0)/24-A1/24)+(B1/24-TIME(15,30,0)/24))*24)<0, "-"&TEXT(-((TIME(7,0,0)/24-A1/24)+(B1/24-TIME(15,30,0)/24))*24,"h:mm"), ((TIME(7,0,0)/24-A1/24)+(B1/24-TIME(15,30,0)/24))*24 )

                Use time format in this cell.
                Negative time is written as text.

  6. Hello,

    Thanks for the tips. Unless I missed it in the article, I'm not seeing the solution I'm looking for. I'm planning courier routes and would like to display the transfer time for each stop. For the first column, I have the arrival time (e.g., 8:00AM). For the second, column, I have the transfer time listed (e.g., 8:00AM - 8:15AM). Is there a function I can use to display the transfer with the only variable being transfer time (e.g., 15 minutes, 30 minutes, etc.?) or would this require additional columns?

    Thanks!

    1. Hello!
      If I understand you correctly, you want to add time to the arrival time (15 minutes)
      Please try the following formula:

      =A1+TIME(0,15,0)

      If this is not what you wanted, please describe the problem in more detail.

      1. Hello,

        This is close, but I'm also hoping there is a way to display both the original time and the time plus variable in the same cell so that if the first column reads 8:00AM, the second column would read 8:00AM - 8:15AM.

        Thanks so much

  7. how to sum numbers and time in one column , for example ( 4151485 1:00 PM 10:00 PM) ?

  8. 1-Mar 1:00 PM to 5-Mar 3:00 PM = ???
    Please Formula..

  9. Hi

    How do I calculate with formula the number of hours , e.g. 0800-1700 ?

  10. hi!, I've managed to get the formula to successfully work with only 1 time in and 1 time out. how do i add more times to the formula. for example i want to calculate how long i was working for. i signed it at 8, signed out at 12:30 for lunch. signed back in at 1:30 and signed out at 5:15 at the end of the day. how do i make this a formula that calculates the number of hours and number of remaining minutes separately.

  11. How do I update this formula from using an 8 hour shift to a 10 hour shift? ,TIME(E6+8-INT(E11),F6+30-(60*(E11-INT(E11))),0),TIME(E6+8-INT(E11),F6-(60*(E11-INT(E11))),0)))))

    1. Hi!
      I cannot guess what your formula is doing. But you can try changing the number 8 to 10. If that doesn't help, give more information about your problem.

  12. How to subtract dd-mm-yy hh:mm am/pm valeues in excel (12hours format)
    For example
    2/17/22 12:00pm -2/17/22 2:00pm = 2 hours

    How to get this in excel

  13. My question is stock movement date is
    2022-02-14 16.51.12
    Gate entry date is
    2022-02-14 16.48.27

    Please help me how to calculated this hours, min, second

  14. Hi!
    If the value "Mon - THU (Morning)" is recorded in one cell, then it is impossible to search through such data.

  15. Anyone have formula to determine rate by weekday and weekend?

    Mon - THU (Morning) - $10
    Mon - THU (Night) - $11

    FRI - SUN (Morning) - $12
    FRI - SUN (Night) - $13

  16. How to subtract 30 min if the shift is for 8hr 30 min .

  17. How to determine a work shift, 06:30 to 18:59 - Morning, 19:00 to 06:29 - Night.

    =IF([@[Clock In Time]]>=TIME(6,30,0),IF([@[Clock In Time]]<=TIME(18,59,0),"Morning","Night"))

    Had try this formula but when key the clock in time between 01:00 - 06:29, it will come out "FALSE"

    please help....

    1. Hello!
      Please try the following formula:

      =IF(AND([@[Clock In Time]]>=TIME(6,30,0),[@[Clock In Time]]<=TIME(18,59,0)),"Morning","Night"))

      I can't check the formula that contains unique references to your workbook worksheets, sorry.

      1. it doesn't work :(

          1. Hello sir ,
            I have one task to solve . In our company there is 3 working shifts are there,
            1. 06:30 to 15:30 shift A
            2. 15:30 to 23:30 shift B
            3. 00:30 to 06:30 shift C

            and i want to add one column which give me only shift name when the employee place there entry in any time
            for example if
            if employee enter after 06:30 but before 15:30 then formula should give value "A"
            if employee enter after 15:30 but before 23:30 then formula should give value "B"
            if employee enter after 00:30 but before 06:30 then formula should give value "C"

            Please guide sir , what will be the best formula ?

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

              =CHOOSE(IFERROR(MATCH(1,($A$1:$A$3D1),0),3),"A","B","C")

              Column A - the beginning of the shift, Column B - the end of the shift. D1 is the time entered by the employee. Determine the desired interval number using the MATCH function. The CHOOSE function will replace the interval number with the desired letter.

  18. In excel, How to subtract 4:58:00 Hrs from 02/07/2022 04:27:00 ?

    1. Hello!
      If your value is written in date and time format, then you can extract the time in the way described in this example

      =A1-INT(A1)

      Set time format in this cell.
      But you can't get the time 4:58:00 from your value. Only 04:27:00.

  19. Per hour 800 rupees then what is the cost for 4 Hrs 20Min

  20. Hello!!! Can I ask what formula should I use if the driver is arriving to job and he is paid for waiting time 30.08€ per after 45 minutes. I need first to find how many minutes and then how much they will get paid. Thank you very much indeed.

  21. How to convert 2 days 19:30:18.530787 into total hours and minutes.

    1. Hello!
      To convert text to date and time, use the formula

      =LEFT(A2,SEARCH(" ",A2)-1)+TIMEVALUE(MID(A2,SEARCH(" ",A2,6)+1,8))

      Then set a custom time format [h]:mm to show times over 24 hours as described in this tutorial.

  22. Hi I'm trying to calculate -minus break times, without the break time being displayed. So for example
    if total hours is greater than 4:30-0:15=04:15
    if total hours is greater than 6:00-0:20=05:40
    If total hours is greater than 8:00-0:30=07:30
    But all in the same Formula if that makes sense. Is this possible?

    1. Hello!
      If I understand your task correctly, use formula with nested IF function:

      =IF(A2 > =TIME(8,0,0),A2-TIME(0,30,0),IF(A2 > =TIME(6,0,0),A2-TIME(0,20,0),IF(A2 > =TIME(4,30,0),A2-TIME(0,15,0),A2)))

  23. I'm calculating time difference with dates using the formula:
    =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) - TIME(HOUR(B2), MINUTE(B2), SECOND(B2))

    The issue occurs when attempting to calculate the time elapsed in H:MM:SS for when it rolls into the next day, ie 12/01/2021 19:37 to 12/02/2021 03:40 .

    Is there a better formula to use when for the example above or a way to convert the data of the 12/02/2021 03:40 and use the same formula?
    Thank you

  24. How to convert 13451 into Hours and minutes?

  25. Hi, I am looking for what I believe is a simple solution, but, I think that I am just not "asking" google the right question LOL
    Anyways, I have my time total figured out.
    3:20 pm - 3:20 am - 12hours.
    Now, I would like to take that 12 hours (say it is in cell u7) and have it show 8 hours in d7 (regular time), anything over 8 hours (up to 3 hours) to show in e7 (overtime). Then, anything over that 3 hours, to show in i7 (double time).
    Thank you.

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

      =IF(U7>TIME(8,0,0),TIME(8,0,0),U7)

      =IF(U7>D7,IF(U7-D7>TIME(3,0,0),TIME(3,0,0),U7-D7),"")

      Hope this is what you need.

  26. I want remove 01/01/1900 12:52:52 AM
    Please guide

  27. Hi there! Thank you so much for this post!

    I have a question. I've set up a sheet with the formula shown here =TEXT(D2-C2, "h:mm") where I calculate the amount of minutes/hours worked on specific tasks. I then wanted to add all of these together, but not sure how to go about it, as =SUM() of all of those =TEXT() doesn't work at all...

    Could you point me in the right direction please?

    Thank you!

    1. Hi!
      Subtract the original time data with something like this:

      =SUM(D2:D9-C2:C9)

      I hope it’ll be helpful.

      1. It really works. Thank you very much

  28. Hi,

    How do you create a formula to add a time from a specific hour onwards. For example start time should be 7PM. data is 6:21PM, formula should start counting at 7PM onwards.

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice, sorry. How much time do you want to add and what result do you want to get?

  29. I am trying to use a simple formula that will auto sum total hours worked. Everything works fine as long as the cells that are being added include an increment of at least one hour. However, all of the fields that have an amount between :01 and :59 are being 'skipped over' in the formula. Please see my example below:

    Day 1 Day 2 Day 3 Day 4 Day 5 Total Hours Worked
    1:00 1:00 1:00 1:00 :05 4:00

    As you can see, Day 5 was not included in the sum of the cell 'Total Hours Worked', and I can't see to figure out how to manipulate excel to include it.

  30. Hi Team,

    My start time is - 12:12:00 AM 23-Nov-21
    My End time is - 10:46:00 PM 22-Nov-21

    Query : how do i subtract this ( Start time - end time)?

    1. Hi!
      Please re-read the article above. It is impossible to find the difference. The start time must be less than the End time.

  31. Hi , I need to substract the time log in time, log out time, lunch hours & if their is any kind other break downs for the machine. so i need total working hours.

  32. Hello,

    I am not sure what formula I have to use to create 3 cells. One which shows date and time now. 2nd is where I would enter employees time worked (for example 32 hours). 3rd cell would have to show the next date and time period the person goes on call.

    Example: today is 10/8/2021 13:00. Employee has called and I see they worked 29 hours and 35 mins. I would need the 3rd cell to tell me the date and time that's going to be when I add 29:35.

    Thanks in advance!

    1. Hi!
      In the third cell, just find the sum of the first two. Read about how to add time in different ways in the article above.

  33. How do I get excel to choose from four cells, the one with the time in it, and then find the difference between it and a finish time

    1. Hi. I am preparing attendance sheet in excel but daily basis I am adding labor intime (as one day present) and want total attendance days in the total of each employee.
      Please guide how to do so?

    2. Hi!
      Time in Excel is written as a number. If you tell me what is written in the other three cells and how these three cells differ from the fourth, I will try to help you. Give an example.

  34. I am wondering how to calculate the time of 23:09:54 of 7/9/2021 to 0:32:32 of 8/9/2021. How do I calculate the time difference/ time worked? What do I write in the excel sheet ? Do i need to add the dates ? Does it only work for 12- hour clock? What formula should I use?

    1. Hi!
      If your data is recorded as date and time, use subtraction. If the time is recorded separately and if the end time is longer than the start time, then you need to add 1 day to their difference

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

  35. Hi, i have 10 employees who rotate day and night shift, i want to setup a formula that i dont have to manually calculate their times clocked in all the different areas - example:

    EmployeeA clocks in on night shift at 18h00 at the main gate then clocks at 19h30 in at the offices, again clocks in at Lamproom at 20h00 he clocks out at Lamproom at 23h00 at office 24h45 and out at main gate at 02h00am (next morning). i need to calculate what his total shift was ie. 8hrs, shift underground (lamproom in and out), the differences between main gate and office 1h30 and office to lamproom - is there a way i can do this on excel? i tried Pivot but the pivot doesnt calculate the time worked it only sums/counts

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

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

      Calculate the amount of time differences.

      =IF(A1 > A2,A2-A1+1,A2-A1)+IF(A3 > A4,A4-A3+1,A4-A3)+IF(A5 > A6,A6-A5+1,A6-A5)

  36. Hi thank you for sharing this, I have a problem with my formula with hours that formulated using =MOD and the formate number is [h:mm]. Now the problem was whenever I multiplied the hours into $ pay rate, it is showing a wrong result. :(

  37. Hi,

    1)I have applied a mid function as =MID(H2,11,8) which is 08:11:08 and also add 4 hrs which is TIME(4,0,0).
    But when I add the time the result becomes 12:12PM Because I am applying on the above formula.

    How I ll get the result as 12:12:18 without showing AM/PM?

    2) how can I convert 08.11.08 to 08:11:08?

    Please help.

      1. Ok, I ll try with text.

        What will be the formula for 2nd one? Please help

  38. Is there a way to make the end result a decimal number? Here is what I am trying to do:

    Time In Time Out Total Hours
    8:30 AM 4:00 PM 7.5

    When I follow the formula in the example above I just get 7 for the total hours.

    Thank you.

      1. Formula 2. Calculating time difference with the TEXT function

  39. I want to calculate the median and mean. Say I have column of 500k rows, which have the time difference between 2 timestamps written as "0 days, 0 hours, 27 minutes and 53 seconds". How do I calculate the median and mean of these?

  40. Is there a way to calculate the differences in time values between two columns easily?

    As an example if I am trying to calculate quickly the difference in time values between:
    column A : 16hrs30min (16.30) value
    column B: 16hours40min (16.40) value

    Column C (shows difference): -0.10min

    I cannot seem to find a formula that will do this. I need to show a positive difference or a negative difference (e.g. -0.10, +0.10, +1.30,etc)

    Do you know a formula I can use?

    Thank you

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

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

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

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

    how we can get working hours in this condition

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

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

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

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

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

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

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