Calculate Due Date

[Guest]

I have am trying to caculate a due date based on adding 8 calendar days.
Easy enough to do but if the date lands on Saturday or Sunday I need the due
date to fall back to the previous Friday and I also need to exclude holidays.
Thanks.

 

[Bob Phillips]

Try this

=A1+8-(WEEKDAY(A1)>6)-(WEEKDAY(A1)>6)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I get a VALUE error on that. I copied the formula and pasted it into the
spreadsheet.

 

[Bob Phillips]

Well, apart from my typo (which just the wrong answer not #VALUE), it does
work.

This is the corrected version

=A1+8-(WEEKDAY(A1)>5)-(WEEKDAY(A1)>6)

What do you have in A1?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

OK, I am able to add 8 days and then calculate if that date is on a Sat or
Sun and have it fall back to Friday with

=IF(WEEKDAY(C3+8,2)=6,B3-1,IF(WEEKDAY(C3+8,2)=7,B3-2,B3))

But I still can't find a way to exclude holidays...

 

[Daniel.M]

OK, I am able to add 8 days and then calculate if that date is on a Sat or
Sun and have it fall back to Friday with

=IF(WEEKDAY(C3+8,2)=6,B3-1,IF(WEEKDAY(C3+8,2)=7,B3-2,B3))

But I still can't find a way to exclude holidays...

For holidays, you need a range to keep your holiday dates (as they are different
from country to country)

If I understood correctly, you need to add 9 days and substract 1.
Thus:

=WORKDAY(C3+9,-1,holidays)

Now if the 9 days ahead need to exclude holidays and weekends as well, then:

=WORKDAY(WORKDAY(A1,9,holidays),-1,holidays)

Regards,

Daniel M.

 

[Bob Phillips]

I see that Daniel has a different slant than me, he assumes that you mean 8
working days, whereas I thought you wanted 8 days irrespective, but not
landing on a weekend. You point re holidays probably means that I have
mis-interpreted, but I don't think you mean 8 working days, more likely 6
working days. Can you clarify to ensure the correct answer?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Let me explain in more detail ... I have a date in A1. Based on that date I
have to count ahead 8 calendar days to determine a due date. BUT ~ If that
date falls on a Saturday or Sunday then I need the due date to fall back to
Friday and if it lands on a holiday, I need it to fall back to the previous
working day. Thanks to you both for your help, I've definitely learned from
your comments.

 

[Myrna Larson]

This formula uses NETWORKDAYS to determine whether 8 days in the future falls
on a work day. If so, use that date; otherwise, start with the date+8 and back
up one workday (using the WORKDAY function to find that date).

=IF(NETWORKDAYS(A1+8,A1+8,HolidayList)=1,A1+8,WORKDAY(A1+8,-1,HolidayList))

If the formula gives you a #NAME! error, look at help for either function on
how to install the ATP.

 

[Bob Phillips]

Shouldn't it go forward one day if it lands on a holiday (q for xgirl;
really)?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Lady Layla]

Not if it relates to payroll!!!!

<vbg>

: Shouldn't it go forward one day if it lands on a holiday (q for xgirl;
: really)?
:
: --
:
: HTH
:
: RP
: (remove nothere from the email address if mailing direct)
:
:
: : > This formula uses NETWORKDAYS to determine whether 8 days in the future
: falls
: > on a work day. If so, use that date; otherwise, start with the date+8 and
: back
: > up one workday (using the WORKDAY function to find that date).
: >
: >
: =IF(NETWORKDAYS(A1+8,A1+8,HolidayList)=1,A1+8,WORKDAY(A1+8,-1,HolidayList))
: >
: > If the formula gives you a #NAME! error, look at help for either function
: on
: > how to install the ATP.
: >
: >
: > On Thu, 31 Mar 2005 14:25:11 -0800, "xgirl"
: <[email protected]>
: > wrote:
: >
: > >Let me explain in more detail ... I have a date in A1. Based on that
: date I
: > >have to count ahead 8 calendar days to determine a due date. BUT ~ If
: that
: > >date falls on a Saturday or Sunday then I need the due date to fall back
: to
: > >Friday and if it lands on a holiday, I need it to fall back to the
: previous
: > >working day. Thanks to you both for your help, I've definitely learned
: from
: > >your comments.
: >
:
: