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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You have a lot of parenthesis that are not needed and I see a cell reference that didn't have a row indicator on it (number or "@row"). Lets start with cleaning them up to see if that helps any...

    =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]@row - 10, [Daily Update Complete]@row = "No", [Weekly Summary Complete]@row = "No", [Executive Summary]@row = "Data Missing"), "Yellow", "Red"))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mad
    Mad ✭✭

    @Paul Newcome You are a wizard I am now going to compare your answer with my question to see where I went wrong and see if your correction works on my sheet . Thank you for your help mate I really appreciate it

  • Mad
    Mad ✭✭

    I can see what you meant about my excessive parenthesis use and I missed my [Executive Summary]@row but it still doesn't work. I feel a bit stupid having to ask but everyone's got to learn I suppose, thank you for trying to help me @Paul Newcome

  • Mad
    Mad ✭✭

    @Paul Newcome I noticed there was a dash [Weekly- Summary Complete] I removed that and the formula now partly works, I have made sure all the requirements are met for green however I can only return a yellow status

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. Without seeing the sheet I was unable to confirm column names were spelled correctly. Glad you were able to figure that bit out.


    As for the Green not working... You are using an AND function which means all of the following must be true at the same time:

    [Daily Update Complete]@row = "Yes"

    [Weekly Summary Complete]@row = "Yes"

    [Executive Summary] = "Data Supplied"

    [Last Report Date]@row < [Todays Date]@row - 7


    If you can confirm that you have a row that is true for all points and it is still not working, are you able to provide a screenshot?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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

  • Mad
    Mad ✭✭

    I thought I could try

    =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(AND([Last Report Date]@row > [Todays Date]@row - 10, [Daily Update Complete]@row = "No", [Weekly Summary Complete]@row = "No", [Executive Summary]@row = "Data Missing"), "Red", "Yellow"))

    But I'm still only returning Yellow

  • Mad
    Mad ✭✭

    I've cracked it

    =IF(AND([Daily Update Complete]@row = "Yes", [Weekly Summary Complete]@row = "Yes", [Executive Summary]@row = "Data Supplied"), "Green", IF(AND([Daily Update Complete]@row = "No", [Weekly Summary Complete]@row = "No", [Executive Summary]@row = "Data Missing"), "Red", "Yellow"))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The issue is the date portion in the AND statement. If [Last Report Date] equals [Todays Date] then it is not less than [Todays Date] - 7.


    Less than [Todays Date] - 7 is basically saying (assuming [Todays Date] is updated daily to reflect today's actual date) that the [Last Report Date] must be more than 7 days in the past.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mad
    Mad ✭✭

    @Paul Newcome I have a todays date column using =TODAY and was trying to reference that column so if the date was within 7 days of todays date it would be green if it was over 10 days it would be yellow and over 10 days would be red.

    I couldn't quite work out the correct way of writing it I believe <= is less than or equal to and I thought I had the formula correct which wasn't the case so I removed it from the formula. I would love to have it in there if I could I just can't work out how to do it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Green would be

    [Last Report Date]@row >= TODAY(-7)


    Then the Yellow IF would come next with

    [Last Report Date]@row >= TODAY(-10)


    Then you would leave Red as everything else.


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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mad
    Mad ✭✭

    @Paul Newcome with your corrections I am only returning "Green" or "Yellow" but not "Red" I think I may have to rethink the the outcome without the date and write an =IF(AND formula to return "Yes" or "No" in the [Last Report Date] column. It only seems to cause an issue when using <=TODAY(-#) and I don't understand the syntax enough yet to work it out. You are a wizard with this and I really appreciate you helping me I am definitely learning from the answers to these questions.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You shouldn't be using <=TODAY(-#). You should be using >=TODAY(-#). Greater than as opposed to less than.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mad
    Mad ✭✭

    Thank you Paul I must be getting on your nerves by now! I've got

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

    For the [Last Report] to return "Yes" or "No" but it's #UNPARSEABLE

  • Mad
    Mad ✭✭
    edited 05/24/22

    Just seen it I missed a " on "No"

    Also realised I can use;

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

    To simplify even more

    @Paul Newcome Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!