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

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

**Times**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 is a **date serial number** and it makes Date calculations easy.

You might 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.

## 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 Two Dates in Excel

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.

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.

## The NETWORKDAYS Function

People also asked how to calculate the time between days but without taking into account weekends or specified holidays. The NETWORKDAYS function will help you to calculate the time period by excluding weekdays and specified holidays.

The formula looks like this: =NETWORKDAYS(start_date, end_date, [holidays])

Like in the previous case the start_date and end_date are required arguments, yet the argument [holidays] is optional. You can use this argument to specify which days should be excluded.

This time I used two random dates which contained two weekends and I added two desired dates under the holidays argument just to show you how it works.

If you need to add some weekends you will have to add one more argument (weekends) and use =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

After you calculated the days by using the NETWORKDAYS formula, to calculate the hours you can simply multiply the given number with the number of hours you need for as long as they stay the same. Also, do not work harder by looking for complicated formulas to calculate simple things.

**What else you should know:**

- If the start date is later than the end date the formula will generate a negative number.
- The NETWORKDAYS formula will also include the start and the ending dates in the calculations, due to this if you have the same date the value returned will be 1.

If you want to find more formulas regarding the date and time feel free to check the following list of functions.

I hope this article helps you to better understand how to calculate hours between two dates in Excel. You can exercise what you learned today by calculating the time left until your next birthday, to do this you can also use the =Today function or the =Now function.

If you found this article helpful, please feel free to check our other articles about tips and tricks in Excel. We also have a dedicated section for details and formulas about dates and times in Excel.

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

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

GregoryExcel’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.

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

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

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

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

<>

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

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

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

DavidAmended file at http://davidj.richardson.name/external/Time-Difference-2.xls — look at the yellow cells.

PaulaHi,

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

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.

Kanchan JoshiHi 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”

RoyGregory,

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

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

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

TimHi 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

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

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

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

SurenHi 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

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

and here is yours:

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.

Kanchan JoshiHi 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

GregorySorry 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 AMin cell A1 and8/19/2012 6:30:00 AMin cell B1.This formula

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

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

=TEXT((B1-A1),”d”) & ” days, ” & TEXT((B1-A1),”h:m:s”) & ” (h:m:s)”will give you0 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.

RyanI 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

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

Islamthanks this was really useful

John RiddellI 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.

Jyothi4-Dec-12 6:22:00 PM 5-Dec-12 12:35:00 PM

Pls let me know the formula for the above date and time

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

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

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

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

VijayA1 = 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.

GregoryThe formula is =(A1-B1)*24 and you need to make sure the cell formatting is General, and not Date/Time.

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

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

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

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

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

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

RuchitSuppose 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

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

DhanaDear 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…

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

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

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

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

JeanWe 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

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

anshulhi ,

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 )

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

Thanks,

Anshul

GregoryThe format you specify does not exist in Excel and Excel will not recognize it as a date or time format.

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