Can someone help with correct syntax please?

Hi All,

I'm back with another question :) I am attempting to write a function but I cannot get it to work, I am not sure where I am going wrong could you take a look at what I've written and tell me if it is possible for it to work and if it is what needs correcting please.

This is what I've got so far

=IF(AND([Daily Update Complete]@row ="Yes"),( [Weekly- Summary Complete]@row="Yes"),([Executive Summary]="Data Supplied"),([Last Report Date]@row < ([Todays Date]@row - 7),"Green", IF(OR([Last Report Date]@row>([Todays Date]-10),([Daily Update Complete]@row="No"),([Weekly Summary Complete]@row="No"),([Executive Summary]@row="Data Missing"),"Yellow","Red" )))))

Any help would be greatly appreciated

Best Answers

  • Mad
    Mad ✭✭
    Answer ✓

    Hi @Paul Newcome here's a screenshot and this is the function in the cell =IF(AND([Daily Update Complete]@row = "Yes", [Weekly Summary Complete]@row = "Yes", [Executive Summary]@row = "Data Supplied", [Last Report Date]@row <= [Todays Date]@row - 7), "Green", IF(OR([Last Report Date]@row > [Todays Date]@row - 10, [Daily Update Complete]@row = "No", [Weekly Summary Complete]@row = "No", [Executive Summary]@row = "Data Missing"), "Yellow", "Red"))

    After posting on Friday I used the reference data to learn and thought "I got this" and after a few hours of trying quickly realised "I haven't got this". Thank you for taking the time to help I hope the snippet provided is enough please tell me if you need more detail from the sheet

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    That would have been in my last formula where everything was all rolled into a single formula.

    In your most recent formula:

    =IF(AND([Last Report Date]@row >= TODAY(-7)), "Yes", IF(AND([Last Report Date]@row >= TODAY(-10)), "No)

    You do not need the AND statements and you forgot a quote after the "No".

    =IF([Last Report Date]@row >= TODAY(-7), "Yes", IF([Last Report Date]@row >= TODAY(-10), "No")

    This formula is saying...

    If the last report date is within the past 7 days then output "Yes". If the last report date is within the past 10 days then output "No". Anything that is more than 10 days old will be blank.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!