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

Vyanessa Stephan

Hello,

I'm looking for a formula to pull a date that is 90 days earlier than our start date but on the closest Tuesday. Thank you

Genevieve P.

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

Vyanessa Stephan

Thank you so much, this worked! Appreciate your help.

Genevieve P.

Great! Happy to help. 😊