Help

A formula that outputs the last Friday of the calendar year

Topic Labels: Formulas
Solved
Jump to Solution
1179 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Lisa_Bauer
6 - Interface Innovator
6 - Interface Innovator

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! 

1 Solution

Accepted Solutions
David_Skinner
9 - Sun
9 - Sun
DATETIME_FORMAT(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)),'MM/DD/YYYY')

See Solution in Thread

6 Replies 6
Sho
11 - Venus
11 - Venus

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"))
  ))
)

 

 

 

 

Slightly more concise than the previous answer:

DATESTR(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)))

Excellent answer!

I now know a little bit about the use of MOD functions.

Lisa_Bauer
6 - Interface Innovator
6 - Interface Innovator

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! 

David_Skinner
9 - Sun
9 - Sun
DATETIME_FORMAT(YEAR(TODAY())&"-12-"&(31-MOD(WEEKDAY(YEAR(TODAY())&"-12-31")+2,7)),'MM/DD/YYYY')
Lisa_Bauer
6 - Interface Innovator
6 - Interface Innovator

Thank you so much @David_Skinner !!!