Smartsheet formulas

Hi,

Can anyone suggest me with a formula to carry out the following function:

- If RISK(H/M/L) CAT (0,1,2,3) column states "2" then Start on site date column needs to insert a date 12 weeks prior to the design required date. If RISK(H/M/L) CAT (0,1,2,3) column states "1", then Start on site date column needs to insert a date 4 weeks prior to the design required date.

This is required to be one formula. Please see attached image of layout of my Smartsheet.

temporary works register.PNG

Find more posts tagged with

Formulas

Comments

Paul Newcome

Give this a shot...

=IF(ISDATE([Design Required By]@row), [Design Required By]@row - IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2, 84, 28)

sahilhq

Sorry that doesn't work. I think I got the question wrong. The formula that I need is if CAT 2 then brief by column needs to insert a date 12 weeks prior. If CAT 1 then 4 weeks prior. Can you please tell me what would the formula me for this case? Also, I didn't understand what @row=2,84,28 meant in your above comment.

Paul Newcome

The @row replaces the row number and tells the formula to just look at the column specified on whatever row the formula is on. It makes formulas more efficient and avoids the worry of accidentally typing in the wrong row number.

Here's a breakdown of the formula, but judging by your screenshot, it will need adjusted to accommodate more than 2 different selections.

=IF(ISDATE([Design Required By]@row), [Design Required By]@row - IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2, 84, 28))

=IF(ISDATE([Design Required By]@row),

This part means says to run the formula if the cell in the row the formula is on in the [Design Required By] column is a date.

[Design Required By]@row -

If it is a date, then we want to take that date and subtract a number of days.

IF([RISK(H/M/L) CAT (0,1,2,3)]@row = 2,

If the [RISK(H/M/L) CAT (0,1,2,3)] column on the formula row equals 2

84,

Then output 84 (days which is 12 weeks to subtract from the original date).

28))

Otherwise (if the above mentioned cell does not equal 2) output 28 (days which is 4 weeks to subtract from the original date).

I do see that you also have a zero in your screenshot though which leads me to believe there is the possibility of the CAT also being a 3.

The formula I have provided should give you a date 12 weeks prior for the CAT being a 2 and 4 weeks for the CAT being a 1.

If you need to account for 0 and 3, let me know. Otherwise the formula provided should be working. Can you copy/paste the formula that is giving you an error?

sahilhq

Thank you so much Paul!

Paul Newcome

Happy to help!

sahilhq

Paul, please can you reply to my other two recent posts about Smartsheet formulas? A quick response will be greatly appreciated.

Paul Newcome

Can you provide links to the posts in question? I can't promise that I'll be able to help, but I will definitely take a look...

sahilhq

Please see links below.

https://community.smartsheet.com/discussion/smartsheet-formula

https://community.smartsheet.com/discussion/smartsheet-formula-0

Paul Newcome

I have replied to both.

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…