Excel Date formula

[nephi]

Trying to create spreadsheet with 12 columns for each month of the year. I want to be able to type in an initial due date for example "01/02/02" and have the remaining formulas fill in as the next month, same day, so first column would be 01/02/02 second would be 02/02/02 3rd column would be 03/02/02, fourth is 04/02/02, etc. Then if I go back and change the initial date all the future dates will change to reflect remaining pmnt months.

 

[brettvba]

put the date in the cell you want to start in eg b1
then put in b2 =b1+1 and fill across
This message was edited by brettvba on 2002-04-18 21:16

 

[Yogi Anand]

On 2002-04-18 21:07, nephi wrote:
Trying to create spreadsheet with 12 columns for each month of the year. I want to be able to type in an initial due date for example "01/02/02" and have the remaining formulas fill in as the next month, same day, so first column would be 01/02/02 second would be 02/02/02 3rd column would be 03/02/02, fourth is 04/02/02, etc. Then if I go back and change the initial date all the future dates will change to reflect remaining pmnt months.

Key-in 01/02/02 in cell A2
then key-in =EDATE(A2,1) in cell B2
and then drag the value in cell B2 to C2, D2, E2, ....

and you will get 01/02/02, 02/02/02, 03/02/02, ... in consecutive cells these will be all 1 month apart successively from the dat in cell A2

HTH

Please post back if it works for you ... otherwise explain a little further and then let us take it from there!

_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line
This message was edited by Yogi Anand on 2003-01-19 18:33

 

[Yogi Anand]

On 2002-04-18 21:15, brettvba wrote:
put the date in the cell you want to start in eg b1
then put in b2 =b1+1 and fill across
This message was edited by brettvba on 2002-04-18 21:16

Hi brettvba:
Adding 1 to the initial value of 01/02/02 will add only one day to the original date, and then if you drag it to the right it will 1 day successively in each cell. The EDATE function facilitates changing month from the initial date entry.

Regards!

Yogi Anand

 

[nephi]

Thanks you guys, havent figured it out yet.
Using =EDATE(A2,1)caused the cell to show #NAME?

 

[Yogi Anand]

On 2002-04-18 21:35, nephi wrote:
Thanks you guys, havent figured it out yet.
Using =EDATE(A2,1)caused the cell to show #NAME?

Hi nephi:
To reiterate the procedure,

1) enter the first date in cell A2 as 01/02/02 (you may want to format it as DATE 03/04/97 (in Excel 97)

2) in cell B2 enter the following formula

=EDATE(A2,1)

3) then fill across cells C2, D2, ....

HTH

Regards!

Yogi Anand

 

[nephi]

Thanks so much, using the EDATE function under Insert/function/Date&time, worked perfectly. You're awesome you two
Nephi