Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recurring Date Mickey Excel Worksheet Functions 2 May 25th 09 03:46 AM
Recurring Excel Formula error - multiple users affected! Rayo K Excel Discussion (Misc queries) 3 April 11th 06 02:22 PM
Formula to compare two lists and separate non-recurring items? Tennessee Excel Worksheet Functions 2 November 10th 05 07:32 PM
Recurring annual events using a specific date as a trigger date Bamboozled Excel Worksheet Functions 1 June 6th 05 01:44 PM
Calculating recurring date in following month, calculating # days in that period Walterius Excel Worksheet Functions 6 June 4th 05 11:21 PM


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"