Converting Excel Formula to Smartsheet

BB2791

Hi,

I'm struggling to convert a formula that I have been able to use in Excel to my Smartsheet. I want to use the formula to change the status column based on an End Date. The Excel formula is:

IF($F2="p","No Date",IF($G2<$C$1,"archive",IF($G2<$C$3,"expired",IF($G2-$C$3<=$C$2,"Notifying","Current"))))

F2 = a cell in the Category column in Smartsheet

G2=a cell in the End Date column in Smartsheet

C1 = a static Date (9/1/22) - I put this in a row (Start Date Column) on top of my main data (see screenshot). I know this is common in Excel or people put these kinds of references in another tab but I'm not sure if that is good practice in Smartsheet

C2 = a static number of days (90) - referenced in "Asset/System ID" column in Smartsheet

The way I converted the Excel formula in Smartsheet was:

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < [Start Date]2, "archive", IF([End Date]@row, <TODAY(), "Expired", IF([End Date]@row - TODAY() - [Asset/System ID]2, "Notifying", "Current"))))

but it's giving me an "Incorrect Argument Set" error

Any tips or assistance would be much appreciated!

Amber Eakin

HI @BB2791 - I see a stray comma, and the final IF doesn't have a logic statement. Does this work?

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < [Start Date]2, "archive", IF([End Date]@row <TODAY(), "Expired", IF([End Date]@row - TODAY() <= [Asset/System ID]2, "Notifying", "Current"))))

Amber Eakin

@BB2791 - That's absolutely right. You can't use an absolute reference in a column formula. A workaround might be adding the actual figure into the formula. You can always update the formula to change the date if needed over time. You could add those absolute references into the Summary section just so everyone knows the current Notifying Period and Archive Date so that those are retained.

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row <DATE(2022, 9, 1), "archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= 90, "Notifying", "Current")))

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

BB2791

That makes sense. Thank you @Amber Eakin

Have a nice day :)

Paul Newcome

@Amber Eakin We must have been typing at the same time. Haha.

@BB2791 You CAN reference a Sheet Summary field in a column formula, so if you move those dates to sheet summary fields and then reference those instead of absolute references to specific cells within the sheet, you can apply it as a column formula and just change the sheet summary field as needed instead of having to hard-code it into the formula and updating the formula itself when it changes.

Amber Eakin

HI @BB2791 - I see a stray comma, and the final IF doesn't have a logic statement. Does this work?

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < [Start Date]2, "archive", IF([End Date]@row <TODAY(), "Expired", IF([End Date]@row - TODAY() <= [Asset/System ID]2, "Notifying", "Current"))))

Paul Newcome

I see two syntax issues right off.

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < [Start Date]2, "archive", IF([End Date]@row, <TODAY(), "Expired", IF([End Date]@row - TODAY() - [Asset/System ID]2, "Notifying", "Current"))))

The comma after the second [End Date]@row needs to be removed.

You didn't finish the logical statement portion of your last IF statement.

BB2791

Thank you both! The comma error fixed part of things and now I'm able to get "Expired", "Archived" and "No Date" statuses but gives me an "Invalid Operation" for rows that should have Notifying and Current statuses

I then added the last logical stmt (I didn't need this in Excel so wasn't sure I would in Smartsheet) and I added some $'s to lock cell references:

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < $[Start Date]$2, "archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() - $[Asset/System ID]$2, "Notifying", IF([End Date]@row > TODAY() + $[Asset/System ID]$2, "Current")))))

But I'm now getting an "Invalid Data Type" error for cells that should be Notifying and Current statuses

Amber Eakin

Hi @BB2791 - You're still missing one logic statement. Try this!

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row < $[Start Date]$2, "archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= $[Asset/System ID]$2, "Notifying", "Current")))

BB2791

Oh yeah, totally missed what you were saying about the logic statement. That did the trick. Thank you so much!

BB2791

One last question - now that the formula is working properly I'm trying to make the Status column a 'function column' but I'm getting an error that says the "syntax isn't right". I think this is due to the hard references but is there a workaround?

Amber Eakin

@BB2791 - That's absolutely right. You can't use an absolute reference in a column formula. A workaround might be adding the actual figure into the formula. You can always update the formula to change the date if needed over time. You could add those absolute references into the Summary section just so everyone knows the current Notifying Period and Archive Date so that those are retained.

=IF(Category@row = "Purchase", "No Date", IF([End Date]@row <DATE(2022, 9, 1), "archive", IF([End Date]@row < TODAY(), "Expired", IF([End Date]@row - TODAY() <= 90, "Notifying", "Current")))

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

BB2791

That makes sense. Thank you @Amber Eakin

Have a nice day :)

Amber Eakin

You, too!

Paul Newcome

@Amber Eakin We must have been typing at the same time. Haha.

@BB2791 You CAN reference a Sheet Summary field in a column formula, so if you move those dates to sheet summary fields and then reference those instead of absolute references to specific cells within the sheet, you can apply it as a column formula and just change the sheet summary field as needed instead of having to hard-code it into the formula and updating the formula itself when it changes.

BB2791

Thanks @Paul Newcome !

Paul Newcome

Happy to help. 👍️