Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 30, 2023 01:17 PM
Looking for a simple formula that spits out that last Friday of the calendar year. I'm trying to avoid needing to make this a manual update so would love to find a formula that just spits that out without me having to update it at the beginning of the year.
Thank you!
Solved! Go to Solution.
Aug 31, 2023 06:15 AM
DATETIME_FORMAT(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)),'MM/DD/YYYY')
Aug 30, 2023 05:05 PM - edited Aug 30, 2023 05:56 PM
It means the last Friday in December, right?
How about a formula like this?
From a given date, find the position of Friday from the last 9 days of the year and subtract from 32 to show Friday.
{Date} would also work with "CREATED_TIME()" or "LAST_MODIFIED_TIME()".
DATESTR(
YEAR({Date}) & "/12/" & (32 -
FIND("5",
WEEKDAY(DATESTR(YEAR({Date}) & "/12/31"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/30"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/29"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/28"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/27"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/26"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/25"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/24"))&
WEEKDAY(DATESTR(YEAR({Date}) & "/12/23"))
))
)
Aug 31, 2023 03:12 AM
Slightly more concise than the previous answer:
DATESTR(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)))
Aug 31, 2023 05:11 AM
Excellent answer!
I now know a little bit about the use of MOD functions.
Aug 31, 2023 05:49 AM
Amazing! Thank you @Sho and @David_Skinner !
If I wanted to change the output to a MM/DD/YYYY format, what's the best way to do that?
Really appreciate both of your quick responses!
Aug 31, 2023 06:15 AM
DATETIME_FORMAT(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)),'MM/DD/YYYY')
Aug 31, 2023 07:00 AM
Thank you so much @David_Skinner !!!