Date Range

Posted by lucky on January 30, 2002 7:19 AM

Hi all!
I have 2 dates entered in 2 cells on sheet1 e.g
a1 = 01/05/1999
a2 = 01/09/1999
and I wish to automatically produce a monthly range on a separate sheet that begins with the date in sheet1!a1 and ends with the date in sheet1!a2, i.e. resulting in;
Sheet2
a1 = 01/05/1999
a2 = 01/06/1999
a3 = 01/07/1999
a4 = 01/08/1999
a5 = 01/09/1999
the date range will vary from a few months to up to 24 months.
Any ideas??
Cheers!

Posted by Juan Pablo G. on January 30, 2002 7:43 AM

How about this formula ?

In Sheet2!A1 put

=DATE(YEAR(Sheet1!A1),MONTH(Sheet1!A1),1)

in Sheet2!A2:A24 put this formula

=IF(AND(A1,DATE(YEAR(A1),MONTH(A1)+1,1)&LT;=Sheet1!$A$2),DATE(YEAR(A1),MONTH(A1)+1,1),0)

Then, format this as

dd/mm/yyyy;;

or the date format you want.

Juan Pablo G.

Posted by lucky on January 31, 2002 2:00 AM

Thanks, but

Thanks Juan Pablo
How would I make the cells in Sheet 2 show no value instead of 00-Jan-00 when they are not required?
I attempted with the following which worked for one cell directly after the last date but then produced #VALUE! for the remaining cells
=IF(AND(A15,DATE(YEAR(A15),MONTH(A15)+1,1)&LT;=Sheet1!$A$2),DATE(YEAR(A15),MONTH(A15)+1,1),"")

Cheers again!!

Posted by Juan Pablo G. on January 31, 2002 7:28 AM

Re: Thanks, but

Did you tried the format I told you ? that's to hide the 0's.

dd/mm/yyyy;;

Juan Pablo G.

Posted by lucky on February 01, 2002 6:13 AM

Beautiful!