Recurring date formula
 

How can I create a formula which will results in setting recurring dates when
copied down a column. I want the date to be on the 3rd Wednesday of each
month. Each row would be for a successive month. This is essentially like
the process available in Outlook for scheduling appointments, set the
recurring criteria and it calculates the specific dates.

Answer: Recurring date formula
 

Creating a formula for recurring dates on the 3rd Wednesday of each month:

1. 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)

   ---

2. YEAR(TODAY()) and MONTH(TODAY()) will return the current year and month respectively.
3. ROW(A1)-1 will return the number of rows down from the first row in the column.
4. The number 1 added to the current month will give us the first day of the next month.
5. 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.
6. 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.
7. Adding 14 to the result of the previous step will give us the date of the third Wednesday of the month.
8. Copy the formula down a column to automatically adjust the month for each row.

Recurring date formula
 

Enter the first date in a cell:

A1 = 1/21/2009 = 3rd Wed in Jan 2009

Enter this formula in A2 and copy down as needed:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

--
Biff
Microsoft Excel MVP


"Mc/" wrote in message
...
How can I create a formula which will results in setting recurring dates
when
copied down a column. I want the date to be on the 3rd Wednesday of each
month. Each row would be for a successive month. This is essentially
like
the process available in Outlook for scheduling appointments, set the
recurring criteria and it calculates the specific dates.

Recurring date formula
 

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

I did dome tinkering and was able to reduce that to:

=A1+28+((DAY(A1+35)<22)*7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Enter the first date in a cell:

A1 = 1/21/2009 = 3rd Wed in Jan 2009

Enter this formula in A2 and copy down as needed:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

--
Biff
Microsoft Excel MVP


"Mc/" wrote in message
...
How can I create a formula which will results in setting recurring dates
when
copied down a column. I want the date to be on the 3rd Wednesday of each
month. Each row would be for a successive month. This is essentially
like
the process available in Outlook for scheduling appointments, set the
recurring criteria and it calculates the specific dates.

Recurring date formula
 

I have a similar question. I tried Formulas and got #VALUE! in A2 I did a
copy paste to be sure I didn't have syntax errors and the same thing. What I
want mine to do is list every Sunday beginning on November 25, 2009

"T. Valko" wrote:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

I did dome tinkering and was able to reduce that to:

=A1+28+((DAY(A1+35)<22)*7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Enter the first date in a cell:

A1 = 1/21/2009 = 3rd Wed in Jan 2009

Enter this formula in A2 and copy down as needed:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

--
Biff
Microsoft Excel MVP


"Mc/" wrote in message
...
How can I create a formula which will results in setting recurring dates
when
copied down a column. I want the date to be on the 3rd Wednesday of each
month. Each row would be for a successive month. This is essentially
like
the process available in Outlook for scheduling appointments, set the
recurring criteria and it calculates the specific dates.

Recurring date formula
 

November 25, 2009 is not a Sunday, but you could enter this and copy down as needed:

="25-Nov-2009"-WEEKDAY("25-Nov-2009")+8+(ROW(A1)-1)*7

Don't forget to format the cells as date.

DeanLinCPR wrote:
I have a similar question. I tried Formulas and got #VALUE! in A2 I did a
copy paste to be sure I didn't have syntax errors and the same thing. What I
want mine to do is list every Sunday beginning on November 25, 2009

"T. Valko" wrote:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))
I did dome tinkering and was able to reduce that to:

=A1+28+((DAY(A1+35)<22)*7)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Enter the first date in a cell:

A1 = 1/21/2009 = 3rd Wed in Jan 2009

Enter this formula in A2 and copy down as needed:

=DATE(YEAR(A1),MONTH(A1)+1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))

--
Biff
Microsoft Excel MVP


"Mc/" wrote in message
...
How can I create a formula which will results in setting recurring dates
when
copied down a column. I want the date to be on the 3rd Wednesday of each
month. Each row would be for a successive month. This is essentially
like
the process available in Outlook for scheduling appointments, set the
recurring criteria and it calculates the specific dates.