Forcing a specific day of week of workback

Hi! I am setting up a workback subtracting days from a date based on dependancies and used the hidden formula from The Monday Man which worked AWESOME! but I would like to take it one step further if possible. Two of my tasks MUST fall on a Tuesday - The brief date and the creative presentation date. Is there a way in the network days formula to force that. Not ALL days must be a Tuesday, just 2 in a list of 20.

Given his stellar expertise on this one, I might involve the monday man himself! Hey @JCorrell :wave: What are your thoughts on taking the formula a step further? I will raise this with our internal formula team too to investigate :mag:

Hi Sheri,

If you want to get the first Tuesday after a specific date, you can use this formula:

FORMAT_DATE(ADD_DAYS({Date},ADD(MINUS(2,WEEKDAY({Date},1)),IF(WEEKDAY({Date},1)>2,7,0))),"YYYY-MM-DD")
  • WEEKDAY({Date},1) returns the day of the week of your date (Monday: 1, Tuesday: 2)
  • MINUS(2,WEEKDAY({Date},1)) calculates the difference in days between the date and the next Tuesday. If the date is already Tuesday, this part becomes 0.
  • IF(WEEKDAY({Date}, 1)>2,7,0) checks if the given date is after Tuesday. If it is, it adds 7 days to move to the next week’s Tuesday.

So

  • ADD(MINUS(2,WEEKDAY({Date},1)),IF(WEEKDAY({Date},1)>2, 7, 0)) gives you the number of days that needs to be added to get to the next Tuesday.
  • ADD_DAYS({Date},ADD(MINUS(2,WEEKDAY({Date},1)),IF(WEEKDAY({Date},1)>2, 7, 0))) adds that number of days to the date.
  • Then we use FORMAT_DATE to get the date in a YYYY-MM-DD format (optional).

Now all you have to do is find a way to identify the dates that need to fall on a Tuesday (you’re not explaining how this can be done). You’ll probably have to encapsulate the above formula in an IF statement.


Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

Thanks for stepping in @GCavin!

@SheriB that should do it for you.