Calculate Due Date

X

xgirl

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

Bob Phillips

Try this

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

--

HTH

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

xgirl

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

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

xgirl

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

Daniel.M

xgirl said:
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...
Click to expand...

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

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