Create a date column that adds to a date but on a particular day.

I have a Date A column and I want to add 14 days to for a Date B column but that date needs to be a Thursday.

Answers

  • Jgorsich
    Jgorsich ✭✭✭✭

    So you want to find the closest Thursday that is 2 weeks out? First, you'd have to know what to do for every initial day OTHER than Thursdays - is the date you are wanting "the first Thursday that is AT LEAST 2 weeks out" (which will sometimes be 20 days away, if your entry date is a Friday) or "the last Thursday before 2 weeks are gone" (which might be as few as 8 days if your entry date is a Wednesday) or "the Thursday in the future that is closest to 2 weeks away", which will get a bit fuzzy….

    Once you know the answer to that, you'll then use and abuse the "weekday" function to achieve what you want ( https://help.smartsheet.com/function/weekday?frame=0&nav=1 ). Get the day of the week for your entry date and compare it to Thursday (which is 5) and add days accordingly. If you wanted to go "the first Thursday that is at least 2 weeks away" I'd do something like this: "=EntryDate@row + 14 + 5 - weekday(EntryDate@row)"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!