ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY

Question

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

https://docs.google.com/spreadsheets/d/1Zzzaxi66H3asCk7xJUOyFiIx0rcIcwKdD2RhwOdUpo0/edit#gid=0

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:

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