Business Days Including Holidays
 

Hello,

I am trying to calulate days to ensure a task was done in 1 business day or
5 business days for particular groups. I know that I can use NETWORKDAYS but
here is the issue:

If a holiday is returned,within a range of dates, how do I find the next
business day and add it to tell me if i day or 5 days?

Can anyone assist??

Business Days Including Holidays
 

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

Don't use NETWORKDAYS(), use WORKDAY(). Look in HELP for details.

--
Kind regards,

Niek Otten

"Dmorri254" wrote in message ...
| Hello,
|
| I am trying to calulate days to ensure a task was done in 1 business day or
| 5 business days for particular groups. I know that I can use NETWORKDAYS but
| here is the issue:
|
| If a holiday is returned,within a range of dates, how do I find the next
| business day and add it to tell me if i day or 5 days?
|
| Can anyone assist??

Business Days Including Holidays
 

Try something like this:

With a list of holiday dates in J1:J10
For a start date in A1
B1:
=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)

Note_1: in case of text wrap, there are no spaces in that formula.

Note_2: That formula is part of the Analysis ToolPak addin. If you get the
#NAME! error, then the addin enabled (and possilby installed).

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dmorri254" wrote:

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

the workday function has a 3rd argument which allows you to supply a list of
holidays - an array of holiday dates or a reference to a range that contains
a list of holiday dates

WORKDAY(start_date,days,holidays)

Easiest to Use it.

--
Regards,
Tom Ogilvy




"Ron Coderre" wrote:

Try something like this:

With a list of holiday dates in J1:J10
For a start date in A1
B1:
=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)

Note_1: in case of text wrap, there are no spaces in that formula.

Note_2: That formula is part of the Analysis ToolPak addin. If you get the
#NAME! error, then the addin enabled (and possilby installed).

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dmorri254" wrote:

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

Tom:
I found inconsistencies in trying to apply WORKDAY "as is" to my
interpretation of the OP's situation when the entered StartDate is on a
weekend or holiday, assuming no actual work would be done on the weekend.

If the goal is to complete a task within 5 workdays, here's what I got using
just WORKDAY(StartDate,4) and ignoring holidays (for simplicity):

StartDate Calcd_EndDate NetWorkDays
Sun, January 01, 2006 Thu, January 05, 2006 4
Mon, January 02, 2006 Fri, January 06, 2006 5
Tue, January 03, 2006 Mon, January 09, 2006 5
Wed, January 04, 2006 Tue, January 10, 2006 5
Thur, January 05, 2006 Wed, January 11, 2006 5
Fri, January 06, 2006 Thu, Jan 12, 2006 5
Sat, January 07, 2006 Thu, Jan 12, 2006 4

Consequently, I adjusted my formula to calc the 4th workday if starting on a
weekday or the 5th workday if starting on a weekend or holiday.

Of course, if I'm wrong and the startdate should always count as the 1st day
then only the WORKDAY function would be necessary.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tom Ogilvy" wrote:

the workday function has a 3rd argument which allows you to supply a list of
holidays - an array of holiday dates or a reference to a range that contains
a list of holiday dates

WORKDAY(start_date,days,holidays)

Easiest to Use it.

--
Regards,
Tom Ogilvy




"Ron Coderre" wrote:

Try something like this:

With a list of holiday dates in J1:J10
For a start date in A1
B1:
=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)

Note_1: in case of text wrap, there are no spaces in that formula.

Note_2: That formula is part of the Analysis ToolPak addin. If you get the
#NAME! error, then the addin enabled (and possilby installed).

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dmorri254" wrote:

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

Agreed. I didn't read your original post closely enough.

--
Regards,
Tom Ogilvy


"Ron Coderre" wrote:

Tom:
I found inconsistencies in trying to apply WORKDAY "as is" to my
interpretation of the OP's situation when the entered StartDate is on a
weekend or holiday, assuming no actual work would be done on the weekend.

If the goal is to complete a task within 5 workdays, here's what I got using
just WORKDAY(StartDate,4) and ignoring holidays (for simplicity):

StartDate Calcd_EndDate NetWorkDays
Sun, January 01, 2006 Thu, January 05, 2006 4
Mon, January 02, 2006 Fri, January 06, 2006 5
Tue, January 03, 2006 Mon, January 09, 2006 5
Wed, January 04, 2006 Tue, January 10, 2006 5
Thur, January 05, 2006 Wed, January 11, 2006 5
Fri, January 06, 2006 Thu, Jan 12, 2006 5
Sat, January 07, 2006 Thu, Jan 12, 2006 4

Consequently, I adjusted my formula to calc the 4th workday if starting on a
weekday or the 5th workday if starting on a weekend or holiday.

