Finding A Date / Day of week before a start date

Travis Ryan
Travis Ryan ✭✭
in Formulas and Functions

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!

· Share on FacebookShare on Twitter

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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!

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!