Excel 2007 : Date Formula to maitain data location

[grsnipe]

Thanks to all that help.

I am building a report that updates monthly for a rolling year. The attached example shows the first three months. The formula I have for the dates (C1:C2) runs off of cell D1 (today). The problem is next month the first report will be for February. How do I keep the report data in the same columns regardless of what month it is? I want to keep the returned data in calendar month order starting with January. I am using a V lookup from another page to locate the data in these reports.

[NBVC]

Why can't you change the MONTH part of the DATE formulas in C1:C2 to just 1 to indicate January?

e.g.

=DATE(YEAR(D1),1,1)

and in F1:F2 use 2 for February, etc..

[grsnipe]

NBVC,
Thanks for the response. This is for a rolling report where part of the data is from this year and the later data would be from last year.
I have come up with the following.
in cell C1 for Jan
=IF(MONTH($D$1)>1,DATE(YEAR($D$1),MONTH(1)+0,1),DATE(YEAR($D$1),MONTH(1)-12,1))
in cell F1 for Feb
=IF(MONTH($D$1)>2,DATE(YEAR($D$1),MONTH(1)+1,1),DATE(YEAR($D$1),MONTH(1)-11,1))
The end date range would refer to start date range
in cell C2 for Jan
=DATE(YEAR(C1),MONTH(C1)+1,0)

Again thanks for the input