IF functions with weekdays

[jpneeson]

Hi guys. I have a Rota sheet that I'm doing some work on. I have an if function in to retrieve the data from the master Rota for each user if they are in that day. The issues I'm having is the coloum beside it.

I want to put a formula in that says for example if b4 is not empty weekday is Monday to Friday 17:00 - 09:00

I have the weekday part worked out I just need this formula to only kick in if the previous Colum is not blank.

Thanks in advance if you can help.

 

[mrshl9898]

Like this?

=IF(B4<>"",

 

[jpneeson]

This is the current formula i am using. If i try =IF(B4<>"", i get the error to say to many arguments.

=IF(WEEKDAY(A4)=2,"17:00",IF(WEEKDAY(A4)=3,"17:00",IF(WEEKDAY(A4)=4,"17:00",IF(WEEKDAY(A4)=5,"17:00",IF(WEEKDAY(A4)=6,"17:00",IF(WEEKDAY(A4)=1,"09:00",IF(WEEKDAY(A4)=7,"09:00","")))))))

 

[mrshl9898]

How about, IF B4 is blank then nothing else:

You have 5 scenarios for 17:00 and 2 for 09:00, change them to ORs.

There is a limit to the number of IFs you can do. 7 I believe, hence the error.

=IF(B4="","",IF(OR(WEEKDAY(A4)=2,WEEKDAY(A4)=3,WEEKDAY(A4)=4,WEEKDAY(A4)=5,WEEKDAY(A4)=6),"17:00",IF(OR(WEEKDAY(A4)=1,WEEKDAY(A4)=7),"09:00","")))

Further simplified, if blank then blank, if 2-6 then 17:00 else 09:00

=IF(B4="","",IF(OR(WEEKDAY(A4)=2,WEEKDAY(A4)=3,WEEKDAY(A4)=4,WEEKDAY(A4)=5,WEEKDAY(A4)=6),"17:00","09:00"))

 

[jpneeson]

That worked perfectly. Thank you so much for your help.

 

[Michael M]

Also depended on your Excel versio....2010 onwards will take 64 IFs.....not that you would want to do that...

 

[Tetra201]

@ mrshl9898:

Further simplified:

=IF(B4="","",IF(OR(WEEKDAY(A4)=1,WEEKDAY(A4)=7),"09:00","17:00"))

Even further simplified:

=IF(B4="","",IF(OR(WEEKDAY(A4)={1,7}),"09:00","17:00"))

etc.