Using If/or/and to change date based on different drop downs and checkmark selections

pbartlett117

Hi all,

Adding on to an existing formula to automatically add a number of days depending if a column is checked or not.

The bone column is either "NA" or various types of bones which populates the final date in the QC column based what is selected. I'm trying to add if the Timeline extension box is checked, it will add the number of days listed in the Days Extended column to that QC date. Right now, the formula below works if I have Sternum selected: It will adjust the date based on the timeline column being checked or not. But when I change Bone to NA, it still displays a date for that selection but it no longer adjusts the date when checking the extension column box. Do I need another IF statement somewhere or if this requiring an AND statement to make it work regardless of what is selected in the Bone column?

Thank you in advance!!

=IF([Last Nx/End Date]@row = "", "01/01/22", IF([Histology Requested]@row = "No", [Last Nx/End Date]@row, IF(OR([Decal Start / Trim]@row = "NA", ISERROR([Decal Start / Trim]@row)), WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), IF([Timeline Extension]@row = 0, WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), WORKDAY([Stain / Scan]@row, [Days Extended]@row + 1, {KAN Holidays Range 1})))))

jmyzk_cloudsmart_jp

Hi @pbartlett117

I checked your formula with a demo sheet below, and found that the following part is causing the error;

OR(logical_expression1, ISERROR([column]@row))

First, in your formula below, from your comment 'The bone column is either "NA" or various types of bones', I guess the [Decal Start / Trim]@row should be Bone@row.

IF(OR([Decal Start / Trim]@row = "NA", ISERROR([Decal Start / Trim]@row)),
 vs
IF(OR(Bone@row = "NA", ISERROR(Bone@row)),

See the [QC - OR (Bone)] formula below in the All Formulas.

https://app.smartsheet.com/b/publish?EQBCT=f3a5085ba7ff4c348d389a55a1be32c7

Then, I tested various formats to give Workday@row to the QC column if Bone is NA or ISERROR. BYW, [Workday] and [Workday Extended] are the two helper columns with the WORKDAY formula to make the main formula easy to understand. (See the [Workday] and [Workday Extended] formulas below.)

Test 1

[Bone OR] =IF(OR(Bone@row = "NA", [Bone is Error]@row), Workday@row, "Other")

The [Bone OR] =IF(OR(Bone@row = "NA", [Bone is Error]@row), Workday@row, "Other") formula, as the one in your formula, gives me the #INVALID VALUE error.

Test 2

[ISERROR(Bone)] =IF(ISERROR(Bone@row), Workday@row, IF(Bone@row = "NA", Workday@row, "Other"))

The [ISERROR(Bone)] =IF(ISERROR(Bone@row), Workday@row, IF(Bone@row = "NA", Workday@row, "Other")) formula, which does not use the OR function, also gives an error.

Test 3

[Bone IF IF] =IF([Bone is Error]@row, Workday@row, IF(Bone@row = "NA", Workday@row, "Other"))
[Bone is Error] =ISERROR(Bone@row)

Lastly, I used a helper checkbox column, [Bone is Error], with the ISERROR(Bone@row) formula, and combined the two with the IF clauses as shown above. This format gives no error.

So, I incorporated this format in the following formula;

[QC 2] =IF([Bone is Error]@row, Workday@row, IF(Bone@row = "NA", Workday@row, IF([Last Nx/End Date]@row = "", "01/01/22", IF([Histology Requested]@row = "No", [Last Nx/End Date]@row, IF([Timeline Extension]@row = 0, Workday@row, [Workday Extended]@row)))))

I do not understand why Smartsheet does not accept the two other formats, but the above format seems to work.😅

All Formulas

[QC ] =IF([Last Nx/End Date]@row = "", "01/01/22", IF([Histology Requested]@row = "No", [Last Nx/End Date]@row, IF(OR([Decal Start / Trim]@row = "NA", ISERROR([Decal Start / Trim]@row)), WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), IF([Timeline Extension]@row = 0, WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), WORKDAY([Stain / Scan]@row, [Days Extended]@row + 1, {KAN Holidays Range 1})))))

[QC - OR (Bone)] =IF([Last Nx/End Date]@row = "", "01/01/22", IF([Histology Requested]@row = "No", [Last Nx/End Date]@row, IF(OR(Bone@row = "NA", ISERROR(Bone@row)), WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), IF([Timeline Extension]@row = 0, WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1}), WORKDAY([Stain / Scan]@row, [Days Extended]@row + 1, {KAN Holidays Range 1})))))

[Bone OR] =IF(OR(Bone@row = "NA", [Bone is Error]@row), Workday@row, "Other")

[ISERROR(Bone)] =IF(ISERROR(Bone@row), Workday@row, IF(Bone@row = "NA", Workday@row, "Other"))

[Bone IF IF] =IF([Bone is Error]@row, Workday@row, IF(Bone@row = "NA", Workday@row, "Other"))

[QC 2] =IF([Bone is Error]@row, Workday@row, IF(Bone@row = "NA", Workday@row, IF([Last Nx/End Date]@row = "", "01/01/22", IF([Histology Requested]@row = "No", [Last Nx/End Date]@row, IF([Timeline Extension]@row = 0, Workday@row, [Workday Extended]@row)))))

[Bone is Error] =ISERROR(Bone@row)

[Workday] =WORKDAY([Stain / Scan]@row, 1, {KAN Holidays Range 1})

[Workday Extended] =WORKDAY([Stain / Scan]@row, [Days Extended]@row + 1, {KAN Holidays Range 1})

pbartlett117

Hi @jmyzk_cloudsmart_jp!

Thanks for the hefty write up, I would have not thought of, or been able to test different parts of the formula in that way.

To answer your question about what needed to be NA:

"First, in your formula below, from your comment '

The bone column is either "NA"

 or various types of bones', I guess the [Decal Start / Trim]@row should be 

Bone@row."

The [Decal Start / Trim]@row was correct in my case as early on when I was creating this formula, I was getting an error when that column was NA (if Bone@row was NA, then the Decal Start@row would also be NA, otherwise it would populate a +1 date from another row that wasn't included in the initial screenshot). However, it also seems that your adjustment also works!

Am I interpreting things correctly that it is not possible to make this formula work without the helper columns or at least the "workday extension" column for when the Bone column = NA?

Interestingly enough (and another issue entirely I think), through my own trial and error, I ended up with a formula that works when the "Bone@row" is NA and properly extends the date when "timeline extension@row" is checked. However, when "Bone@row" is changed to something else, the "QC/Upload@row" is blank with no value. Wonder if this