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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!