Google Spreadsheets - IF with more that three arguments

Question

I'm currently working an a very simple project management template. The Idea is that after entering a start and end-date columns will show up as a very simplistic gantt chart (conditional formating).

Everything works fine, until "year" comes into consideration. Since it is a several years lasting project and we work with calenderweeks it is essential that the work package is only visualized in its specific year.

So I came up with the following (rough'n'dirty) formular (example from cell K3): =and(if(K$2>=$F3;1;0);if(K$2<=$H3;1;0);if(or(right($E3;4)=K$1;1);(right(G3;4)=K1;1;0)))

This is the document: https://docs.google.com/spreadsheets/d/15F1uBnoHMuJqc_w0X04U5-ZCQ_6mgO_HJqvN5U28cog/edit?usp=sharing

Problem: GoogleSpreadsheets only alows three arguments with IF. But I do not know how to structure it otherwise...

Thankful for any suggestion!

Cheers, Matt

Answer 1

This might be what you are looking for, ties the start week/ end week and year together for comparison.

  =IF(AND(
(K$1&TEXT(K$2;"00")) >=(RIGHT($E$3;4)&TEXT($F$3;"00"));
(K$1&TEXT(K$2;"00")) <=(RIGHT($G$3;4)&TEXT($H$3;"00")))
;1;0)