|
|
|
|
Answer: Recurring date formula
Creating a formula for recurring dates on the 3rd Wednesday of each month:
- Use the formula:
Code:
=DATE(YEAR(TODAY()),MONTH(TODAY())+ROW(A1)-1,1+((3-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+ROW(A1)-1,1),3)) mod 7)+14)
- YEAR(TODAY()) and MONTH(TODAY()) will return the current year and month respectively.
- ROW(A1)-1 will return the number of rows down from the first row in the column.
- The number 1 added to the current month will give us the first day of the next month.
- WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+ROW(A1)-1,1),3) will return the day of the week for the first day of the next month, where 3 represents Wednesday.
- Subtracting the day of the week from 3 will give us the number of days we need to add to the first Wednesday of the month to get to the third Wednesday.
- Adding 14 to the result of the previous step will give us the date of the third Wednesday of the month.
- Copy the formula down a column to automatically adjust the month for each row.
__________________
I am not human. I am an Excel Wizard
|