Date formula help: return 90 days earlier than start date, on the closest Tuesday

Hi Vyanessa,

This will look complicated, but it's actually fairly straight forward when broken down. Keep in mind you'll have to paste this into a Date Type of column.

Try this:

=IF(WEEKDAY([Start Date]@row - 90) = 1, ([Start Date]@row - 90) + 2, IF(WEEKDAY([Start Date]@row - 90) = 2, ([Start Date]@row - 90) + 1, IF(WEEKDAY([Start Date]@row - 90) = 3, [Start Date]@row - 90, IF(WEEKDAY([Start Date]@row - 90) = 4, ([Start Date]@row - 90) - 1, IF(WEEKDAY([Start Date]@row - 90) = 5, ([Start Date]@row - 90) - 2, IF(WEEKDAY([Start Date]@row - 90) = 6, ([Start Date]@row - 90) - 3, IF(WEEKDAY([Start Date]@row - 90) = 7, ([Start Date]@row - 90) + 3)))))))

Broken Down:

=IF(WEEKDAY([Start Date]@row - 90) = 1, ([Start Date]@row - 90) + 2,

IF(WEEKDAY([Start Date]@row - 90) = 2, ([Start Date]@row - 90) + 1,

IF(WEEKDAY([Start Date]@row - 90) = 3, [Start Date]@row - 90,

IF(WEEKDAY([Start Date]@row - 90) = 4, ([Start Date]@row - 90) - 1,

IF(WEEKDAY([Start Date]@row - 90) = 5, ([Start Date]@row - 90) - 2,

IF(WEEKDAY([Start Date]@row - 90) = 6, ([Start Date]@row - 90) - 3,

IF(WEEKDAY([Start Date]@row - 90) = 7, ([Start Date]@row - 90) + 3)))))))

This formula looks at what the day of the week is, 90 days before the Start Date. If it's a Sunday (or day 1 of the week), then it will minus 90 days, but then add two to get to the nearest Tuesday.

If it's a Monday (day 2 in the week), it only needs to minus 90 days and then add 1 to get to the nearest Tuesday.... and so on.

Here are some Help Center articles I used: WEEKDAY function / Using Dates in Formulas / @row Function

Let me know if you have any questions about this!

Cheers,

Genevieve

Great! Happy to help. 😊