Switch ON/OFF with the IF function

Hi All,

Hope you're all doing fine. I am stuck on something that looked simple to me. I have personnel working away on a rotational scheme of 28/28 days. I need to control their stay on site to prevent excessive overstay, and request their travel bookings on time.

In my spreadsheet, I have Block "A" just to enter personnel names; Block "B" for entry and leave dates; and Block C is an already set-up control Block.

Now, where I get stuck is to correctly set the formula for ON/OFF switch with an IF function. Basically, once I enter an Entry Date under R column, I get all knock-on effects in columns U,V,W,X (Confirmation of Entry Date, Number of Days on site, Leave date, Return Date).

That way, if "V" is between 0 and 28, then Column "I" MUST show "ON", and SHOUD keep showing "ON" in case of "Overstay" (If the leave date is not entered into "S" column for confirmation). Note that the Leave Date may be different from the normal scheduled leave date; in this case column "I" should keep showing "ON" if that different leave date is still superior to TODAY (still not passed). But once it is passed, column "I" MUST then switch to "OFF".

I tried several formulas similar to this one =IF(W5;">"&TODAY();"ON";IF(AND(W5;">"&TODAY();S5;"<>""*");"ON";IF(S5;"<"&TODAY();"OFF";"OFF")) but nothing is working.

Please help!

Hey Mates,

I found it. This one works fine: =IF(AND(ISNUMBER(R5);ISBLANK(S5);V5>-1);"ON";IF(AND(ISNUMBER(R5);ISNUMBER(S5);S5>TODAY());"ON";IF(AND(ISNU
MBER(R5);ISNUMBER(S5);S5<TODAY());"OFF";"OFF"))).