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

#### guybrown

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### West Man

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

#### taigovinda

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

Excel Workbook
ABC
1StartPlusEquals
2Fri Aug 204Sun Aug 29
3Sat Aug 214Sat Sep 4
4Sun Aug 224Sun Sep 5
5Mon Aug 234Sun Sep 5
6Fri Aug 203Sat Aug 28
7Sat Aug 213Sun Aug 29
8Sun Aug 223Sat Sep 4
9Mon Aug 233Sat Sep 4
Sheet1
Cell Formulas
RangeFormula
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...

Last edited:

#### guybrown

##### Board Regular

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.

Last edited:

#### West Man

##### Well-known Member
(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

##### MrExcel MVP
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")

Replies
6
Views
393
Replies
2
Views
70
Replies
12
Views
2K
Replies
1
Views
286
Replies
5
Views
571

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,981
Messages
5,767,435
Members
425,413
Latest member
ccfam04

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back