List the months between 2 dates

C

curly_lox

I really need to find a function/formula which will show me the NAMES of
months which fall between 2 dates.

Ie 1/1/2007 to 31/3/2007 should show me Jan, Feb, Mar.

I do not want a count of months between 2 dates but what months fall between
the 2 dates.

PLEASE HELP!!!
Emma
 
B

Bob Phillips

Select 12 cells and enter

=TEXT(DATE(YEAR(TODAY()),ROW(INDIRECT(MONTH(A1)&":"&MONTH(A1)+DATEDIF(A1,A2,"m"))),1),"mmm")

as a block array formula.

It will show #N/A for months not in the range


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Max

Perhaps one close alternative which might be acceptable ?

Assuming the earlier date is in A2, later date in B2 (real dates, of course)
then in say, C2:
=TEXT(A2,"mmm")&" - "&TEXT(B2,"mmm")
 
R

Roger Govier

Hi Emma

With your start date in A1 and end Date in B1 enter in D1
=TEXT(A1,"mmm")
and in E1
=IF(DATE(YEAR($A1),MONTH($A1)+COLUMN(A1),1)<$B1,
TEXT(DATE(YEAR($A1),MONTH($A1)+COLUMN(A1),1),"mmm"),"")
copy across through F1:O1
 
C

curly_lox

Hmm I guess that is a step closer to what I'm after.

Still accepting any other alternatives!!!

Thanks Max
 
C

curly_lox

Roger this looks really good and on practising it in a new worksheet it
worked perfectly. I already have this giant spreadsheet up though so when I
go to put in the formula and change which cells its referring to it shows a
blank.

The Start Month is in Y2, The End Month is in Z2, and the Text Start Month
is in U2. I'm guessing that the (1) in the formula is whats throwing it out
slightly
 
R

Roger Govier

Hi Emma

Assuming the three cells in which you want the month names to show are
U2, V2 and W2
U2
=TEXT(Y2,"mmm")
V2
=IF(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1)<$Z2,
TEXT(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1),"mmm"),"")
copy across to W2
 
C

curly_lox

thank you for this!!

Roger Govier said:
Hi Emma

Assuming the three cells in which you want the month names to show are
U2, V2 and W2
U2
=TEXT(Y2,"mmm")
V2
=IF(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1)<$Z2,
TEXT(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1),"mmm"),"")
copy across to W2
Click to expand...
 
C

curly_lox

Roger I need your help again lol

Ok so I gave up yesterday as I thought I was having a stroke from all the
concentration put into this formula

Let me explain more -

I have a spreadsheet with roughly 20,000 rows of information - each row has
a start date, and end date and I need to produce 12 or more columns which
will show what months fall between the 2 dates (which your formula does when
I copy and paste it into a new spreadsheet but doesn't when I put it into my
original work)

There is other random information like name etc in Columns A:E, and I have
inserted 13 columns (G:S) so that the formula can be copied/dragged and
pasted there.
In column U is the Month (text format)
Column V:X is random info
Column Y is the start date
Column Z is the End date

Ideally I would like this formula to go in column G

I'm sorry for being a moron.
 
R

Roger Govier

Hi Emma

No probs.
Insert in G2
=TEXT($Y2,"mmm")
in cell H2
=IF(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1)<$Z2,
TEXT(DATE(YEAR($Y2),MONTH($Y2)+COLUMN(A1),1),"mmm"),"")

Drag across through I2:R2
Select G2:H2 down the page, by just double clicking on the fill handle
at the bottom right of H2 after you have selected.

COLUMN(A1) returns the number 1 (because it is the first column). The 1
as part of A1 has no affect at all.
As you copy across, it will change to COLUMN(B1), C1 etc. and return 2
through to 12.
That number is adding to the Month value from cell Y2 to create a new
date, the final 1 in the formula is making the date the 1st of each
month.
MONTH($Y2) will return the month number for that date, YEAR($Y2)
returns the Year number
Date, takes 3 parameters
DATE(year,month,day)
so DATE(2007,1,1) returns 01 Jan 2007 and
TEXT(DATE(2007,1,1),"mmm") will return Jan. If we used "mmmm" it would
return January.

Hopefully it will be a little clearer now as to how it all fits together
 
Top