Free Microsoft Excel 2013 Quick Reference

Calculating networkdays and business hours Results

I have been trying to come up with a formula that will calculate the time
difference between two given cells. To get the correct amount of time it
takes employee's to complete the task, I must factor in networkdays and
business hours (8am-5pm). Unfortunately I have been unsuccessful in my
attempts to calculate both factors. Is there such a formula? Any help is
greatly appreciated!

09/15/06 10:05:56 09/18/06 12:15:25 - difference is ???

Hi! I am trying to calculate the elapsed business hours (need it to be more granular than the networkdays function) between two date/timestamps. For instance, if my start time is 4pm Monday, and end time is 9am Tuesday, the elapsed business time is 2 hours (assuming 8-5 business hrs), but networkdays would calculate 2 days. I think we have found a way to write a formula to calculate the hours on day 1 and the hours on the last day, then the days between (with networkdays-2), and add these together. I believe this works for any situation where the # of days >2; we may need some if/then processing for smaller time periods. Just checking to see if anyone knows of an easier way, or a pre-written function from a third party? Thank you so much! I love this forum and the rules associated with it - very refreshing!

Dear all

I am shared a problem which is related with calculate the SLA Non-Business hours report. I am successfully calculating Business hour report excluding the holiday by using formula. The formula is
 And it’s working fine.
I want a that kind of formula to calculate the non-business hour
Not like Non-Business = Total downtime – Business hour
My Business Hours: 9:00 – 19:00
Non Business Hours: 19:00 – 9:00
I am waiting for a positive reply

Thanks
Tirtha sarathi

I am trying to calculate downtime for a Service Level Agreement.

The data that I have is the start date/time and the resolved date/time for an incident.

The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours.

As example(s)
- if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours.
- if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours.

The above assumes working hours are 8:00 AM through 5:00 PM.

I have tried using the NETWORKDAYS and WORKDAY functions with little success.

TIA,

RM.

Hi

I use this formula at work to calculate business hours from Mon-Fri:

=IF(OR(L10="",L11=""),"",(NETWORKDAYS(L10,L11,$N$2:$N$23)-1)*($Q$3-$Q$2)+IF(OR(ISNUMBER(MATCH(INT(L11),$N$2:$N$23,0)),WEEKDAY(L11,2)>5),$Q$3,MEDIAN(MOD(L11,1),$Q$3,$Q$2))-IF(OR(ISNUMBER(MATCH(INT(L10),$N$2:$N$23,0)),WEEKDAY(L10,2)>5),$Q$2,MEDIAN(MOD(L10,1),$Q$3,$Q$2)))

where Q3= business start time 8.30am
where Q2= business end time 5.30pm

thus the difference between 18-Apr-08 16:30 and 21-Apr-08 13:30 is 6 hours.

I now need to adapt this formula for another Department that also works on Saturday from 8.30am to 5.30pm.

Can anyone help?

Regards

R

