Free Microsoft Excel 2013 Quick Reference

# Calculating networkdays and business hours Results

## Calculating Networkdays and Business Hours

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

## Formulas: Calculating elapsed business hours (not d

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
My Business Hours: 9:00 – 19:00
Non Business Hours: 19:00 – 9:00
I am waiting for a positive reply  Thanks
Tirtha sarathi

```

## Calculate Business Hour Downtime Excluding Weekends

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

## Tricky Date calculation: How to calculate a future date

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

## Calculating Business Hours Between 2 Dates

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

## Tricky Date calculation: How to calculate a future date

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

## Work Hours Between two dates including weekends

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

## Calculating Business Hours Between 2 Dates

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

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

Regards,
Sreerag

## SLA End time for business hours only

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.

## Calculate Networkdays & 2 set of business hours

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)

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.

## Another question about time and date

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
1/12/06 5:55 PM to 1/16/06 11:44 PM. Is there a separate formula for that as
well?

--
Erin

## How to find out the networkday...???

(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

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

Thank you very much

Marc

## Which formula to add to Networkday formula to work with specified lunch hour?

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

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.

## Calculating Elapsed Time - but not overnight.

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.

## Processing Time that excludes holiday, weekends, and non-business hours

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

I have attached a sample spreadsheet with the formula above.

Thanks for the help

## Time Calculations(Cont...)

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

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?

## Difference between 2 dates - excluding working hours, weekends and holidays

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