Start List On First Friday Of The Month

[Mr. Rick]

Hello,

I have manually created a list of dates in Column E and a list of employees next to the dates in Column E to show which days they work. Currently there are five employees, named in cells A2:A6, and the first employee listed starts on the first Friday of January and works 7 days, on the second Friday the second employee starts and works for 7 days and the pattern repeats itself down throughout the year.

I used the CEILING formula in E7 to determine the first Friday of the month (and year as the dates start in January). What I am looking for is for a formula in F7 that pulls in the first employee in the list and then something to fill out the rest of column F to list the employees names every 7 days, hopefully this formula is also smart enough to recognize a change in the number of employees if so happens in future years. Thanks for the help!

Example of what I currently have:

Friday,January 1,2021 Bob
Saturday,January 2,2021 Bob
Sunday,January 3,2021 Bob
Monday,January 4,2021 Bob
Tuesday,January 5,2021 Bob
Wednesday,January 6,2021 Bob
Thursday,January 7,2021 Bob
Friday,January 8,2021 Joe
Saturday,January 9,2021 Joe
Sunday,January 10,2021 Joe
Monday,January 11,2021 Joe
Tuesday,January 12,2021 Joe
Wednesday,January 13,2021 Joe
Thursday,January 14,2021 Joe
Friday,January 15,2021 Bill
Saturday,January 16,2021 Bill
Sunday,January 17,2021 Bill
Monday,January 18,2021 Bill
Tuesday,January 19,2021 Bill
Wednesday,January 20,2021 Bill
Thursday,January 21,2021 Bill

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[AliGW]

Questions:

1. If the number of employees changes, do they continue to work in a 7-day pattern, or does the number of days on also need to reflect the number of employees?
2. Do you always want to find the first Friday of the year? This will find the first Friday after and including the date in A1: =WORKDAY.INTL(A1-1,1,"1111011")

[Mr. Rick]

Thanks for the warm welcome!

1. Yes, the schedule for these employees will always be Friday - Thursday in a 7 day pattern. If there are less then 5 employees they would just cycle through faster.

2. I created my current list as I am using a VLOOKUP function on a different sheet to look through this list and pull in who is working on which day to fill out a monthly calendar. It's necessary for this list to have at least January 1st - December 31st, and the employees starting on Fridays and ending on Thursday.
In absence of knowing of a formula to use, my work around for determining when the 1st employee listed starts on the first Friday of the year was by leaving myself room as the first Friday of the year might be on a day from the 1st to the 7th, which is why I am using a formula (changed it to the one you provided ) in E7 as a starting point. From this reference date the other dates cascade up and down to fill out the rest of the year. Using this setup I know E7 is the first Friday of the year, so in F7:F13 I used an absolute cell reference to A2 as I know that's the first employee. I copied this pattern to fill out the rest of the calendar.

What I have works pretty good, and if employees change it wouldn't take much work to adjust this calendar, but I feel that there is a better way. Currently if we add or subtract employees I would have to change all of the cell references.

[AliGW]

Can I just check that you are still using Excel 2013 and not something newer?

[JohnTopley]

See attached

in E1

=INDEX($A$2:$A$21,MOD(INT((ROWS($1:2)-1)/7),COUNTA($A$2:$A$21))+1)

The count (ROWS($1:2)) was set to 2 as "Jill" was shown for 6 days initially.

If the Employee list changes, then all results prior to the change will be invalid (as COUNTA is used as the counter and hence it will automatically adjust to the new situation from "day 1")

It may be possible to get round this by using COUNTIFS and "Date Effective" for each Employee. Not tried and hence not tested!

[bebo021999]

Start in E1:
=WORKDAY.INTL(A1-1,1,"1111011")-7
From E2:=E1+1
F1:

Please Login or Register  to view this content.

Drag down

[AliGW]

In F1 copied down:

=INDEX($A$2:$A$20,IF(E1<$A$1,COUNTA($A$2:$A$20),IF(MOD(NETWORKDAYS.INTL(E$1,E1,"1111011"),COUNTA($A$2:$A$20))=0,COUNTA($A$2:$A$20),MOD(NETWORKDAYS.INTL(E$1,E1,"1111011"),COUNTA($A$2:$A$20)))))

or:

=INDEX($A$2:$A$20,IF(OR(E1<$A$1,MOD(NETWORKDAYS.INTL(E$1,E1,"1111011"),COUNTA($A$2:$A$20))=0),COUNTA($A$2:$A$20),MOD(NETWORKDAYS.INTL(E$1,E1,"1111011"),COUNTA($A$2:$A$20))))

[Mr. Rick]

Perfect, does exactly what I need it to do. Thanks for your help AliGW!

[AliGW]

Thanks for the comments and rep, however please don't ignore other contributors to your thread - acknowledge all solutions offered. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Mr. Rick]

Thanks for your help as well bebo021999 and JohnTopley!