How to calculate the third friday of a month?
 

Does anyone have an idea of how to accomplish this?

Answer: How to calculate the third friday of a month?
 

To calculate the third Friday of a month in Excel:

1. Enter the year and month in separate cells. For example, enter "2021" in cell A1 and "8" (for August) in cell A2.
2. Use the
   Formula:

   ---

   DATE 


   ---

   function to create a date for the first day of the month. The formula would be:
   Formula:

   ---

   =DATE(A1,A2,1) 


   ---

3. Use the
   Formula:

   ---

   WEEKDAY 


   ---

   function to determine the day of the week for the first day of the month. The formula would be:
   Formula:

   ---

   =WEEKDAY(DATE(A1,A2,1)) 


   ---

4. Subtract the weekday number from 6 (which represents Friday) to determine how many days until the first Friday of the month. The formula would be:
   Formula:

   ---

   =6-WEEKDAY(DATE(A1,A2,1)) 


   ---

5. Add 14 to the result from step 4 to get the date for the third Friday of the month. The formula would be:
   Formula:

   ---

   =DATE(A1,A2,1)+14-(WEEKDAY(DATE(A1,A2,1))<6)*7 


   ---

This formula works by adding 14 days to the first day of the month and then subtracting the number of days until the first Friday of the month. The <6 part of the formula checks if the first day of the month is a Friday or later, and if so, subtracts an additional 7 days to get the correct date for the third Friday.

How to calculate the third friday of a month?
 

=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6))

with any date in A1 will return the third Friday of the month

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"KJP" wrote in message
...
Does anyone have an idea of how to accomplish this?