InfoSpark's Helpful Smartsheet Formulas
Power users of Smartsheet will often need to come up with a clever formula to get their Smartsheet sheet to do what they need. Below you'll find a helpful list of the most popular formulas based on the years of Smartsheet consulting projects the team has been working on.
We have dozens and dozens of go-to formulas, but below we have listed some of the ones that we've used the most.
Get in touch with us if you need any more help for your Smartsheet project!
Next Month
Checkbox Column
Have the checkbox automatically tick when a date column is in the next month:
=IF(AND(MONTH([DATE_COLUMN_NAME_HERE]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) +35), YEAR([DATE_COLUMN_NAME_HERE]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) +35)), true)
Convert a text date to a Smartsheet Date
Date Column
If you have a date as text in "ISO" format, e.g. 2024-08-29
=IFERROR(DATE(VALUE(LEFT([DATE_COLUMN_NAME_HERE]@row, 4)), VALUE(MID([DATE_COLUMN_NAME_HERE]@row, 6, 2)), VALUE(MID([DATE_COLUMN_NAME_HERE]@row, 9, 2))), "")
Level of Hierarchy
Text/Number column
When you use the indent feature, to create a work breakdown structure, it's sometimes handy to easily see how many levels a task has been indented...
=COUNT(ANCESTORS()) + 1
Month Name
Text/Number column
If you have a month number, you often want to convert that into a month name. e.g. '1' to become 'January'
=IF([Month Number]@row = 1, "January", IF([Month Number]@row = 2, "February", IF([Month Number]@row = 3, "March", IF([Month Number]@row = 4, "April", IF([Month Number]@row = 5, "May", IF([Month Number]@row = 6, "June", IF([Month Number]@row = 7, "July", IF([Month Number]@row = 8, "August", IF([Month Number]@row = 9, "September", IF([Month Number]@row = 10, "October", IF([Month Number]@row = 11, "November", IF([Month Number]@row = 12, "December", "ERROR!"))))))))))))
Day Name
Text/Number column
If you have a weekday number, you often want to convert that into a weekday name. e.g. '1' to become 'Sunday'
​
=IF([Weekday Number]@row = 1, "Sunday", IF([Weekday Number]@row = 2, "Monday", IF([Weekday Number]@row = 3, "Tuesday", IF([Weekday Number]@row = 4, "Wednesday", IF([Weekday Number]@row = 5, "Thursday", IF([Weekday Number]@row = 6, "Friday", IF([Weekday Number]@row = 7, "Saturday", "ERROR!")))))))