IF Formula Date

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
    MichaelTCA ✭✭✭✭✭✭


    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.

  • 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
    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
    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"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!