Is there a "workday" opposite such as "weekendday"?

[guybrown]

Hi all

I've looked all over the internet but can't find what I'm looking for.

How workday works:
=Workday(A1,3) returns the date of the 3rd workday after the date in A1.

What I need:
How can I get the date of the 3rd weekend day?
it might look something like this:
=weekendday(A1,3)

eg:
If A1 contains Friday 20/08/2010, and I want the 3rd weekend day, I would get Saturday 28/08/2010.

Anyone? Much appreciated.

Guy
excel 2007 xp professional

 

[West Man]

Try =A1-WEEKDAY(A1)+21
Disregard, I did not understand your needs

 

[West Man]

How about: =A1-WEEKDAY(A1)+15-(WEEKDAY(A1)<>7)

 

[taigovinda]

Probably there's a better way (which I'd love to see), but this seems to work:

Excel Workbook
	A	B	C
1	Start	Plus	Equals
2	Fri Aug 20	4	Sun Aug 29
3	Sat Aug 21	4	Sat Sep 4
4	Sun Aug 22	4	Sun Sep 5
5	Mon Aug 23	4	Sun Sep 5
6	Fri Aug 20	3	Sat Aug 28
7	Sat Aug 21	3	Sun Aug 29
8	Sun Aug 22	3	Sat Sep 4
9	Mon Aug 23	3	Sat Sep 4
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B2=0,"N/A",A2+7-WEEKDAY(A2)+7*ROUNDDOWN((B2-1)/2,0)+IF(AND(ISEVEN(B2),WEEKDAY(A2)=7),7,IF(OR(ISEVEN(B2),WEEKDAY(A2)=7),1,0)))

West Man your formula looks nicer but doesn't make the three days variable...

 

[guybrown]

How about: =A1-WEEKDAY(A1)+15-(WEEKDAY(A1)<>7)

thanks for all responses.
i'm liking this one...
i understand the start of the formula but not the end <> bit.
can you explain?

i can see that you're taking the date number, taking away the weekday number which takes you to the previous saturday, you add 15 days (2 weeks and a day, which takes me to 2nd sunday after my original date but then i don't get the <> bit. i'm guessing that it solves a probelm caused as a result of the original date being a saturday... but how does the <> work/operate in the -(weekday(a1)<>) bit.

cheers again.

 

[West Man]

(WEEKDAY(A1)<>7) will return true if A1 is a Saturday. Because of the - sign in front, true gets converted to 1 and false gets converted to zero. The adjustment is then made based on whether A1 is Saturday or not.

 

[barry houdini]

Do you always want to add 3 "weekenddays"? If not then, as taigovinda says, West Man's formula is not easily "extensible". If your start date is in A2 and weekenddays to add in B2 you could use this formula

=A2-WEEKDAY(A2,2)+(MAX(0,WEEKDAY(A2,2)-5)+B2)*3.5+MOD(MAX(0,WEEKDAY(A2,2)-5)+B2,2)*2.5

I believe that will give the same results as taigovinda's suggested formula as long as B2 is a positive number.

By the way, if you upgrade to Excel 2010 there's a WORKDAY.INTL function that can handle this more easily, e.g.

=WORKDAY.INTL(A2,B2,"1111100")