Formula to get Week offs

[sakmsb]

Hello All,

I have a table like attached.

Put in the if loop to get the week offs, wanted to know if there is any other formula that would cut down the length of the already written formula.

=IF(AND(A2="",B2=""),$A$1&","&$B$1,IF(AND(B2="",C2=""),$B$1&","&$C$1,IF(AND(A2="",G2=""),$A$1&","&$G$1,IF(AND(C2="",D2=""),$C$1&","&$D$1,IF(AND(D2="",E2=""),$D$1&","&$E$1,IF(AND(E2="",F2=""),$E$1&","&$F$1,IF(AND(F2="",G2=""),$F$1&","&$G$1)))))))

Thanks!Weekoffs.xlsx

[newdoverman]

This is a little bit shorter but maybe easier to understand at a glance.

Please Login or Register  to view this content.

The enclosed file has a very short formula if you use the number of the day attended instead of just 1's. Sun =1, Mon=2, Tue=3 etc.

Please Login or Register  to view this content.

[AlKey]

Try this array formula

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[sakmsb]

that was awesome trick newdoverman, replacing with numbers...

thanks...

[AlKey]

Thank you for the feedback!

[newdoverman]

Thanks for the feedback.