Of course, if I'm wrong and the startdate should always count as the 1st day
then only the WORKDAY function would be necessary.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tom Ogilvy" wrote:

the workday function has a 3rd argument which allows you to supply a list of
holidays - an array of holiday dates or a reference to a range that contains
a list of holiday dates

WORKDAY(start_date,days,holidays)

Easiest to Use it.

--
Regards,
Tom Ogilvy




"Ron Coderre" wrote:

Try something like this:

With a list of holiday dates in J1:J10
For a start date in A1
B1:
=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)

Note_1: in case of text wrap, there are no spaces in that formula.

Note_2: That formula is part of the Analysis ToolPak addin. If you get the
#NAME! error, then the addin enabled (and possilby installed).

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dmorri254" wrote:

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

WOW thanx much you guys...I think we came up with the answer...we created a
lookup for internal holidays..and you are correct we needed to exclude
weekends and holidays...so we used =WORKDAY(H2,5,Holidays)..against the start
time of the task...this created a target date..then to determine if the work
was done ontime, we did the following against the closed time of the task(if
is was balnk then we used
TODAY).......=IF(I2=IF(D2="",TODAY(),D2),IF(D2="" ,"Time Remaining","Sla
Met"),"Sla Not Met")

Thanx for the help

"Ron Coderre" wrote:

Tom:
I found inconsistencies in trying to apply WORKDAY "as is" to my
interpretation of the OP's situation when the entered StartDate is on a
weekend or holiday, assuming no actual work would be done on the weekend.

If the goal is to complete a task within 5 workdays, here's what I got using
just WORKDAY(StartDate,4) and ignoring holidays (for simplicity):

StartDate Calcd_EndDate NetWorkDays
Sun, January 01, 2006 Thu, January 05, 2006 4
Mon, January 02, 2006 Fri, January 06, 2006 5
Tue, January 03, 2006 Mon, January 09, 2006 5
Wed, January 04, 2006 Tue, January 10, 2006 5
Thur, January 05, 2006 Wed, January 11, 2006 5
Fri, January 06, 2006 Thu, Jan 12, 2006 5
Sat, January 07, 2006 Thu, Jan 12, 2006 4

Consequently, I adjusted my formula to calc the 4th workday if starting on a
weekday or the 5th workday if starting on a weekend or holiday.

Of course, if I'm wrong and the startdate should always count as the 1st day
then only the WORKDAY function would be necessary.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Tom Ogilvy" wrote:

the workday function has a 3rd argument which allows you to supply a list of
holidays - an array of holiday dates or a reference to a range that contains
a list of holiday dates

WORKDAY(start_date,days,holidays)

Easiest to Use it.

--
Regards,
Tom Ogilvy




"Ron Coderre" wrote:

Try something like this:

With a list of holiday dates in J1:J10
For a start date in A1
B1:
=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)

Note_1: in case of text wrap, there are no spaces in that formula.

Note_2: That formula is part of the Analysis ToolPak addin. If you get the
#NAME! error, then the addin enabled (and possilby installed).

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dmorri254" wrote:

Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this
grouo has 5 business days to be completed. I need to then have excel give me
the date of the 5th business day excluding the holiday (Juky 4th).

Thanx again

" wrote:

surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the
weekends and holidays to give you the elapsed time - which you can then
use to determine if within 1 business day or 5 business days etc

I remember a good few years ago now building a very complicated formula
to calculate service time which not only had to cater for days, but
allow for a task being logged outside working hours and finished
outside working hours - it had to bring the relevent date back or
forwards as appropriate to ONLY count chargeable elapsed time!

Business Days Including Holidays
 

Ron Coderre Wrote:

=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)


Hi Ron,

doesn't look like there was a need to consider start date on a holida
but if there was you could use

=WORKDAY(WORKDAY(A1,-1,J$1:J$10),5,J$1:J$10

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=53847

Business Days Including Holidays
 

Hi, DL

1)On the train home from Boston, I was thinking that my original formula
could stand some improvements. You took care of that nicely.

2)Regarding:
doesn't look like there was a need to consider start date on a holiday<<

I allowed that the start date might be logged by a help desk that's takes
calls 24/7 but the SLA only includes standard workdays. Whether that's true
or not, the new formula calculates the right target date.

***********
Best Regards,
Ron

XL2002, WinXP-Pro


"daddylonglegs" wrote:


Ron Coderre Wrote:

=WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10)


Hi Ron,

doesn't look like there was a need to consider start date on a holiday
but if there was you could use

=WORKDAY(WORKDAY(A1,-1,J$1:J$10),5,J$1:J$10)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=538475