Forum Discussion

mp_castel's avatar
mp_castel
Copper Contributor
Jun 06, 2025

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 ...
admin
excel
Formulas and Functions
training
m_tarler's avatar
m_tarler
Bronze Contributor
Jun 07, 2025

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 

 

  • mp_castel's avatar
    mp_castel
    Copper Contributor
    Jun 10, 2025

    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)

     

     

Resources