Converting Excel Formula to Smartsheet

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

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

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

That makes sense. Thank you @Amber Eakin

Have a nice day :)

@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.

All comments

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

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.

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

{5680CA25-EAB5-4AD3-A478-893EFEB81B20}.png.jpg

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

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

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?

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

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

That makes sense. Thank you @Amber Eakin

Have a nice day :)

You, too!

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

Thanks @Paul Newcome !