I have done a lot of research using these groups but just can't get a
solution to what I'm after: I would like to calculate a future (EndDT)
date based on the following given input: StartDT & Time; DayStart;
DayEnd; HolidayList; OffSet (in hh:mm for the future date calculation).
What is very important is that the calculated future date must be a
date and time and the calculation must only use weekdays and business
hours for the future date calculation (no weekends, no holidays).
Somehow I cannot string together the correct logic using NETWORKDAYS
(and some other crude ways I'd rather not mention!). I did find a
number of very good examples but (most) are based around a given start
& end date. If anyone can help, please advise. Many thanks in advance,
Chris.

I found some help in another post which brought me a bit closer to the
result I am looking for. I need to calculate business hours between 2
dates. I am using a formula that I found on the site which excludes
weekends, which is one piece of it, but I also need to know how to
modify this formula even more to show a start time and a cutoff time.

This is the formula I am using:

=NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

*** This is the dilemma, lets say that the business hours are from
7a-6p, so if something was submitted after 5pm, I wouldn't want the
time to be calculated until the beginning of the next working day. **
Is this possible?

Ex. 3/27/2006 17:22 - 3/28/2006 8:24 would show 15:02 as the result.
But because of the time this was submitted, I would want the time to
start from 7a on 3/28/06. So in actuality, I would want the result to
be 1:24.

--
tanya216
------------------------------------------------------------------------
tanya216's Profile: http://www.excelforum.com/member.php...o&userid=33149
View this thread: http://www.excelforum.com/showthread...hreadid=529630

I have done a lot of research using these groups but just can't get a
solution to what I'm after: I would like to calculate a future (EndDT)
date based on the following given input: StartDT & Time; DayStart;
DayEnd; HolidayList; OffSet (in hh:mm for the future date calculation).
What is very important is that the calculated future date must be a
date and time and the calculation must only use weekdays and business
hours for the future date calculation (no weekends, no holidays).
Somehow I cannot string together the correct logic using NETWORKDAYS
(and some other crude ways I'd rather not mention!). I did find a
number of very good examples but (most) are based around a given start
& end date. If anyone can help, please advise. Many thanks in advance,
Chris.

Hello-

I have a question on how to calculate the # of business hours between to time date fields, we staff on weekends so I don't want to eliminate them. I was using NetWorkDays until we added the weekend hours and am not sure of a work around.
Cell A1 = 2/20/2010 2:30:00 PM
Cell B1 = 2/21/2010 2:45:00 PM
Cell C1 = 9:15:00 [HH:MM:SS]

Weekend business hours are 7:30AM to 4:30PM

Any suggestions would be appreciated.

Thanks
ClikClak

I found some help in another post which brought me a bit closer to the result I am looking for. I need to calculate business hours between 2 dates. I am using a formula that I found on the site which excludes weekends, which is one piece of it, but I also need to know how to modify this formula even more to show a start time and a cutoff time.

This is the formula I am using:

=NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1)

*** This is the dilemma, lets say that the business hours are from 7a-6p, so if something was submitted after 5pm, I wouldn't want the time to be calculated until the beginning of the next working day. ** Is this possible?

Ex. 3/27/2006 17:22 - 3/28/2006 8:24 would show 15:02 as the result. But because of the time this was submitted, I would want the time to start from 7a on 3/28/06. So in actuality, I would want the result to be 1:24.

Hi,

I am using the following formula to calculate business hours.

=(NETWORKDAYS(R9,T9)-1)*("17:00"-"08:00")+IF(NETWORKDAYS(R9,T9),MEDIAN(MOD(T9,1),"17:00","08:00"),"17:00")-MEDIAN(NETWORKDAYS(R9,T9)*MOD(R9,1),"17:00","08:00")

The business hours considered here is 8AM - 5PM, Start time in R9 and End time in T9.

Now the problem is its calculating the correct value when the days are same, for e.g.,

Condition 1

When I am giving "31 March 2009 15:00:00" as start time (R9) and "31 March 2009 23:00:00" in end time (T9), I am getting the correct value. i.e, "2:00:00"

Condition 2

While giving "31 March 2009 16:00:00" as start time and "01 April 2009 09:00:00" as end time I am getting a value of "1:00:00", actually the value should be "3:00:00".

Can anyone please help me to find where the issue is...

Also, is that possible to make a list of non working days which would be considered while calculating the Business Hours

Regards,
Sreerag

Hi

Trying to calculate the Service Level End Time, based on the the start time and our current service levels, however this only needs to include business hours (08:30 - 17:30)

e.g
A call comes in at 1/09/2009 4:00:00 AM.
We have 4 business hours to respond.
SLA end time would be 2/09/2009 11:00:00 AM.

I've tried using the NETWORKDAYS function, but can't get it to work. Any help would be appreciated.

Hi, all sifu,
i have been googling a while and find no near remedy.Hope someone can help.

The current formula work very well for working business hour. Help need is: I like to include a difference set of business hours for Weekend ie: Sat and Sun.

=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business hour (7:00am)
I2 = start of business hour (10:00pm)
J2:J4 = Holidays (If require) just add to Networkdays(x,x,j2:j4)

K2 = Start business hour 9:00 am (Sat and Sun)
K3 = End business hour 9:00 pm (Sat to Sun)

is it possible? any help appreciate. Thanks.

Hi-
I would like to know if the formula I found from Biff's post will work for
my situation:
I need to find the elapsed time between 2 dates which includes business
hours only,
e.g. 1/12/06 3:35 PM to 1/17/06 8:23 AM
The Formula that Biff wrote is
=SUM(18/24-(A1-INT(A1)),(B1-INT(B1))-8/24)+(NETWORKDAYS (A1,B1)-2)*9/24
Will this formula work for the hours of 8-5p, and exclude holidays?

**Also, I need to calculate total business hours but also include non
business hours:
1/12/06 5:55 PM to 1/16/06 11:44 PM. Is there a separate formula for that as
well?

thanks for your help!
--
Erin

Please help me to calculate the time difference between two date which should not include (1. holidays, 2.Weekends, 3.non business work timing)

(e.g) start date & Time: 03/15/2010 07:00 AM
End date & Time: 03/17/2010 10:36 AM

Start Time: 03/15/2010 07:00 AM (cell A2)
End Time: 03/17/2010 10:36 AM (cell B2)

And my working days are Monday to Friday

And my working Hours are 07:00 to 19:00

It should calculate only for business days(Monday to Friday)

It should not calculate the holidays(i.e) Christmas, New Year, Thanks Giving Day etc...

It should calculate duration between the above mentioned date and timing

Kindly reply me back soon. Egarly waiting for your reply!

Thank you very much for your valuable time patience and for your help...

Thank you very much

Marc

Hi below is a sample of how my worksheet look like.

Starting from B1 cell

Start date Start time End date End time
8/5/2010 9:00 8/5/2010 14:00
8/7/2010 9:00 8/9/2010 15:00

Working Day Mon to Fri 0830 to 1730 Lunch hr 1
Working Day Sat 0830 to 1230 Lunch hr 0

Start date Day 1 End date Day 2 Elapse Time Total Hrs
8/5/10 9:00 Thu 8/5/10 14:00 Fri 4:00
8/7/10 9:00 Sat 8/9/10 15:00 Mon 2.21

The formula that i using on Elapse Time are

=(NETWORKDAYS(B9,D9)-1)*("17:30"-"08:30")+D9-B9-(TIME(1,0,0))

Which is the right answer.

QNS 1

But there is an scenario where employee tend to end work before lunch hour. So in this case, is there a formula to work with above formula that indicate different lunch hour whereby if employee end work before lunch or start work after lunch hour. The minus 1 hour will not be calculated.

QNS 2

How do I get networkday to calculate through Sat to Mon and exclude Sun? Given Sat = 4 hours, Mon = 9 hours of business hours.

Thanks.

Hi,

I thought I would find plenty of help for this around the web and in these forums, but alas, I have not.

What I need to do is calculate the number of hours between two times, but only within a set 13 hour business day. 8am-9pm 7 days a week.

I have used networkdays before, but that excludes weekends.

For example, Monday 8am till Thursday 11pm, calculate the number of hourse and minutes. BUT exclude each night's hours between 9pm-8am.

Hope that makes sense!

Thanks for ANY help.

narelle.

this is my first post so if you need more info just let me know.

I am trying to write a formula that will calculate the time between two dates/times. The clock should stop on all weekends and federal holiday. It should also stop at 5 PM and resume at 8 AM the next business day. If the second date is during a non-working day or hour, it should calculate as 5PM the previous business day

The following formula gets me part of the way but it seems to have a problem when the start date is on a holiday or weekend.

=IF(AND(INT(A5)=INT(B5),NOT(ISNA(MATCH(INT(A5),D$2:D$113,0)))),0,ABS(IF(INT(A5)=INT(B5),ROUND(24*(B5-A5),2),(24*(F$2-E$2)*(MAX(NETWORKDAYS(A5+1,B5-1,D$2:D$113),0)+INT(24*(((B5-INT(B5))-(A5-INT(A5)))+(F$2-E$2))/(24*($F$2-$E$2))))+MOD(ROUND(((24*(B5-INT(B5)))-24*E$2)+(24*F$2-(24*(A5-INT(A5)))),2),ROUND((24*(F$2-E$2)),2))))))

A=Application date
B=First activity date

D=List of excluded dates
E=Business day start time
F=Business day end time

I have attached a sample spreadsheet with the formula above.

Thanks for the help

In an earlier post daddylonglegs showed a great way of calculating time differences accommodating Working hours exclusive of weekends, would it be possible to change the weekdays taken into account e.g Saturday- Wednesday

The below link shows how to exclude specific days of the week without networkdays but does not calculate for working hours

http://www.cpearson.com/excel/betternetworkdays.aspx

Originally Posted by daddylonglegs
I do have a formula that accomodates start and/or end times outside the business hours, i.e.

=(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3, J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)

Where A2, B2, J2 and J3 are as before. This formula doesn't take lunch into account, do you need that?

Hi,

I am trying to write an excel formula that will calculate the time difference (hours and minutes) between two dates (the time a ticket was opened and the time it was closed), taking into consideration only working hours (8:30 am - 5 pm) and excluding weekends and holidays.
For example:
Ticket opened on : Friday Mar 4 2011 4:00 pm
Ticket closed on : Tuesday Mar 8 2011 9:00 pm
Holiday ex: Mon Mar 7 2011

Then the answer should be: 1.5 hours

I am aware of NETWORKDAYS formula which handles weekends, any help is appreciated in adding to the formula to count for business hours and exclude holiday days (I suppose the holidays are to be stored seperately in the sheet).

Thank you.
Ray


No luck finding an answer? You could always try Google.