Forum Discussion
Formula 4 week rotation excluding Fridays and weekends
Hi. I'm creating an employee work calendar. I need to formulas. One i have it but the other one i cannot figure it out. Employee A works from home, called W, one day a week. Week 1 on a monday, week 2 on a tuesday, week 3 on a wednesday, week 4 on a thursday, week 5 on a monday and so on. Fridays not available and saturdays and sunday are weekends. My formula only works if the first of the month is a monday. So i cannot do another month plus i cannot figure it out for employee B when the working from home is a Tuesday for week 1, can someone tell me what i'm doing wrong?
2 Replies
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
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.
