IF Formula Date

Nicolas Roucher

I would like to use a formula to indicate automatically the status of the line.

If the condition is ok --> "Late"

if not --> "In Progress"

I tried with this formula but it doesn't work, could you please help me?

=IF(AND([DATE OBJECTIF ANALYSE]@row > TODAY(); [STATUT DELAI ANALYSE]@row = ""; "Late"; "In Progress"))

MichaelTCA

Hello,

If you want both criteria to result in "Late" as a result, you will need a close ) for the AND function. Bolded below:

=IF(AND([DATE OBJECTIF ANALYSE]@row > TODAY(); [STATUT DELAI ANALYSE]@row = ""); "Late"; "In Progress")

Or a nested IF statement if you want the criteria split up for each result:

=IF([DATE OBJECTIF ANALYSE]@row > TODAY(); "Late"; IF([STATUT DELAI ANALYSE]@row = "";"In Progress";"X"))

The "X" is an ambiguous variable that means nothing except it's the FALSE section of this function, within this example. You can use this spot for "On-Time", or another term for when all conditions prior fail.

If you have large nested IF statements, it's a good idea to try and group columns with the same results and use AND/OR functions.

Nicolas Roucher

Thank you for your feedback but it doesn't work, there is "#circular refererence" which appear if I use the first the formulas. Do you have an idea?

Christian Graf

Hi @Nicolas Roucher

It looks like you are trying to reference the same column you are trying to put the status in. Take that out so that you don't have a circular reference.

=IF([DATE OBJECTIF ANALYSE]@row > TODAY(), "Late", "In Progress")

Hope this helps!

MichaelTCA

Christian is right.

Sorry, I didn't notice you were analyzing the same column that you're entering a function into.

If you need another status column to compare to, you can create a helper column and reference that column within this function.

=IF(AND([DATE OBJECTIF ANALYSE]@row > TODAY(); [STATUS HELPER COLUMN]@row = ""; "Late"; "In Progress"))