top of page

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!

vecteezy_downloading-and-storage-in-internet-concept-young-smiling_13753985.jpg

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