top of page

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

bottom of page