Business Days Including Holidays

[Dmorri254]

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

[[email protected]]

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!

[Dmorri254]

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

"[email protected]" 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!
>
>

[Niek Otten]

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

--
Kind regards,

Niek Otten

"Dmorri254" <[email protected]> wrote in message news:[email protected]...
| 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??

[Ron Coderre]

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
>
> "[email protected]" 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!
> >
> >

[Tom Ogilvy]

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
> >
> > "[email protected]" 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!
> > >
> > >

[Ron Coderre]

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
> > >
> > > "[email protected]" 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!
> > > >
> > > >

[Tom Ogilvy]

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
> > > >
> > > > "[email protected]" 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!
> > > > >
> > > > >

[Dmorri254]

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
> > > >
> > > > "[email protected]" 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!
> > > > >
> > > > >

[daddylonglegs]

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

[Ron Coderre]

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