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

Find more posts tagged with

Accepted answers

Mad

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

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.

All comments

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

Mad

https://community.smartsheet.com/discussion/comment/330642#Comment_330642

@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

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

@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

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?

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

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

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.

Mad

https://community.smartsheet.com/discussion/comment/330668#Comment_330668

@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

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

Mad

https://community.smartsheet.com/discussion/comment/330706#Comment_330706

@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

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

Mad

https://community.smartsheet.com/discussion/comment/330811#Comment_330811

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.