Need to calculate 10 weekends every year.

**eeps24** · 09-05-2018, 10:06 AM

I have a silly question and not sure if it relates to excel 100%.

Say a new employee starts work on 09/19/18 (Monday - Friday) but this person has to do 10 weekends a year (09/19/18 - 09/18/20), how would I schedule this? For example, 52 weeks / 10 weekends comes to 5.2 weeks. So I figured every 5 weeks they have to do a weekend which works but as I carry this over year to year and it doesn't balance out. In excel I was manually doing this, year to year this person may end up doing 11 weekends a year (calendar days shifting). So I guess I am close saying every 5 weeks they will do a weekend but I dont think its the perfect answer.

Year 1 and 2 seem fine, but then year 3 ends up being 11 weekends.

year.PNG

Any one have any ideas? or formulas?

Thank you

**popipipo** · 09-05-2018, 10:41 AM

If you have 5 employees having a contract of 10 weekends a year then you become 2 weekend with no employee

Don't give employees a contract of 10 weekends a year but contract of once a 5 weeks (average).

**popipipo** · 09-05-2018, 10:43 AM

if this is not possible then maybe this will help

**eeps24** · 09-05-2018, 10:45 AM

this contract is only for 1 employee and the terms are just 10 weekends a year, no more and no less. It has to be 10 weekends a year. I am thinking a solution could be, after every 9/19 just count every 5. So it wouldnt be a continues 5 every year but once 9/19 comes around, recount every 5 weeks. But I am not sure if that makes entire sense to do.

*sorry popipipo we posted the same time. I will reveiw your post.

**eeps24** · 09-05-2018, 11:09 AM

yes your sheet does help, but I am trying to understand what you did to get that. Are you saying every year I follow your formula? Can you please explain to me what its exactly doing?

Thank you!

**Kaper** · 09-05-2018, 11:17 AM

A bit more complex (but no manual adjustment needed) universal formula for B3, and copy all the way down.

=B$1+ROUND((365.25*(ROWS($3:3)-0.5)/10),0)-WEEKDAY(B$1+ROUND((365.25*(ROWS($3:3)-0.5)/10),0))

I tested down to 100+ rows - it's year 2118 (100 years away) and always gives nicely distributed 10 Saturdays (note distance between 31-Aug-2019 05-Oct-2019 and other pairs near work anniversary):

06-Oct-2018
10-Nov-2018
15-Dec-2018
19-Jan-2019
23-Feb-2019
06-Apr-2019
11-May-2019
15-Jun-2019
20-Jul-2019
31-Aug-2019
05-Oct-2019
09-Nov-2019
14-Dec-2019
18-Jan-2020
29-Feb-2020
04-Apr-2020
09-May-2020
13-Jun-2020
25-Jul-2020
29-Aug-2020
03-Oct-2020
07-Nov-2020
12-Dec-2020
23-Jan-2021
27-Feb-2021
03-Apr-2021
08-May-2021
12-Jun-2021
24-Jul-2021
28-Aug-2021
02-Oct-2021
06-Nov-2021
18-Dec-2021
22-Jan-2022
26-Feb-2022
02-Apr-2022
07-May-2022
18-Jun-2022
23-Jul-2022
27-Aug-2022
...
01-Oct-2118
12-Nov-2118
17-Dec-2118
21-Jan-2119
25-Feb-2119
08-Apr-2119
13-May-2119
17-Jun-2119
22-Jul-2119
26-Aug-2119

**Kaper** · 09-05-2018, 11:28 AM

And the final one, which lists both Saturdays and Sundays.
B3 and copy down:

Formula:

Please Login or Register  to view this content.

It's basically the same as previous one on odd rows (divided by 20, not 10 - aws we will use it on every second row).
But on even rows, it uses just cell above (Saturday) value +1 to get Sunday date

**popipipo** · 09-05-2018, 11:44 AM

Every year a start the counting at 09/19
The 5 times every 5 weeks (the 35 in formula) the 1 times 6 weeks and then 5 times 5 weeks

Maybe this file with other formulas you understand better

**eeps24** · 09-14-2018, 09:02 AM

thank you both, I think all these suggestions work. I will take your examples and test.

Need to calculate 10 weekends every year.

LinkBack

Thread Tools

Rate This Thread

Display

Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Re: Need to calculate 10 weekends every year.

Thread Information

Users Browsing this Thread

Similar Threads

calculate dates excluding weekends

calculate dates excluding weekends

calculate dates excluding weekends

need to calculate max excluding the weekends

Using advanced filter to calculate a 3 year and 5 year history

formula to calculate months in a specific year given a multi-year range

formula - calculate the yearly cost each year of the 10 year contract

Bookmarks

Bookmarks

Posting Permissions