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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!