Calculate Hours Between Two Dates and Times in Excel

Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.

Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.

A Date value in Excel looks like this: 40519
A Time value in Excel looks like this: 0.58333

Cell formatting changes how you see these numbers.

The Date: 7 Dec, 2010
The Time: 2:00 PM

How to Calculate Time Between Dates in Excel or the Duration Between Two Dates

If you want to calculate time between dates in Excel or the duration between two dates, you need to understand what they mean first. When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This a date serial number and it makes Date calculations easy.

You ask, “Why is this such a weird-looking number?” Well the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.

In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. (don’t ask) So the serial date 40519 represents 8 Dec, 2014 (Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system).

How to Subtract Time in Excel (Time Difference)

When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly.

Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM.

As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day.

How to calculate time difference in Excel

Dates and Times Together

In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.

So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.

You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833.

So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.

Calculating Hours Between 2 Dates and Times

If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time.  Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. (24 * 4/3 to be more precise)

Time Between Two Times / Dates

Finding the number of hours or the time between two times / dates is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours.

If you want to enter the dates and times separately (which is loads easier than typing in a date/time in one cell) then add the date/times together.

Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24

Here’s a look at a typical worksheet designed to calculate the hours between two dates.

Calculate Hours Between Two Dates and Times

As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2.

Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.

I hope this article helps you to better understand how to calculate time difference in Excel.

Related Posts Plugin for WordPress, Blogger...

