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

  1. how can i calculate Total down time hours

  2. DATE Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Issue Start Time Issue End Time Total Network Down Time
    01 September 2017 12:17:00 12:47:00 13:17:00 13:47:00 14:17:00 14:47:00 15:17:00 15:47:00 16:17:00 16:47:00

  3. Hi,

    Calculate the time duration in different days 9/09/17 18:00 & 09/11/17 11:40.wht is the total time duration b/w the days

  4. how can i get the difference in years,month,days,hours,minute,seconds
    for example-
    (20-09-1993 19:00:00)- (27-08-2017 14:00:15)

  5. =IF($C7="","",(MOD(D7-C7,1))*24)+IF($E7="","",(MOD(F7-E7,1))*24)+IF($G7="","",(MOD(H7-G7,1))*24)+IF($I7="","",(MOD(J7-I7,1))*24)+IF($K7="","",(MOD(L7-K7,1))*24)+IF($M7="","",(MOD(N7-M7,1))*24)+IF($O7="","",(MOD(P7-O7,1))*24)

    I know that I am missing something in this formula. If anyone can spot it let me know. This is in/out times, same day, several jobs, i.e., 1 hr at 1 job, 6 at next job, total for the day is xxx hours.

  6. Need help with how to calculate duration between date with time, less 30 minutes for example:
    A1 08/22/17 8:00 am - B1 08/20/17 8:30 am = 2 days

    Thanks
    Maggie

    1. Hello, Maggie,

      Please try out the following formula: =ROUNDUP((A1-B1)*24,0)/24 & " days"

      Hope this will help.

      1. Hi Ekaterina,

        Thanks for the help but it's not calculated proper. Maybe, I did not explain well, for example:

        A1 5/2/16 17:32 - B1 5/3/16 19:59 should return 2 days not 1 day

        Regards,
        Maggie

  7. I am trying to sum some values which are in word forms (0 Days, 1 Hours, 10 Minutes + 0 Days, 17 Hours, 54 Minutes + 0 Days, 0 Hours, 12 Minutes). Can you help me to sum these values?

    1. Hello, Komal,

      you need to create a custom time format that will allow summing the values when they're written as you need. You can read how to create such a format in our article.
      Hope it helps.

  8. Hello,

    Can you help me create a formula to calculate time between these two?

    2017-08-16 18:24:00 2017-08-17 06:40:00

  9. How is it with 24 hour working time? For example:start 23:00 finish 01:30 should equal 150 minutes.

  10. Thanks a lot. its very helpful.

  11. This is the formula that I have in my cells

    =TEXT(C11-B11,"h")+TEXT(E11-D11,"h")+TEXT(G11-F11,"h")+TEXT(I11-H11,"h")+TEXT(K11-J11,"h")

    I'm trying to calculate start time and end times for my employees. The problem I'm having is that formula ignores the minutes and only shows the hours. How do I show both hour and minute?

  12. Tq very helpful

  13. we required first in end last out time.
    I have data this format,
    EMP_ID POOL_DATE SHIFT POOL_TIME IN_OUT_REMARKS
    0001 20170301 N 1949 In Time
    0001 20170301 N 808 Out Time
    0001 20170302 N 1946 In Time
    0001 20170302 N 813 Out Time
    0001 20170303 N 1938 In Time
    0001 20170303 N 810 Out Time
    0001 20170304 N 1940 In Time
    0001 20170304 N 811 Out Time
    0001 20170305 D 809 In Time
    0001 20170305 N 804 Out Time
    0001 20170305 N 1612 Out Time
    0001 20170306 N 1951 In Time
    0001 20170307 N 1945 In Time
    0001 20170307 N 810 Out Time
    0001 20170308 N 1953 In Time
    0001 20170308 N 819 Out Time
    0001 20170309 N 1944 In Time
    0001 20170309 N 812 Out Time
    0001 20170310 N 1946 In Time
    0001 20170310 N 807 Out Time
    0001 20170311 N 2002 In Time
    0001 20170311 N 808 Out Time
    0001 20170312 D 809 In Time
    0001 20170312 N 805 Out Time
    0001 20170313 N 1955 In Time
    0001 20170313 N 803 Out Time
    0001 20170314 N 1954 In Time
    0001 20170314 N 820 Out Time
    0001 20170315 N 1954 In Time
    0001 20170315 N 837 Out Time
    0001 20170316 N 1944 In Time
    0001 20170316 N 815 Out Time
    0001 20170317 N 1950 In Time
    0001 20170317 N 809 Out Time
    0001 20170318 N 1943 In Time
    0001 20170318 N 805 Out Time
    0001 20170319 N 809 Out Time
    0001 20170320 N 1946 In Time
    0001 20170321 N 1945 In Time
    0001 20170321 N 810 Out Time
    0001 20170322 N 1941 In Time
    0001 20170322 N 832 Out Time
    0001 20170323 N 807 Out Time
    0001 20170324 D 749 In Time
    0001 20170325 N 1950 In Time
    0001 20170325 N 805 Out Time
    0001 20170326 N 807 Out Time
    0001 20170327 N 1951 In Time
    0001 20170328 N 803 Out Time
    0001 20170329 N 1946 In Time
    0001 20170330 N 1939 In Time
    0001 20170330 N 810 Out Time
    0001 20170331 N 1948 In Time
    0001 20170331 N 812 Out Time

    And we required format,

    Requriedment report
    Code SHIFT IN Date In Time Out Time OUT Date
    0001 N 20170301 1949 813 20170302
    0001 N 20170302 1946 810 20170303

    And Shift Name and range
    SHIFT NAME IN TIME OUT TIME MIN IN MAX OUT
    N 20:00 5:00 19:00 8:30
    D 8:00 17:00 7:00 7:00

    I am waiting your kind response. Your advice is very much appreciated.

  14. hi,

    I am rizwan and we required first in end last out time.
    I have data this format,
    EMP_ID POOL_DATE SHIFT POOL_TIME IN_OUT_REMARKS
    0001 20170301 N 1949 In Time
    0001 20170301 N 808 Out Time
    0001 20170302 N 1946 In Time
    0001 20170302 N 813 Out Time
    0001 20170303 N 1938 In Time
    0001 20170303 N 810 Out Time
    0001 20170304 N 1940 In Time
    0001 20170304 N 811 Out Time
    0001 20170305 D 809 In Time
    0001 20170305 N 804 Out Time
    0001 20170305 N 1612 Out Time
    0001 20170306 N 1951 In Time
    0001 20170307 N 1945 In Time
    0001 20170307 N 810 Out Time
    0001 20170308 N 1953 In Time
    0001 20170308 N 819 Out Time
    0001 20170309 N 1944 In Time
    0001 20170309 N 812 Out Time
    0001 20170310 N 1946 In Time
    0001 20170310 N 807 Out Time
    0001 20170311 N 2002 In Time
    0001 20170311 N 808 Out Time
    0001 20170312 D 809 In Time
    0001 20170312 N 805 Out Time
    0001 20170313 N 1955 In Time
    0001 20170313 N 803 Out Time
    0001 20170314 N 1954 In Time
    0001 20170314 N 820 Out Time
    0001 20170315 N 1954 In Time
    0001 20170315 N 837 Out Time
    0001 20170316 N 1944 In Time
    0001 20170316 N 815 Out Time
    0001 20170317 N 1950 In Time
    0001 20170317 N 809 Out Time
    0001 20170318 N 1943 In Time
    0001 20170318 N 805 Out Time
    0001 20170319 N 809 Out Time
    0001 20170320 N 1946 In Time
    0001 20170321 N 1945 In Time
    0001 20170321 N 810 Out Time
    0001 20170322 N 1941 In Time
    0001 20170322 N 832 Out Time
    0001 20170323 N 807 Out Time
    0001 20170324 D 749 In Time
    0001 20170325 N 1950 In Time
    0001 20170325 N 805 Out Time
    0001 20170326 N 807 Out Time
    0001 20170327 N 1951 In Time
    0001 20170328 N 803 Out Time
    0001 20170329 N 1946 In Time
    0001 20170330 N 1939 In Time
    0001 20170330 N 810 Out Time
    0001 20170331 N 1948 In Time
    0001 20170331 N 812 Out Time

    And we required format,

    Requriedment report
    Code SHIFT IN Date In Time Out Time OUT Date
    0001 N 20170301 1949 813 20170302
    0001 N 20170302 1946 810 20170303

    And Shift Name and range
    SHIFT NAME IN TIME OUT TIME MIN IN MAX OUT
    N 20:00 5:00 19:00 8:30
    D 8:00 17:00 7:00 7:00

    I am waiting your kind response. Your advice is very much appreciated.

  15. Hi,

    I want to different between 18:00 to 03:30, If the time above or lower than 09:30 Hr then it show me.

    For example, My time is 19:10 to 04:30 that means -10 min is difference. and When my time is 18:50 to 4:30 that means +10 min is difference.

    How can i calculate it.

  16. Hi,

    I want to different between 19:00 to 04:30, If the time above or lower than 09:30 Hr then it show me.

    For example, My time is 19:10 to 04:30 that means -10 min is difference. and When my time is 18:50 to 4:30 that means +10 min is difference.

    How can i calculate it.

  17. Need formula to calculate average time of 4 months. which should be calculate per day as 8hr

  18. Hello,

    I'm trying to find a way to calculate the time spent working out problems for testing purposes on excel. I find that if I use the NOW() function on the cells I need to display the current time, both cells change at the same time resulting in the exact hours and minutes. I need to find a way to keep the start time intact and then the NOW () function to display when a task was completed. That way the end result gives me an accurate number of the time a prospect employee used to complete the excel test. Is there another function or formula that gives the exact time without entering this manually perhaps by entering a value prior to starting the project? And then entering a value again when completing everything so it automatically calculates the time spent?

  19. Hi,

    How can I change the timestamp results below:

    11:20:32
    11:32:36
    11:41:28
    11:53:36
    12:02:48

    To this result:

    0
    12
    21
    33
    .
    .
    .

    Thanks

  20. Help! I do not know what is wrong with my formulas.
    I am doing a time sheet,
    all cells are in as time 00:00;
    time in & time out= total (=sum(B1-A1))
    That all works, but when I want to total the week, (=sum(C1:C7))
    the total does not add up, if I enter one day at a time, the first 2 rows add correctly but the third row is subtracting the total from itself. the same happens for the next 3 rows. The end result is 8 hours which is what is entered on row 7.
    Thank you for any help you can provide

    1. Hi There,

      in total cell change format Ctrl 1 go to custom and select (hh):mm.

  21. Hello Ma'am

    I want answer this 2 questions.

    Question-Get work hours between dates custom schedule using the following formula
    options
    a) {=SUM(CHOOSE(WEKDAY(ROW(INDIRECT(date1&":"&date2)),1,2,3,4,5,6,7))}
    b) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1":"date2))),1,2,3,4,5,6,7))}
    c) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&":"&date2))),1,2,3,4,5,6,7))}
    d) {=SUM(CHOOSE(WEEKDAY(ROW(INDIRECT(date1&";"&date2)),1,2,3,4,5,6,7))}

    Quesiton-Which of the following can be accomplished by Creating Highlighted Cell Rules?
    Note: There may be more than one right answer.
    Options
    a)Which cell values are less than 0?
    b) What are the top 10 values?
    c) Which cell values are above average, and which are below average?
    d) Which cells values are duplicate?
    e) Which cell values are less than 010?

    Thanks

  22. how to calculate total hours when date and time written in separate columns like.

    start time . start date . end time . end date ----- total hours

  23. How do you create an invoicing formula that multiplies an hourly pay rate (£) with total hours, mins?

  24. what formula should i use to get time difference in quarters like 7:00AM - 8:15AM = 1.25 ? IF 15min equal to .25 and 30 mint to .50 ?

    1. If 7:00am is in cell A1 & 8:15am in cell B1 formula should be =sum(b1-a1)*24 hope this helps (don't forget to format the results cells as "number")

  25. Hello everyone, i m working as a MIS Analyst, pls suggets me to time Calculation like shift started in the evening at 6.30 PM and Leaving time is morning 4.00AM, pls suggets with Formula for Time Calculation.

    its very urgent...

  26. how to put an If function in the following scenario:

    time-in schedule no. of mins late
    7:10 AM 7:00 AM 10 mins
    6:45 AM 7:00 AM O mins

    Thank you

  27. I noted in your formulas above the date and time are in the same cell. How do you work these formulas with the dates and times in separate cells? Is it possible or do they have to be in the same cell. I'm trying to calculate the time an individual would be held in restraints. The individual cells have Event Begin Date, Event Begin Time, Event End Date, Event End time. All the cells are in general format since they are exported from a database into Excel. What guidance can you give me.

  28. I am trying to calculate the sum of two time(S).
    I tried to add 15:05:30 and
    14:02:00 and used the formula =sum(DE19:DE20)resulting into 5:07:30. After using =sum($DE$19:$DE$20), I got the same result. Please help.

    Also please tell me if I want to add multiple timestamps each timestamp of the format(day hour:month:second),which formula I should apply?

    1. Your expected results would be 29:07:30. Since one day is 24 hours, the balance you see is 5:07:30. It is a question of formatting the results (Control+1). Try using a "custom" format of [h]:mm:ss.

  29. Hi,
    If i insert a time in a colum, i need to change the time automatically in another colum.
    for example if i put 10:00 in A1 then automatically B1 need 10:05 and C1 will 10:20 like.
    Please help me.
    Thx

  30. I need a formula to calculate the time difference with sum subtraction on time

    for example:

    11:00 PM -01:00 AM = 02:00 HRS

    but 11:00 PM -01:00 AM to show me nothing

    Kindly help me on this.

    Thanks in advance

  31. If I have total hours of let's say 52 for a work week and only want 4o hours to show as "Regular Hours" and the additional 12 hours to be in a different cell for "Overtime Hours"?
    I have tried IF and MIN formulas but neither seem to work. I have a feeling it has something to do with the way I am writing them, i.e., =IF(XX>40,40,XX) or =MIN(40,XX) where "40" is not in a "TIME" format.

    What am I doing wrong?

  32. Hello there,

    None of these formulas work on my time cell. It is in the following format: 9:23am and I have tried all of the formulas to subtract one time from another also, to convert the time to 24 hour clock and nothing works on my time column! It is exported data from Xero accounting software and I split out the date from the time, but now I'm really stuck. Help?

  33. I am using the following formula to calculate difference in time. =INT(B1-A1) & " days, " & HOUR(B1-A1) & " hours, " & MINUTE(B1-A1) & " minutes and " & SECOND(B1-A1) & " seconds" however I wanted to see if it was possible to put some time of if, then formula with it to remove any hours that are between 5pm and 8am also any hours that are on a week end so 48 hours for Saturday and Sunday?

  34. I need to create a spreadsheet with 40 names for start lunch and end lunch

  35. Hi,

    I have shift start time & shift end time
    I want to calculate total hours worked during 22:00-6:00
    Please help me with the formula

  36. Thanks

    It is very convenient and useful to us.

  37. Dear Sir,

    I use this formula to calculate time difference in Days hours and minutes

    =INT(B2-A2) & " days, " & HOUR(B2-A2) & " hours, " & MINUTE(B2-A2) & " minutes"

    Which shows the difference of time in Column C now in C i have 10 differences of time from C2 TO C12 now i want to take sum of these 10 values in Column C as a total like Total Days & Hours & minutes what formula should i apply ??

  38. How can we change the real time to accumulative time:

    11:20:32
    11:32:36
    11:41:28
    11:53:36
    12:02:48

    how can I change this to
    0
    12
    .
    .
    .

  39. Hi, I need your help please let me know how to calculate the difference between two date using networkdays formula.

    exp: Start date 4/12/16 3:51 PM and end date 4/20/16 5:00PM.

    currently I am using below formula,
    =IF(INT(AJ3-F3)>0,INT(AJ3-F3)&"d","")&IF(HOUR(AJ3-F3)>0,HOUR(AJ3-F3)&"h")

    Above is not working for networkdays.

    Please suggest.

  40. HI, I need help with a time formula. C1 is 13:40, C2 is 16:40 and C3 gives me the difference of 3:00. I have these formulas set up. What i am trying to do is check another cell, (C5) for "specific text" prior to returning a result of C3 as the full value (3:00) or half value (1:30) to C6.

    Thanks in advance
    Doug

  41. HI, I need help with a time formula. C1 is 13:40, C2 is 16:40 and C3 gives me the difference of 3:00. I have these formulas set up. What i am trying to do is check another cell, (C5) for "specific text" prior to returning a result of C3 as the full value (3:00) or half value (1:30) to C6.

    Thanks
    Doug

  42. =A2 + TIME(2, 0, 0)

    hello

    thanks a lot it was very good but I have a question.

    I want to add one hour to my time but I have a large data in one column, how can I write the formulation to apply it for whole of the data in the column once?

    thanks in advance

  43. I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.

    (Start Date/time) A1 = 11/25/2016 14:00
    (Hour to fix) B2 = 20 hours
    (Fix date/time) C1 = fix time?
    Business hour = Mon - Sun 08:00 - 20:00

    I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task including weekends (End Date). This would be based on a certain number of "business hours" that would be calculated in another cell.

  44. I'm Trying to Subtract the Below said wrong time format, Answer should be =30, Please help me

    A1 = 03.00PM
    A2 = 2:30 PM

  45. Hi,

    I need to calculate day wise hour wise penalty.

    In a day (24 hours) exemption hours are 20 (total 24 months).

    If down time is > 4 hours in a day, Per Hour Rs.200/- penalty.

    Pls share formula..

  46. Best explanation I have ever seen... Many thanks to Svetlana Cheusheva.

  47. I am trying to calculate total hours worked in a day with the result being in number instead of time. For Example 8.5 instead of 8:30. Using clock in(C27), lunch out(G27), lunch in(H27), clock out(L27) to calculate total hours. I am using =(L27-H27)+(G27-C27). I also have the cell with the results formatted as (Custom [h]:mm). What can I add to the formula or how should I format the cell to change the results to my liking?

  48. i am calculating day and month difference in twodays
    cell a1 07/07/2016
    cell b1 07/10/2016
    formula =TEXT(B1-A1,"m") it displaying 4 but the difference in month is 3

    =TEXT(B1-A1,"dd") its displaying 1 but the difference in days should b 0

    plz revert

    and plz let me know also can v calculate years in two dates

  49. does anyone have a template?

  50. I would appreciate help with formulas for the following:
    Sign in time: 9:35am
    Minus max duty day: 18 hours
    Minus debrief: 30 minutes
    Minus flying time: 14 hours, 20 minutes
    What I need is the final time after the above calculations.

    Thank you!

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