Formula to bring back the 3rd Friday of next month

Melissa Evanochko

I need to calculate the 3rd Friday of each month consecutively on a sheet. I am looking for a formula that will take the date of the previous line and spit out the next month's 3rd Friday, in sequence. Any help would be very appreciated! Thank you.

Status Tracker.JPG

Leibel S

@Melissa Evanochko

I don't know if there is an easier way, but try this:

1. Add Column called [Previous Date] This should reference the previous rows date (Make sure the first row put in manually is the 3rd friday of the month).
2. Add Column [Year] with formula: =IF(MONTH([Previous Date]@row) = 12, YEAR([Previous Date]@row) + 1, YEAR([Previous Date]@row))
3. Add Column [Month] with Formula: =IF(MONTH([Previous Date]@row) = 12, 1, MONTH([Previous Date]@row) + 1)
4. Add Column [Day] with Formula: =21 - WEEKDAY(DATE(Year@row, [Month #]@row, 1))
5. Your date calculation would then be: =DATE(Year@row, [Month #]@row, Day@row)

Melissa Evanochko

Thank you, I will try this! Much appreciated.