Helpful Formulas for Larger Schedules

John Sauber

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?

Sam S

Great formulas, thanks for sharing! Here are a few date formulas I have built (not quite as complex as yours!)

 

This one displays quarter and fiscal year for a date:

 

Result: Q4FY2014

 

=IF(ISDATE(Date14), IF(MONTH(Date14) = 1, "Q1FY" + YEAR(Date14), IF(MONTH(Date14) = 2, "Q1FY" + YEAR(Date14), IF(MONTH(Date14) = 3, "Q1FY" + YEAR(Date14), IF(MONTH(Date14) = 4, "Q2FY" + YEAR(Date14), IF(MONTH(Date14) = 5, "Q2FY" + YEAR(Date14), IF(MONTH(Date14) = 6, "Q2FY" + YEAR(Date14), IF(MONTH(Date14) = 7, "Q3FY" + YEAR(Date14), IF(MONTH(Date14) = 8, "Q3FY" + YEAR(Date14), IF(MONTH(Date14) = 9, "Q3FY" + YEAR(Date14), IF(MONTH(Date14) = 10, "Q4FY" + YEAR(Date14), IF(MONTH(Date14) = 11, "Q4FY" + YEAR(Date14), IF(MONTH(Date14) = 12, "Q4FY" + YEAR(Date14))))))))))))))

 

Show the month name of a date:

 

Result: April

 

=IF(ISDATE(Date29), IF(MONTH(Date29) = 1, "January", IF(MONTH(Date29) = 2, "February", IF(MONTH(Date29) = 3, "March", IF(MONTH(Date29) = 4, "April", IF(MONTH(Date29) = 5, "May", IF(MONTH(Date29) = 6, "June", IF(MONTH(Date29) = 7, "July", IF(MONTH(Date29) = 8, "August", IF(MONTH(Date29) = 9, "September", IF(MONTH(Date29) = 10, "October", IF(MONTH(Date29) = 11, "November", IF(MONTH(Date29) = 12, "December")))))))))))))

 

 

SashaR

This one isnt long but took FOREVER to figure out...

 

It will pull the domain out of an email address and capitolizes (only works with .com addresses):

 

If the selected email address is John@doe.com the result would be Doe

 

=IF(ISBLANK(Email10), "", UPPER(MID(Email10, (FIND("@, Email10) + 1), 1)) + MID(Email10, (FIND(@, Email10) + 2), ((FIND(.c", Email10)) - (FIND("@", Email10) + 2))))

Sam S

Here is another, returns a selected date in a YYYYMMDD format:

 

=YEAR(Date42) + "" + LEFT("0", 2 - LEN(MONTH(Date42))) + MONTH(Date42) + LEFT("0", 2 - LEN(DAY(Date42))) + DAY(Date42)

John Sauber

Sam, those are good formulas. You can reduce the size of the quarter formula by using inequalities. See below:

=IF(ISDATE([Start On]1), IF(MONTH([Start On]1) < 4, "Q1FY" + YEAR([Start On]1), IF(MONTH([Start On]1) < 7, "Q2FY" + YEAR([Start On]1), IF(MONTH([Start On]1) < 10, "Q3FY" + YEAR([Start On]1), "Q4FY" + YEAR([Start On]1))

 

SashaR, this is another good one. The version below will work for all 3-character top-level domain types, not just ".com.":

=UPPER(MID(Email1, FIND("@, Email1) + 1, LEN(Email1) - FIND(@", Email1) - 4))

 

If you have international TLD's such as johndoe@doe.co.uk, this isn't going to work and would require a bit extra:

 

Carol Hacker

After reading about these formulae I'm hoping that someone might have something that can help me.  I'm trying to create a formula for an event schedule - have 100 events, could be 1 day, 2 day, 3 day, 5 day events.  I have a current Start Date and End Date for each.  Want to be able to figure out what the Start and End Date would be if I have increments of (every 2 months, every 3 months, every 4 months, up to every 6 months.  I only want Monday - Friday dates and I want to exclude holidays.  

Example:

Start Date  Sep 14 2015

End Date Sep 16 2015

What date would be if I scheduled this event every 2 months for next 2 years? 

Judith Blanken

I need a formula for

 

If there is a predecessor

the status of predecessor is complete (other row)

than show "start"

If there is NO predecessor show "start"

 

 

How can I make this?

Name of columns:

Predecessor: Voorafgaande taak

Status: Voltooid

 

THANKS!

 

 

JohnHinkle

Hi Judith,

 

I am not sure you will get exactly what you are looking for but we can get close.

 

Check if there is a predecessor - easy.

 

=IF(ISBLANK(Predecessor2), 

 

Check if the status of predecessor is complete and show start - possible but maybe not exactly what you want. 

 

You can reference any cell and check if it has a certain value, then perform an action. 

 

=IF(Status1 = "Done", "Start")

 

This checks the cell, Status1. If it is "Done" then formula displays "Start". You will have to manually select the predecessor status cell in the formula.  

 

If no predecessor, show start - easy, just add a condition to the above formula. 

 

To put this all together. This formula would be in Row 2 and the predecessor is Row 1.

 

=IF(ISBLANK(Predecessor2), "Start", IF(Status1 = "Done", "Start", "Not Ready"))

 

This says, if Predecessor2 is blank, show Start. If not then, if Status1 is Done, show Start. If not, show Not Ready.

Judith Blanken

Thanks! It works!!