Date formula help

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

Answers

  • 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

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

  • Great! Happy to help. 😊

Sign In or Register to comment.