Here are a few formulas which are perhaps a bit more advanced, but have really helped our organization. I have also placed them into a published sheet found here, where you can see the formuals working.
Weeks to Start
Calculates the number of weeks away a task's start date is. This works for any date, such as a task's Complete On date as well. This can be very handy when filtering and for showing groups of tasks starting or finishing in the same weeks into the future.
=IF([% Complete]1 = 1, "-", IF(DATEONLY([Start On]1) < TODAY(), 0, INT((DATEONLY([Start On]1) - TODAY(1)) / 7) + 1))
Number of Predecessors
Certain task types in many production schedules may have "natural" numbers of predecessors, meaning Task Type A might appear 75 times in a schedule, and it normally has 2 predecessors. Task Type B might appear 40 times in the schedule, and it normally has just 1 predecessor. You can count the number of predecessors a task has, and can use reporting to identify possible mistakes in the number of predecessors a task has. We have found that in a large sheet which is shared by many people, users occasionally will move a task's start date and wipe out all of the predecessors inadvertantly. A report (or conditional formatting) showing all Task Type A tasks where the number of predecessors is not equal to 2 would quickly bring these to light. With the size of one of our schedules and the number of users involved, this trick has located countless scheduling errors.
=IF(ISBLANK(Predecessors1), 0, LEN(Predecessors1) - LEN(SUBSTITUTE(Predecessors1, ",", "")) + 1)
Day of the Week of a Date
We had to refrain from using this formula on our large sheets because the formula is quite large, and it bogged down performance, especially when conditionally formatting based on the day of the week. For small to modest-sized sheets, this formula returns the day of the week, for a date-type or date/time-type column.
=IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 10)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 10)) / 7 = 0, "Sunday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 11)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 11)) / 7 = 0, "Monday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 12)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 12)) / 7 = 0, "Tuesday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 13)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 13)) / 7 = 0, "Wednesday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 14)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 14)) / 7 = 0, "Thursday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 15)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 15)) / 7 = 0, "Friday", IF(INT((DATEONLY([Start On]1) - DATE(2015, 5, 16)) / 7) - (DATEONLY([Start On]1) - DATE(2015, 5, 16)) / 7 = 0, "Saturday", "Other")))))))
What other creative or advanced formulas help you work better in Smartsheet?