After some unsuccessfull workings hours, I definitely need your support.

I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in specific columns.

For each new row, I would like to put an automatic fill of 3 "candidate status" in a specific column based on 4 cases:

Status 1: « Candidate onboarded » if cell (Target Date) from a specific column (F3:F) is not empty AND target start date - entered manually afterwards - is earlier than TODAY

Status 2: « Candidate identified » if cell from another column (C3:C) is not empty AND targeted start date (F3:F) (already entered manually) - is later than today OR BLANK (because the targeted start date is unknown)

Status 3: « Open » if cell from column (C5:C) is empty

I have tried following formula, that is not working for the status "Candidate onbarded".

=ARRAYFORMULA(IF(AND(F3:F10<>"",F3:F10<=TODAY()),"Candidate onboarded",IF(C3:C10<>"","Candidate identified","Open")))

Do you have an idea how I can solve this problem? Many thanks in advance for your support! Best regards Pierre

enter image description here


1 Answer 1


the arrayformula() function and the and() function don't work when coupled... you have to find another way to evaluate the and... and luckily you just need to do a multiplication. Look at the tab "Daniele-Solution" using this formula:

            if((F3:F7<>"")*(F3:F7<=today());"Candidate onboarded";
               if(C3:C7<>"";"Candidate identified";"Open")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.