53 thoughts on “Calculate Hours Between Two Dates and Times in Excel

  1. Pingback: Calculate Hours Between Two Dates and Times in Excel | Excel Semi-Pro | excel

  2. David

    There are pitfalls to treating time within the day as a fraction, due to the way Excel calculates those fractions.

    For example, the difference between today’s 12:00:01AM and 12:10:01AM is NOT equal to 0:10:00. In fact, for today, only 168 of the 86400 (there being 86400 seconds in a day) possibilities will work as you might expect.

    One way to fix this is to round any times when doing calculations or comparisons: on a quick check, rounding the time to somewhere between 7 and 10 digits will ensure that it works as you would expect.

    1. Gregory

      Excel’s numbers are precise up to 15 digits, and Excel does treat time within one day as a fraction, so I’m not following what you’re saying. If I put a start time of 12:00:01 AM in a cell and and end time of 12:10:01 AM in another cell, then subtract start from end, I get precisely 10:00 minutes of duration between the two. The underlying cell values, their time serial numbers are: start time of 1.15741E-05, end time of 0.006956019 and the duration is 0.006944444 and so Excel is providing sufficient precision to calculate time values.

      The worksheet described in this article was designed to calculate time between two different dates, not within the same day, and so it wouldn’t be able to calculate the example you gave. Sorry if I didn’t make that clear.

      1. David

        It displays as exactly 10:00, but if you type 0:10:00 in another cell and compare the two, they are just barely different. From the calculation you get 0.0069444444444453, from the direct type-in you get 0.0069444444444444, and thus any comparison may fail.

        1. Gregory

          When I type in 10:00 in another cell and compare the values they both come out to 0.00694444444444444. I don’t know why you’re getting a different number for the calculation. Here’s a link to the spreadsheet I’m using. Maybe that will help clear things up.

          1. David

            You are correct to note in your file that endTime – startTime = duration.

            But try endTime – duration = startTime in your file. It doesn’t work.

            <>

          2. Gregory

            In the file I provided, in cell D7 use the formula =C2-D4 which is End Time minus Duration (from the straight data entry) and the answer is 12:00:01 AM, which is the Start Time.

            And I get the same thing if I use =C2-D2 which is End Time – Duration, and get 12:00:01 AM, which is the Start Time.

  3. David

    You are correct to note in your file that endTime – startTime = duration.

    But try endTime – duration = startTime in your file. It doesn’t work.

  4. Paula

    Hi,

    If i need only service time, how can i do?

    For example if my service hour is 9:00 to 18:00 and i need to know total of service time between two dates (not count weekend and holidays), how can i count these?

    Thank you in advance.

    Paula

    1. Gregory

      @Paula, If your service hours don’t change each working day, meaning you would have 18:00 – 9:00 = 9:00 hours every day then you can just count the number of working days and multiply by 9 to get the number of total service hours.

      To count the number of weekdays, minus holidays for your working days, you can use the NETWORKDAYS function, which has three arguments: Start Date, End Date, and Holidays.

      The holidays are usually a range of Holiday dates, but this last argument is optional. If Holidays are not used the formula returns the number of weekdays between the two dates. If there is a Holiday list, then they are subtracted as well.

      As an example, if Start Date is in cell A2 and End Date is in cell B2, and a list of holidays is a named range, HolidayList, then you get =NETWORKDAYS(A2,B2,HolidayList).

      If your service hours change every working day then you would have to do something different.

      Hope this helps.

      1. Kanchan Joshi

        Hi Greg,

        I am not a programmer but I used your formula and it works! Thanks a lot for sharing the details.
        But I also need the result if the day is the same (zero) and hours and minutes are different. Would you please be able to help with the additional patch to the formula for the same day and different time?

        Kanchan

        =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

      2. Roy

        Gregory,

        I can’t get my formula to work and hope you can help.
        I also want to measure only the service time. (based on registered incidents)

        I can measure the working days, but my service times variate. The service window time is between 07:00 and 18:00 (11 hour) on working days.

        For example:

        The incident was opened on 07/09/2012 08:00 and closed on 10/09/2012 10:35
        -8 & 9 September are weekend days

        Therefore the total service time = 10 hours (workday 7-9) + 3 hours 35 min (workday 10-9) = 13 hours, 35 min.

        Can you help wiht this formula?

        Regards Roy

        1. Gregory

          I sent Roy an email that an answer would be blog post worthy. And before I could start writing it, he sent me the following reply.

          Hi Gregory,

          I think I found the answer on a different forum.

          =(NETTO.WERKDAGEN(A2,B2,G$5:G$19)-1)*(“18:00”-“7:00″)+ALS(NETTO.WERKDAGEN(B2,B2,G$5:G$19),MEDIAAN(REST(B2,1),”18:00″,”7:00″),”18:00″)-MEDIAAN(NETTO.WERKDAGEN(A2,A2,G$5:G$19)*REST(A2,1),”18:00″,”7:00”)

        2. Gregory

          Assume Incident Open is 7 Sep, 2012 8:00 and in cell A2. Assume Incident Close is Sep 10, 2012 10:35 and is in cell B2. Assume that Start_Time is 7:00 and End_Time is 18:00. A formula that will exclude weekends, and calculate the number of hours worked is:

          =IF(NETWORKDAYS(A2,B2)<2,MOD(B2,1)-MOD(A2,1),(End_Time-MOD(A2,1)+MOD(B2,1)-Start_Time)+((NETWORKDAYS(A2,B2)-2)*10/24))

          if you format the cell with [h]:mm;@ time format. Using the square bracket around the hour will allow hours to accumulate past 24 hours.

          This formulas will also give you the correct time duration if the incident is opened and closed in the same day, next day or next week.

          I do believe that there is a limit to how many hours will accumulate in the time format. You might check Excel HELP to find out what is the maximum number of hours. I don't know right off the top of my head.

          Hope this helps.

          1. Tim

            Hi Gregory,

            I’m trying to use this formula but I think there’s something missing?
            IF(NETWORKDAYS(A2,B2)<2 is true then the only action you take is MOD(B2,1)-MOD(A2,1) and this doesn't take into account the start and end times?

            Thanks

            Tim

          2. Gregory

            You are correct in that if the time on the second day is less than the time on the first day the calculation returns a negative. So if MOD(B2,1)>=MOD(A2,1) then the proper action is to add a day to the end date and then subtract, like this (MOD(B2,1)+1)-MOD(A2,1) and otherwise MOD(B2,1)-MOD(A2,1) will work.

            Thanks for pointing this out.

  5. kumar

    Hi Gregory,
    Good Day.

    I need your kind assistance here to help me out on getting the answers in day/hours/min/sec. I did use the formula as =MAX(A1:B1)-MIN(A1:B1) and got the answer as 26:49:15. where as the answer i want is like “1 day, 2 hours, 49 minutes and 15 seconds”.

    Please advice and thank you in advance.

    Kumar

    I forgot to mentioned that the value that i used was 23/2/2012 3:25:45 PM in cell A1 and 24/2/2012 6:15:00 PM in cell B1.

    1. Gregory

      In cell C1 use the formula =INT(B1-A1) to get the days. Then you have to get the remaining hours, which is the formula =MOD(B1-A1,INT(B1-A1)) and represents the remaining time. So in cell D2 enter the formula =HOUR(MOD(B1-A1,INT(B1-A1))). In cell E2 enter the formula =MINUTE(MOD(B1-A1,INT(B1-A1))). And in cell F2 enter the formula =SECOND(MOD(B1-A1,INT(B1-A1))). You can string this all together in the following formula =C1 & ” days, ” & D1 & ” hours, ” & E1 & ” minutes, ” & F1 & ” seconds” which will give you 1 days, 2 hours, 49 minutes, 15 seconds.

      But a single day isn’t plural, so you might want to test and see if the formula in cell C1 is equal to 1, like this: =C1 & IF(C1=1,” day, “,” days, “) & D1 & ” hours, ” & E1 & ” minutes, ” & F1 & ” seconds” and then you will get 1 day, 2 hours, 49 minutes, 15 seconds.

      Of course you can combine all these formulas into one cell, instead of many cells, and get a really big formula like this:

      =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

      which is what I think you were asking for, but not expecting.

      Hope this helps.

  6. Suren

    Hi Gregory,
    The below formulae is not working and it says too many arguments in the “Days”

    =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

    Could you please let me know where the problem lies.

    Thank you

    1. Gregory

      Sorry for the delay, but the formula you sent was quite vexing. Aside from the fact that when you copied the formula in my comment section and WordPress added an extra amp; for every & sign, your formula appears to be identical to the one I created.

      However, after removing the amp; it still didn’t work. So I lined it up with my original formula and looked at each character in the formula string and found only one difference, which was very hard to spot.

      It seems that the quote marks you use are different than the one’s I use. My quote marks are perfectly vertical and have a consistent line width, and your quote marks are like mini-comma’s that are slanted. I replaced all your quote marks with the one’s that I use and the formula worked perfectly. Here’s my formula:

      =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

      and here is yours:

      =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

      The quote mark I’m using is just to the left of the Return/Enter button and I use the shift key. I’m not sure what quote mark you are using or why Excel is making the distinction between what I use an what you are using, but it’s real.

      Hope this helps.

      1. Kanchan Joshi

        Hi Gregory,
        I am not a programmer but I used your formula and it works! Thanks a lot for sharing the details.
        But I also need the result if the day is the same (zero) and hours and minutes are different. Would you please be able to help with the additional patch to the formula below for the same day and different time?

        Kanchan

        =INT(B1-A1) & IF(INT(B1-A1)=1,” day, “,” days, “) & HOUR(MOD(B1-A1,INT(B1-A1))) & ” hours, ” & MINUTE(MOD(B1-A1,INT(B1-A1))) & ” minutes, ” & SECOND(MOD(B1-A1,INT(B1-A1))) & ” seconds”

        1. Gregory

          Sorry for the delay in responding. The problem with extending this formula to included the same day is that the MOD function will create a #DIV/0! error. Adding more IF/THEN formulas to this equation makes it unnecessarily complicated. Here are a few alternatives.

          Assume you have 8/19/12 4:00 AM in cell A1 and 8/19/2012 6:30:00 AM in cell B1.

          This formula =TEXT((B1-A1),”d, h:mm:ss” ) will give you 0, 2:30:00 for an output.

          This formula =TEXT((B1-A1),”d”) & ” days, ” & TEXT((B1-A1),”h:m:s”) & ” hours:minutes:seconds” will give you 0 days, 2:30:0 hours:minutes:seconds for an output.

          And this formula =TEXT((B1-A1),”d”) & ” days, ” & TEXT((B1-A1),”h:m:s”) & ” (h:m:s)” will give you 0 days, 2:30:0 (h:m:s) for an output. (My favorite.)

          All shorter and easier to implement. I couldn’t use the original format of xx days, xx hours, xx minutes, xx seconds because the TEXT function sees the “m” as a month and not minutes. You have to use “h:m” before the “m” is recognized as minutes, but the hours are attached, which kind of defeats the purpose.

          Hope this helps.

  7. Ryan

    I need to calculate work hours on different projects. The work days are always the same (Mon. – Sat.), and the work hours in a day are always the same (8 Am – 6 pm) but the start time and finish time of the project vary throughout these parameters. Can you please help. Thanks

    1. Gregory

      It sounds relatively simple until you consider that the start and end days may be a partial day. Oh and don’t count Sundays. I have a few questions: 1) Is a full day considered 10 hours? 2) Are you counting partial days for the start and end days? (example: start Thursday at noon, and finish the following Tuesday at 2:00 PM)

      Send me an email from my contact page with the answers.

  8. John Riddell

    I have recently created a series or worksheets that are linked. The intent is to paste data from a report concerning employee shrinkage for the last 7 days. There are two pieces to this that i am not sure how to complete.

    First I need the excel worksheet to calculate partial days for unscheduled and scheduled absences. scheduled would be sick, vacation, Jury Duty, ect. Unscheduled can also be sick(called one-Click sick) and personal. the description of such event would look similar to the following:
    Time Off [Sick Time] (Sick-Time) from 4:00AM – 4:56AM;source:PTO
    or
    Time Off [Sick Time] (Sick-Time) from 3:00PM – 4:00PM;source:One-Click Sick, PTO

    As you can see, the times displayed do not amount to a whole shift. (9hours,11Hours, or 13 hours) So i want to be able to differentiate from counting a whole day such as the following:
    Time Off [Sick Time] (Sick-Time) from 3:00PM – 12:00AM;source:One-Click Sick, PTO

    The second issue i have is kind-of similar. I want to calculate the amount of overtime hours worked in a single day. The description would show as the following:
    Paid position [Overtime]: TAC Level 1 in TAC Level 1 from 9:45PM – 10:15PM
    or
    Paid position [Overtime]: TAC Level 1 in TAC Level 1 from 9:00PM – 11:30PM
    Both overtime events fall on a Sunday. So i need excel to add the times displayed for that day.
    A copy of the worksheet is available upon request.

    1. Gregory

      Assume 4-Dec-12 6:22:00 PM is in cell A1 and 5-Dec-12 12:35:00 PM is in cell B1. To get the difference in hours use the formula =(B1-A1)*24.

      To get hours only use the formula =INT((B1-A1)*24). And to get the minutes only use the formula =MOD((B1-A1)*24,1)*60.

      If you want the hours and minutes in hh:mm format, just use the formula =B1-A1 and change the cell formatting to Custom with [h]:mm;@ as the type.

  9. Siva

    Need some inputs. Tried using the formulae you had posted in your earlier thread.
    1/18/13 1:00 1/21/2013 5:00 1.17
    1/18/2013 23:00 1/21/2013 5:00 0.25
    1/19/13 1:00 1/21/2013 5:00 0.17
    1/19/2013 23:00 1/21/2013 5:00 -0.75
    1/20/2013 1:00 1/21/2013 5:00 0.17
    1/20/2013 23:00 1/21/2013 5:00 -0.75
    1/18/13 1:00 1/21/2013 5:00 1.17
    1/18/2013 23:00 1/21/2013 5:00 0.25
    1/19/13 1:00 1/21/2013 5:00 0.17
    1/19/2013 23:00 1/21/2013 5:00 -0.75
    1/20/2013 1:00 1/21/2013 5:00 0.17
    1/20/2013 23:00 1/21/2013 5:00 -0.75

    Formula used for 3rd Column.

    =IF(NETWORKDAYS(A2,B2)<2,MOD(B2,1)-MOD(A2,1),("24:00"-MOD(A2,1)+MOD(B2,1)-"00:00")+((NETWORKDAYS(A2,B2)-2)*10/24))

    Still get negative values. Work hours are between 00:00 hrs to 24:00 on weekdays and weekends (sat. & Sun) are holidays (no working hrs)…

    Appreciate your help.

    1. Gregory

      The NETWORKDAYS formula excludes weekends, meaning Saturday and Sunday are not included. This is why those results are not correct.

      Take the last example. Assume 1/20/2013 23:00 in cell A2 and 1/21/2013 5:00 is in cell B2. January 20, 2013 is a Sunday, so the NETWORKDAYS formula ignores Sunday and the formula breaks.

      If you are using Sunday or Saturday for one of your days, the proper calculation is =(B2-A2)*24 which will give you 6 hours.

    2. Gregory

      There is another problem with the formula that I didn’t realize until another reader commented. Regarding negative values here is the answer I gave that directly answers some of the issues with negative values. It refers to the first part of the formula =IF(NETWORKDAYS(A2,B2)<2,MOD(B2,1)-MOD(A2,1)

      You are correct in that if the time on the second day is less than the time on the first day the calculation returns a negative. So if MOD(B2,1)>=MOD(A2,1) then the proper action is to add a day to the end date and then subtract, like this (MOD(B2,1)+1)-MOD(A2,1) and otherwise MOD(B2,1)-MOD(A2,1) will work.

  10. Vijay

    A1 = 12.12.2012 13:30 B1 = 11.12.2012 15.30 I need to subtract A1 by B1 and have to get the result as 22 hours. Please help.

  11. Tof

    How to calculate the difference if the time period includes a daylight saving day? There is one day of 23 hours and one day of 25 hours every year… It makes my daily average calculations incorrect on those days.

    1. Gregory

      You bring up an interesting point. However after putting some Date/Times into a spreadsheet I realized that Excel doesn’t know there is 23 or 25 hours for those particular days. This year daylight savings time begins Sunday March 10 at 2:00 AM and ends on Sunday November 3 at 2:00 AM.

      If I take the difference between the date/times of March 9 at 9:00 AM and March 10 at 9:00 AM, Excel will calculate 24 hours difference. Same thing happens when I take a start day/time of Nov 2 at 9:00 AM and an end day/time of Nov 3 at 9:00 AM, I get 24 hours difference.

  12. Jorge

    I need to calculate the daparture time given delivery time and transit time.
    Cell A1 = 02/03/2013 (Delivery date)
    Cell B1 = 01:00 AM (Delivery Appointment)
    Cell C3 = 10.33 (Transit time- Number of hours)

    1. Gregory

      Add the Date and Time to get a Date-Time number. Use the TIME Function to convert the decimal hours to time, then subtract. Based upon your numbers, here’s the formula:

      =(A1+B1)-TIME(INT(C1),MOD(C1,1)*60,0)

      the rest is: 2/2/13 14:41 which is the departure date/time you are looking for.

      1. Jorge

        Since i am calculating departure time, how can i modify the formula to accomodate loading hours of 8:00AM to 5:00 PM? That is to say, If the calculated departure time is 03/24/2013 6:00 PM, then the formula would return 03/24/2013 5:00 PM, and if the calculated time is 03/24/2013 06:00 AM the formular would return 03/23/2013 5:00 PM. Simply put if the loading time is between 5:00 pm and 11:59 PM then return loading time as 5 PM on the same day, if the loading time is between Midnight and 8:00 AAM then return answer should be load previous day by 5:00 PM.

        1. Gregory

          You will need a nested IF statement. Essentially if the time is less than 8:00 then give me 8:00 and if greater than 17:00 then give me a 17:00, otherwise do the calculation. Assume the formula (A1+B1)-TIME(INT(C1),MOD(C1,1)*60,0) is in cell H1. Your formula would be:

          =IF(MOD(H1,1)<8/24,INT(H1)+TIME(8,0,0),IF(MOD(H1,1)>17/24,INT(H1)+TIME(17,0,0),H1))

  13. Ruchit

    Suppose I want to subtract 07th March,2013, 11:50:15 from 11th March 2013, 15:45:30. And I want to result to be displayed as total number of hh:mm:ss. Is that possible. If it is possible then kindly let me know how to do that. I am doing experiments and will need to calculate time difference between 4-5 days and even longer.

    If I am not doing something wrong then the method described above works only till time difference is 24hr or less.
    Regards,
    Ruchit

    1. Gregory

      Assume 3/7/13 11:50:15 is in cell A1 and 3/11/13 15:45:30 is in cell B1. In cell C1 enter the formula =B1-A1. And then, while cell C1 is active, select the Format Cells dialog box (Ctrl+1, or CMD+1 on a Mac) click on the Number tab, and under Category select Custom. Then in the the Type box use the following cell formatting for time:

      [h]:mm:ss

      The trick here is to put square brackets around the hour [h] which tells Excel to use a cumulative value instead of something within 24 hours.

  14. Dhana

    Dear Friends,

    Cell A1 = 01-Apr-13 (Start date)
    Cell B1 = 10:00 AM (Start Time)
    Cell C1 = 02-Apr-13 (End date)
    Cell D1 = 10:40 AM (End Time)

    I have use this formula to find hrs between both date & time =((C1+D1)-(A1+B1))*24 and get result = 24.66
    But i need like = 24:40
    How to get like this…

    1. Gregory

      Use this formula:

      =(C1+D1)-(A1+B1)

      Then change the cell formatting to:

      [h]:mm

      And you’ll get 24:40 for you answer.

      Multiplying by 24 gives you a decimal hour, so it’s not needed. Putting square brackets [] around the Hour in the time format allows the time to accumulate more that 24 hours.

  15. Sudhir

    Dear Gregory,

    i have a problem in counting date and time. for example our office timings is form 10:00 to 19:00. we have raised a ticket on 17th april 2013 14:00 hrs complaint resolved on 18th april 2013 11:00 hrs. now how we can calculated time exactly. because after 19:00 hrs to 10:00 is non working time. i have to exclude this time. suggest me how i can get exact time it took to close a complaint.

    1. Gregory

      Assume cell A2 contains 4/17/13 14:00 and cell B2 contains 4/18/13 11:00 and assume there is a DayStart value of 10:00 and a DayEnd value of 19:00. The following formula will calculate the working time of the first and last day, and add the 9 hours per day if greater than 2 days. Weekends will be excluded.

      =(((NETWORKDAYS(A2,B2)-2)*(DayEnd-DayStart)*24)+((DayEnd-MOD(A2,1)+MOD(B2,1)-DayStart)*24))

      this formula returns 6 hours.

  16. kyle

    Gee i sure am glad my company works 24h a day and never gives us holidays, sure makes this whole subtracting dates thing a lot easier.

  17. Jean

    We are trying to calculate the number of working hours it takes between two points in time. The formula works great except when the ‘start’ is after the ‘end’. The formula we are using and the cell components are below. We would like the result for the following data to equate to -4.08.

    = (((NETWORKDAYS(‘DATA COLLECTION’!H7,’DATA COLLECTION’!V7,DATA!$B$5)-2)*(DATA!$B$3-DATA!$B$4)*24)+((DATA!$B$3-MOD(‘DATA COLLECTION’!H7,1)+MOD(‘DATA COLLECTION’!V7,1)-DATA!$B$4)*24))

    Data Collection H7: 4/24/13 11:50 AM (start time)
    Data Collection V7: 4/23/13 4:45 PM (end time)
    Data B3: 17:00
    Data B4: 08:00
    Data B5: 5/27/13

    1. Gregory

      As far as I know, time calculations can’t be negative. All the Excel time formula’s I’ve dealt with always give you an error for “negative” time.

  18. anshul

    hi ,

    i have start date in one column and end date in another column and i want to find the number of days with hh:mm:ss format excluding the weekend and any holiday between the two dates. Could you please provide me the formula for the above query?
    For example-
    04/26/2013 01:28 – A1
    05/08/2013 04:50- B1
    06/02/2013 21:55 – O/P (dont whether the time is correct or not just for your reference .I want this format )

    1. anshul

      there is one correction the O/P should be in this format 12:03:21:47( just for a reference)

      Thanks,
      Anshul

    2. Gregory

      Given what is in cell A1 and B1, you can enter the following formula in any cell. Lets pick C1.

      =(B1-A1)-((INT(B1)-INT(A1))-(NETWORKDAYS(A1,B1))+1)

      Then you have to format the cell with the custom time format [h]:mm:ss;@ that will give you the result 195:22:00. If you need days and then hh:mm:ss you can enter the following formula in cell D1 to get the number of days. =INT(C1) and you can enter the following formula in cell E1 to get the remaining hours =MOD(C1,1) and this cell will need the hh:mm:ss format.

Comments are closed.