Calculating weekend dates

[IGNACIO]

I have events that are going to repeat x times in an exact day of the week every week. I need to calculate the date of the final event. I
have done a formula using workday function when the events are going to repeat from Monday to Friday, with the advantage of
calculating some holidays. I don’t know how to do the same for the weekend days. Example: I have a Saturday and Sunday event,
starting on 12-26-2010. The numbers of events: 45. When is the day of the final event?

 

[barry houdini]

Which version of Excel do you have? With Excel 2010 you can use WORKDAY.INTL function like this

=WORKDAY.INTL(A1-1,45,"1111100")

....in earlier Excel versions it'll be a little more complicated....

 

[IGNACIO]

I have Excel 2008 for Mac

 

[barry houdini]

If you have the start date in A1 and the number of days (45 in your example) in B1 then you can use this formula to get the end date

=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10))-1,2)>5,ROW(INDIRECT("1:"&B1*10))),B1)+A1-1

confirmed with CTRL+SHIFT+ENTER

I assume you don't need to exclude holidays if you are only considering weekend dates but you may need to exclude Christmas Day or similar......?

 

[IGNACIO]

What I´doing for the weekday calculation is:

=WORKDAY(A10,B10-1,F2:F8)

A10 is the start date, B10 is the number of events and F" to F8 are the hollidays.
<style>table { }.font5 { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { text-align: center; }.xl25 { font-weight: 700; text-align: center; }.xl26 { text-align: center; }.xl27 { text-align: center; }ruby { }rt { color: windowtext; font-size: 8pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; display: none; }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="237"> <col span="2" width="75"> <col style="" width="87"> <tbody><tr height="13"> <td class="xl25" height="13" width="75">START</td> <td class="xl25" width="75">EVENTS</td> <td class="xl25" width="87">END</td> </tr> <tr height="13"> <td class="xl26" height="13">28-Jun-10</td> <td class="xl27">140</td> <td class="xl24">11-Jan-11</td> </tr> </tbody></table>
I have been trying with your formula, but returns a #NUM!

Thank you very much!!!!

 

[barry houdini]

I have been trying with your formula, but returns a #NUM!

That formula is an "array formula". Put the formula in the cell then press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER.

That should make curly braces like { and } appear around the formula in the formula bar......and you should get a valid date (if it shows as a number then format as a date)

 

[Dave Patton]

Is your latest comment dealing with a different question; it uses =WORKDAY.

=WORKDAY(A10,B10-1,F2:F8) yields the number of weekdays excluding the current date and holidays.

For your original question, consider arithmetic

Cell A1 Sun Dec 26, 2010
45th =A1+(45-1)*7/2

To list the dates

A2 =IF(WEEKDAY(A1,2)=7,A1+6,IF(WEEKDAY(A1,2)=6,A1+1))

copy down


I believe array formuals with some or all Mac versions use different keystrokes Press "COMMAND+RETURN on the Mac) ".

 

[barry houdini]

I believe array formuals with some or all Mac versions use different keystrokes Press "COMMAND+RETURN on the Mac) ".

Thanks Dave

 

[IGNACIO]

THANK YOU BARRY AND DAVE. I´M USING BARRY´S FORMULA WITH THE DAVE´S HINT AND IS WORKING PERFECTLY.
I have two questions:
1- How can I include a list od holidays days for the weekend formula?
2- In the workday formula I have add a cell without the subtraction of one day in order to know the first day available I have. How can I do the same for the weekends formula?

THANK YOU AGAIN AND HAPPY HOLLIDAYS

IGNACIO