Workday function returns the wrong date when the duration is 5 day

[KarenMA]

I am calculating end dates, considering holidays.
I also have an "IF" in the formula.
Cell F17 has the following:
=IF(D17=1,E17,WORKDAY(E17,D17,$D$92:$D$97)-1)
When the number of days in cell d17 is '5' the workday returned is always
off by 2 days. In this case the start date is 11/2/2009 and duration is 5
days. the finish date should return 11/6/2009 but instead returns 11/8/2009.
This is consistant whenever I have 5 days.

Can anyone explain what I am doing wrong. All of my other Worday functions
are correct in this sheet.

 

[Mike H]

Hi,

First could I suggest that when posting date questions you make the dates
unambiguous. To me 11/2/2009 is 11 February what is it to you? I have assumed
2 November.

2 November 2009 is a Monday and if you add 5 workdays we get 9 November if
we add 4 workdays Excel returns 6 November which is a Friday. In other words
Workday doesn't include the start day in the count of days to add so you are
getting the correct answer.

Mike

 

[Mike H]

I should have added as a messy workaround and if you want to include the
start date as one of the working days you could do this.

=WORKDAY(E17,D17-1,D9297)

So for your posted examplt this will return 6 Nov and while not extensively
tested should work consistently provided you don't schedule a saturday or
sunday as the start date.

Mike

 

[Ron Rosenfeld]

I am calculating end dates, considering holidays.
I also have an "IF" in the formula.
Cell F17 has the following:
=IF(D17=1,E17,WORKDAY(E17,D17,$D$92:$D$97)-1)
When the number of days in cell d17 is '5' the workday returned is always
off by 2 days. In this case the start date is 11/2/2009 and duration is 5
days. the finish date should return 11/6/2009 but instead returns 11/8/2009.
This is consistant whenever I have 5 days.

Can anyone explain what I am doing wrong. All of my other Worday functions
are correct in this sheet.

Your problem is that you are including your start date in the count. The
WORKDAY function does not do that ordinarily (although the NETWORKDAYS function
does).

Your method of subtracting 1, as well as your D17=1 clause, to compensate for
this behavior doesn't work because you are subtracting one "day" when you
really want to subtract one "workday".

Perhaps this will work for you:

=MAX(E17,WORKDAY(E17,D17-1,$D$92:$D$97))

--ron

 

[Ron Rosenfeld]

Perhaps this will work for you:

=MAX(E17,WORKDAY(E17,D17-1,$D$92:$D$97))

I should add that this will only work if D17 is never less than 0. If D17
could be negative, then:

=IF(D17>=0,MAX(E17,WORKDAY(E17,D17-1,$D$92:$D$97)),
MIN(E17,WORKDAY(E17,D17+1,$D$92:$D$97)))

--ron