Finding A Date / Day of week before a start date

Travis Ryan

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!

Jeff Reisman

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), "")))))

Jeff Reisman

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), "")))))

Travis Ryan

https://community.smartsheet.com/discussion/comment/321169#Comment_321169

This works wonderfully! Thank You! I was a little to simplistic I see in my original...