# Date formula help

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

Tagged:

• 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