First thursday of the year

[EtienneD]

Hi everybody,

In cell S2 I have the "=TODAY()" formula to get today's date. I need to get the first Thursday of the year based on cell S2. I tried multiple formulas I found in this forum but none of them work on my sheet.

 

[Joe4]

Hi everybody,

In cell S2 I have the "=TODAY()" formula to get today's date. I need to get the first Thursday of the year based on cell S2. I tried multiple formulas I found in this forum but none of them work on my sheet.

What exactly is in cell S2?
Is it a valid date, or a just a year number?

 

[Sanjeev1976]

Try the below formula :

=WORKDAY.INTL(("1-"&YEAR(S2))-1,1,"1110111")

 

[mikerickson]

=DATE(1943,1,1)+MOD(5-DATE(1943,1,1), 7) is the first Thursday of 1943

 

[JEC]

Based on the fact that the 4th of January is always in the first week.

=DATE(YEAR(S2),1,4)-WEEKDAY(DATE(YEAR(S2),1,4),2)+4

 

[Fluff]

Another option

=WORKDAY.INTL(DATE(YEAR(S2),1,0),1,"1110111")