Weekly date with manual override

Mark.Hendley

I'm looking to create a formula to have a cell show a date for when our next delivery comes in.

Typically these deliveries are every Tuesday, but sometimes they are late by 1 day, or we skip a week because we don't need it.

I would like a cell to show the next estimated delivery date

example.

Today the cell would show: "TODAY"

Tomorrow the cell would show: "Tuesday, Jan 2 2024" (we are skipping next week)

Again, once the day of delivery is here the cell will show "TODAY" and then the following day will show the next estimated delivery date.

I want to have 2 columns that are override columns, one that changes the date by one day, and the other by a week.

I'm really not sure where to even start with this formula so any insight is appreciated. Preferably an explanation as to how the formula works, so I can learn from it and help others.

Thanks!

Lucas Rayala

Hi @Mark.Hendley, I modified a formula from this post:

Function to calculate date of next Monday — Smartsheet Community

... to get the formula for "Next Tuesday" (with the exception of Tuesday being "Today"):

=IF(WEEKDAY(TODAY()) = 3, "Today", IF(WEEKDAY(TODAY()) < 3, TODAY() + 9, IF(WEEKDAY(TODAY()) > 3, TODAY() + 10 - WEEKDAY(TODAY()))))

Does this get you far enough? If you need to add a week or day with an override column, you would just need to take the output of above and add 1 or 7 to it, but that gets into how your data and sheets is formatted.