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