Date formula that returns: Friday before the last Tuesday of the month
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!
Answers
-
This is a fun one. I had to break it down into a few steps to keep my head on straight:
- 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
- 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)
- 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)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!