Calculate Due Date

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.

Try this

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

--

HTH

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


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

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

"Bob Phillips" wrote:

> Try this
>
> =A1+8-(WEEKDAY(A1)>6)-(WEEKDAY(A1)>6)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "xgirl" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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)


"xgirl" <[email protected]> wrote in message
news:[email protected]...
> I get a VALUE error on that. I copied the formula and pasted it into the
> spreadsheet.
>
> "Bob Phillips" wrote:
>
> > Try this
> >
> > =A1+8-(WEEKDAY(A1)>6)-(WEEKDAY(A1)>6)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "xgirl" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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

"xgirl" wrote:

> I get a VALUE error on that. I copied the formula and pasted it into the
> spreadsheet.
>
> "Bob Phillips" wrote:
>
> > Try this
> >
> > =A1+8-(WEEKDAY(A1)>6)-(WEEKDAY(A1)>6)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "xgirl" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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

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)


"xgirl" <[email protected]> wrote in message
news:[email protected]...
> 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...
>
> "xgirl" wrote:
>
> > I get a VALUE error on that. I copied the formula and pasted it into
the
> > spreadsheet.
> >
> > "Bob Phillips" wrote:
> >
> > > Try this
> > >
> > > =A1+8-(WEEKDAY(A1)>6)-(WEEKDAY(A1)>6)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "xgirl" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >

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.

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.

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)


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

Not if it relates to payroll!!!!

<vbg>

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
: 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)
:
:
: "Myrna Larson" <[email protected]> wrote in message
: news:[email protected]...
: > 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.
: >
:
: