Forum Discussion
Formula 4 week rotation excluding Fridays and weekends
I hope below solution will resolve you issue for 4 weeks rotation...
Week Number Formula
=INT((DAY(B1)-1)/7)+1Weekday Number (Mon=1)
=WEEKDAY(B1,2)Employee A WFH
=IF(AND(B3<6,CHOOSE(B2,1,2,3,4,1)=B3),"W","")Employee B WFH
=IF(AND(B3<6,CHOOSE(B2,2,3,4,1,2)=B3),"W","")If this resolves your query don't forget Mark as Solution.
There are a few questions I have. Is this supposed to be continuous so after week 5 on a monday if week 6 is a new month that should be a tuesday or should it get reset back to monday? If it is reset then what constitutes the first week of a new month? the 1st day of the month based on a sun-sat week? the first full week based on sun-sat? the first full week based on mon-sun? etc...
my guess is you want a continuous running pattern but you still need a 'start date' to base it on. I have both Monday and Tuesday continuous and by the month options in the attached.
the continuous Monday formula is:
=IF(WEEKDAY($A$5#,2)=MOD(INT(($A$5#-$B$1+WEEKDAY($B$1))/7),4)+1,"W","")where the '2' in WEEKDAY($A$5#,2) is defining MONDAY as 1 so changing that to 3 will define TUESDAY
$A$5# is the series of dates it is checking
$B$1 is the 'start date' to base the start of the sequence
thank you, I think I didn't explain myself. This is what I'm looking for.
The C/F rotation days is fine. =IF(AND(WEEKDAY(DATE(2025,7,D$54),2)<=4, WEEKDAY(DATE(2025,7,D$54),3)=MOD(INT((D$54-1)/7),4)+2), "W", IF(WEEKDAY(DATE(2025,7,D$54),2)<=5, IF(MOD(D$54,2)=1, "C", "F"), ""))
but the problem is the rotation of Working from Home (W)
