Roster - 6 week om 2 weeks off Formula

[Steves73]

Hi All

I'm having trouble with a formula, as you can see below, Row 3 (Starting at CM3) has a formula that calculates a 41 day on 14 day off roster and formats automatically, Formula below


=IF($A3="","",IF(CM$1>=$C3,VLOOKUP(MOD(CM$1-$C3,$A3+$B3),{0,"W";41,"O"},2),""))

What I am trying to do is add in Travel Days (T) at the start of the 41 days (W), end of the 41 Days and the end of the off days (O) - I have manually entered how I need it to look in Row 5

Is anyone able to assist with modifying the above formula

Hope there ins enough information, I can upload the spread sheet if needed

Regards

Steve

 

[Tetra201]

Your manually entered values do not match the description, so here is my guess -- adjust the 0,1,$A3,$A3+1 part as needed:

=IF($A3="","",IF(CM$1>=$C3,LOOKUP(MOD(CM$1-$C3,$A3+$B3),CHOOSE({1,2,3,4},0,1,$A3,$A3+1),{"T","W","T","O"}),""))

 

[Steves73]

Your manually entered values do not match the description, so here is my guess -- adjust the 0,1,$A3,$A3+1 part as needed:

=IF($A3="","",IF(CM$1>=$C3,LOOKUP(MOD(CM$1-$C3,$A3+$B3),CHOOSE({1,2,3,4},0,1,$A3,$A3+1),{"T","W","T","O"}),""))

Your guess was right on the mark, apologies on the incorrect manual description, but you got it. Thank you very much

 

[Tetra201]

Glad to have helped.