Listing months and years in two columns based on start and end date.

K

KLynnB

Hello Everyone,

I freely admit to being an Excel idjut and I hope you all can help m
out. What I would like to do is enter a start date in one cell and en
date in another cell. Then I would like for the spreadsheet to lis
the months in between the start date and end date in one column and th
associated year to that month in the next column.

For example, if my start date is May 2000 and my end date is July 2001
the columns should be May 2000, June 2000, July 2000 until the end dat
is reached.

Is there a way to do this?

Thanks!

KLyn
 
R

Ron Rosenfeld

Hello Everyone,

I freely admit to being an Excel idjut and I hope you all can help me
out. What I would like to do is enter a start date in one cell and end
date in another cell. Then I would like for the spreadsheet to list
the months in between the start date and end date in one column and the
associated year to that month in the next column.

For example, if my start date is May 2000 and my end date is July 2001,
the columns should be May 2000, June 2000, July 2000 until the end date
is reached.

Is there a way to do this?

Thanks!

KLynn
Click to expand...

If StartDate is in A1, then place the following formula in B1:


=IF(A1="","",IF(DATE(YEAR(A1),MONTH(A1)+1,1)<=EndDate,DATE(YEAR(A1),MONTH(A1)+1,1),""))

and copy/drag it as far to the right as you might possibly have columns (i.e.
IV1).


--ron
 
T

Tom

Hey Ron (or anyone else),

To expand on this, I'm trying to fill the column down with the dates for
that month and it not include any days from the same month, the next
month, but for the cell to be blank after the last day of the month.
I've been playing with your formula below, adding DAY.

=IF(D33="","",IF(DATE(YEAR(D33),MONTH(D33),DAY(D33+1))<=$G$3,DATE(YEAR(D33),MONTH(D33),
DAY(D33+1)),""))

D33=the first day of the month from your formula below
G33=the first day of the next month (I have 2 columns in between the
month columns)

Problem is, after I get to the last day of the month, it goes back to
the 1st of the month. I understand why this is happenning in the
formula, but do not know how to get it be blank or not to be the first
day of the next month.

As is:
..
..
..
27-Jul-04 1-Aug-04
28-Jul-04 2-Aug-04
29-Jul-04 .
30-Jul-04 .
31-Jul-04 .
1-Jul-04

Should be:
..
..
..
27-Jul-04 1-Aug-04
28-Jul-04 2-Aug-04
29-Jul-04 .
30-Jul-04 .
31-Jul-04 .

Any help would be appreciated.

Thanks,

Tom
 
Top