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

guybrown

Board Regular
Joined
Jul 2, 2008
Messages
100
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
Joined
Mar 27, 2006
Messages
1,175
Try =A1-WEEKDAY(A1)+21
Disregard, I did not understand your needs
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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
Joined
Jul 2, 2008
Messages
100

ADVERTISEMENT

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.
 
Last edited:

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
(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
Joined
Mar 23, 2005
Messages
20,825
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")
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top