Free Microsoft Excel 2013 Quick Reference

- Calculating Networkdays and Business Hours
- Formulas: Calculating elapsed business hours (not d
- SLA non-business hour calculation
- Calculate Business Hour Downtime Excluding Weekends
- Calculating Business Hours
- Tricky Date calculation: How to calculate a future date
- Calculating Business Hours Between 2 Dates
- Tricky Date calculation: How to calculate a future date
- Work Hours Between two dates including weekends
- Calculating Business Hours Between 2 Dates
- Calculation of business hours
- SLA End time for business hours only
- Calculate Networkdays & 2 set of business hours
- Another question about time and date
- How to find out the networkday...???
- Which formula to add to Networkday formula to work with specified lunch hour?
- Calculating Elapsed Time - but not overnight.
- Processing Time that excludes holiday, weekends, and non-business hours
- Time Calculations(Cont...)
- Difference between 2 dates - excluding working hours, weekends and holidays

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

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

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.

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

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.

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

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

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.

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

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.

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.

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

(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

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.

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.

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

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?

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.