Formulas are a pain! I struggling to Use Smartsheet in my Job! No decent Training Courses

What I hate so much about SMARTSHEET....is FORMULAS!!!!!!!!! No decent YouTube examples or use-rguides on how to create a step-by-step. It's really off putti g as a user!

I am trying to convert the MS Excel formula below to Smartsheet and I am really getting annoyed!!!!!

"=IF(J142="","No Target Date",IF((J142-$J$1)>3,"Green",IF((J142-$J$1)>-1,"Amber","Red")))

The formula I provided is checking the value of cell J142. If J142 is blank or empty, then the formula will return the text "No Target Date". If J142 is not empty, the formula will perform additional calculations.

The formula calculates the difference between the value in J142 and the value in cell J1, and then compares that difference to some thresholds.

If the difference is greater than 3, the formula returns the text "Green".

If the difference is between -1 and 3 (inclusive), the formula returns the text "Amber".

If the difference is less than -1, the formula returns the text "Red".

So, overall, the formula is returning a traffic light status based on the difference between the value in J142 and the value in J1, with "Green" indicating that the difference is large, "Amber" indicating a moderate difference, and "Red" indicating a small or negative difference.

Please can someone provide the correct formula?

Regard

Rakesh Mistry

rakeshmistry@hotmail.com

+44 7930 520 813

Find more posts tagged with

Accepted answers

All comments

=Chris Palmer

Hello @rakmis,

The syntax is slightly different when specifying cells and columns from Excel to Smartsheet.

Excel requires the column letter, where Smartsheet requires [column name]@row

Example in Excel: J142

Example in Smartsheet: [Target Date]@row

Something like this may work:

=IF([Target Date]@row="","No Target Date",IF(([Target Date]@row-$[Target Date]$1)>3,"Green",IF(([Target Date]@row-$[Target Date]$1)>-1,"Amber","Red")))

rakmis

It has not work. I need to add =Today() in the formula to work.

Explain what this is trying is do??...I don't understand!!!! So fustrating...

Didn

=IF([Target Date]@row="","No Target Date",IF(([Target Date]@row-$[Target Date]$1)>3,"Green",IF(([Target Date]@row-$[Target Date]$1)>-1,"Amber","Red")))

I was expecting Smartsheet to be code free. There are other tools out there that require no programming. I had to refer to ChatGPT for above code....AI also struggled!

Smartsheet...did not think about creating a full training course which starts from scratch to advance? No proper user guides with examples on-line! Not even proper training videos with Beginner/Intermediate/Advance.

I have to go to YouTube and the videos are either small snippets or something not related what you trying to search on.

No official Training Courses running in the UK for those who wish master Smartsheet and become an SME!!!!!

Andrée Starå

Hi @rakmis

I hope you're well and safe!

Here are some excellent resources to train yourself or have someone coach you.

https://help.smartsheet.com/help

https://smartu.smartsheet.com/onboarding-video-series

https://smartu.smartsheet.com/page/purchase-instructor-led-training

https://smartu.smartsheet.com/page/purchase-elearning

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @rakmis

In addition to @Andrée Starå's links, you may also want to check out the free webinars on Formulas, here:

https://help.smartsheet.com/learning-track/best-practice-webinars/formula-foundations

For your specific question, can you explain what didn't work about the formula above? The only thing I see that may be wrong is using "Amber" instead of "Yellow" to make the status symbol balls appear in the correct colour:

I'd be happy to explain what the formula is doing. The easiest way to do this is to break it out by each individual IF statement:

=IF([Target Date]@row = "", "No Target Date",

IF(([Target Date]@row - $[Target Date]$1) > 3, "Green",

IF(([Target Date]@row - $[Target Date]$1) > -1, "Yellow",

"Red")))

Lets take a look at the first statement:

=IF([Target Date]@row = "", "No Target Date",

This says, if the Target Date in this current row (or @row) is blank (indicated by ""), then return the text "No Target Date".

Then we have a comma at the end to ask the question, but what if the target date in this row is not blank? In that case, we move on to your second statement:

IF(([Target Date]@row - $[Target Date]$1) > 3, "Green",

Take the date in this current row and subtract the Target Date in the top row (indicated by the $1 after the column name). If the result of this subtraction is greater than 3, meaning that the current row's date is more than 3 days in the future, then return Green.

But, if the subtraction is less than or equal to 3 days in the future, the formula moves on to the next statement:

IF(([Target Date]@row - $[Target Date]$1) > -1, "Yellow",

If you take the date in the current row and subtract the date in the top row and it's both less than 3 (because the formula already evaluated this in the previous statement) and it is greater than -1, meaning that the date in this row is either one day before OR up to 3 days after the date in the top row, then turn Yellow.

Otherwise, if none of the above statements are true... meaning that the date in the current row is more than 1 day in the past compared to the top date, then return a Red status ball.

"Red")))

Are you wanting the formula to do something else? How are you wanting Today to be evaluated?

Cheers,

Genevieve

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.