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
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!