Finding A Date / Day of week before a start date
I am needing to build a formula to calculate when we want to have an order of materials delivered based on the start date of a job. We get deliveries on Wednesdays.
I like to give a day or two wiggle room so that if a job moves up we are not waiting for material. In other words if a job is starting Friday it can be delivered that same Wednesday. Any other days of the week we want to have a delivery the previous Wednesday.
I think my formula is close but not quite what I am trying to get. Its giving me the Wednesday before, but the friday starts are a full 10 days before where it should just be the current week wednesday.
=IFERROR(IF(WEEKDAY(StartDate@row) = 1, StartDate@row  5, StartDate@row  WEEKDAY(StartDate@row)  3), "")
What am I missing? Thanks in advance!
Best Answer

Here you go. Essentially, if the start date is a Monday, subtract 5 days to get the delivery date, if the start date is a tuesday, subract 6 days to get the delivery date, and so on, until you get to Friday, where you just subtract 2 days. If there's no start date at all, leave the delivery date blank.
=IF(WEEKDAY(StartDate@row) = 2, (StartDate@row  5), IF(WEEKDAY(StartDate@row) = 3, (StartDate@row  6), IF(WEEKDAY(StartDate@row) = 4, (StartDate@row  7), IF(WEEKDAY(StartDate@row) = 5, (StartDate@row  8), IF(WEEKDAY(StartDate@row) = 6, (StartDate@row  2), "")))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

Here you go. Essentially, if the start date is a Monday, subtract 5 days to get the delivery date, if the start date is a tuesday, subract 6 days to get the delivery date, and so on, until you get to Friday, where you just subtract 2 days. If there's no start date at all, leave the delivery date blank.
=IF(WEEKDAY(StartDate@row) = 2, (StartDate@row  5), IF(WEEKDAY(StartDate@row) = 3, (StartDate@row  6), IF(WEEKDAY(StartDate@row) = 4, (StartDate@row  7), IF(WEEKDAY(StartDate@row) = 5, (StartDate@row  8), IF(WEEKDAY(StartDate@row) = 6, (StartDate@row  2), "")))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

This works wonderfully! Thank You! I was a little to simplistic I see in my original...
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 378 Global Discussions
 208 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!