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

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)

"curly_lox" wrote in message
...
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

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")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"curly_lox" wrote:
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

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

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

Still accepting any other alternatives!!!

Thanks Max

"Max" wrote:

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")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"curly_lox" wrote:
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

welcome, check out responses by Bob & Roger!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"curly_lox" wrote in message
...
Hmm I guess that is a step closer to what I'm after.

Still accepting any other alternatives!!!

Thanks Max

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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

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
--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

thank you for this!!

"Roger Govier" wrote:

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
--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

You're very welcome Emma. Thanks for the feedback.

--
Regards

Roger Govier


"curly_lox" wrote in message
...
thank you for this!!

"Roger Govier" wrote:

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
--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

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.

"Roger Govier" wrote:

You're very welcome Emma. Thanks for the feedback.

--
Regards

Roger Govier


"curly_lox" wrote in message
...
thank you for this!!

"Roger Govier" wrote:

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
--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

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

--
Regards

Roger Govier


"curly_lox" wrote in message
...
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.

"Roger Govier" wrote:

You're very welcome Emma. Thanks for the feedback.

--
Regards

Roger Govier


"curly_lox" wrote in message
...
thank you for this!!

"Roger Govier" wrote:

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
--
Regards

Roger Govier


"curly_lox" wrote in message
...
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

"Roger Govier" wrote:

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

--
Regards

Roger Govier


"curly_lox" wrote in
message
...
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