Formula for recurring meetings

Teresa Almeida

Hi, 

I'm currently working on a smartsheets for a project that has a number of deliverables. As part of the process for each one, they need to be reviewed in a meeting - there are meetings already set-up with the client every Wednesday and Friday which is when they will all be reviewed. 

 

Is there a way for me to create a formula to say something like if the owner of the line is [Client] then the due date is the next available Wednesday or Friday?

 

The project plan is always in flux, so i'm trying to find an easy way of not having to go over each deliverable manually anytime a deadline is missed.

 

Thanks, 

Teresa

Shaine Greenwood

Hi Teresa,

 

You can create a formula that looks at every day of the week using the WEEKDAY function inside a nested IF statement, which returns another date value with the added days you need. I've used the OR function to make the formula a bit shorter, as some days of the week will add the same number of days to get what you need.

 

This is the formula that I tested to work in my sheet using me as the assigned to. You'll need to adjust this to fit the columns and assigned to person in your sheet.

 

IMPORTANT: Do not copy and paste this formula directly into your sheet. The way that Smartsheet understands formula syntax relies on the formula itself being entirely plain text. The formatting here in the community is rich text, and it will cause the formula to not work in your sheet. You'll NEED to manually type this, or paste it into another application that can convert it into plain text first.

 

=IF(AND([Assigned To]7 = "Shaine Greenwood", WEEKDAY([Due Date]7) = 1), [Due Date]7 + 3, IF(AND([Assigned To]7 = "Shaine Greenwood", OR(WEEKDAY([Due Date]7) = 2, WEEKDAY([Due Date]7) = 4), [Due Date]7 + 2, IF(AND([Assigned To]7 = "Shaine Greenwood", OR(WEEKDAY([Due Date]7) = 3, WEEKDAY([Due Date]7) = 5)), [Due Date]7 + 1, IF(AND([Assigned To]7 = "Shaine Greenwood", WEEKDAY([Due Date]7) = 6), [Due Date]7 + 4)))))

 

The syntax breaks down to this: IF Assigned To in Row 7 is "Shaine Greenwood" AND the weekday number of Due Date in Row 7 is 1 (which is Sunday), then return Due Date plus 3 days, which will be Wednesday.

 

Check out our functions article in the help center for more information on these formulas: https://help.smartsheet.com/articles/775363