Date formula that returns: Friday before the last Tuesday of the month

Beth Fantozzi 1

Hi everyone - I'd like to pick your brain:

I've been asked to automate an update request to be sent on the Friday before the last Tuesday of the month. Is there a formula or a way to calculate this date?

Thanks in advance for your help!

Jason Tarpinian

This is a fun one. I had to break it down into a few steps to keep my head on straight:

1. This field will give you the last day of any given month (Date). "Last Day of the Month" =DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1
2. Then we need to know what weekday was the last day of the month? "Weekday of the Last Month Day?" =WEEKDAY([Last Day of the Month]@row)
3. Then we decide how many days back do we need to go to find the last Tuesday (WEEKDAY = 3), then from there find our Friday. "Formula" =IF([Weekday of the Last Month Day?]@row >= 3, [Last Day of the Month]@row - ([Weekday of the Last Month Day?]@row + 1), [Last Day of the Month]@row - ([Weekday of the Last Month Day?]@row + 1) - 7)

Then you can combine it all into one ugly formula with "Date" as the only field you are referencing.

=IF(WEEKDAY((DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1)) >= 3, (DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1) - (WEEKDAY((DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1)) + 1), (DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1) - (WEEKDAY((DATE(IF(MONTH(Date@row) = 12, YEAR(Date@row) + 1, YEAR(Date@row)), IF(MONTH(Date@row) = 12, 1, MONTH(Date@row) + 1), 1) - 1)) + 1) - 7)

Beth Fantozzi 1

Jason, thank you so much for taking the time to help with this! I'll throw it in my sheet in a bit but didn't want to wait to send you a huge THANK YOU! I continue to learn so much from this community and am so grateful for your help!

Beth Fantozzi 1

This works! Jason, thanks again - this has been so helpful.

In case anyone stumbles across this answer and wants to use this solution: I was getting an error message until I set the column type (where I was placing the above formula) as a date.