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