Dates, Years and Seasons
 

I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below
which will identify the Spring, Summer, Autumn, Winter each of which is
defined as falling between particular dates. I could just expand the
formula below to accommodate this. However the problem is I want the
formula to operate over many years., eg if B4 is 1/2/2007 I want it
to show Spring but obviously this formula cannot do this as it specifies
the year. Is there anyway to create it so that it is just looking
between 1st February and 31st March in any year, regardless of the year
entry in B4. Grateful for any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

=if(and(month(b4)=1,month(b4)<=3)

--
Don Guillett
SalesAid Software

"Graham Haughs" wrote in message
...
I will have various dates entered in B4 for example which will include the
year, ie dd/mm/yyyy format. I want a formula like the one below which will
identify the Spring, Summer, Autumn, Winter each of which is defined as
falling between particular dates. I could just expand the formula below to
accommodate this. However the problem is I want the formula to operate
over many years., eg if B4 is 1/2/2007 I want it to show Spring but
obviously this formula cannot do this as it specifies the year. Is there
anyway to create it so that it is just looking between 1st February and
31st March in any year, regardless of the year entry in B4. Grateful for
any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

If your definition of spring is 2/1/2007 through 3/31/2007 (using American
date format) then, assuming the dates in question are in column A,

=IF(OR(MONTH(A1)=2,MONTH(A1)=3),"Spring","Another season")

This will remain true for all subsequent and previous years, if, as above,
you define "spring" as being between February 1st and March 31st. You can do
a similar formula for other seasons, depending on how you define them.

Dave
--
Brevity is the soul of wit.


"Graham Haughs" wrote:

I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below
which will identify the Spring, Summer, Autumn, Winter each of which is
defined as falling between particular dates. I could just expand the
formula below to accommodate this. However the problem is I want the
formula to operate over many years., eg if B4 is 1/2/2007 I want it
to show Spring but obviously this formula cannot do this as it specifies
the year. Is there anyway to create it so that it is just looking
between 1st February and 31st March in any year, regardless of the year
entry in B4. Grateful for any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

I think I mis-read your post. Don's formula is a more accurate one.
--
Brevity is the soul of wit.


"Dave F" wrote:

If your definition of spring is 2/1/2007 through 3/31/2007 (using American
date format) then, assuming the dates in question are in column A,

=IF(OR(MONTH(A1)=2,MONTH(A1)=3),"Spring","Another season")

This will remain true for all subsequent and previous years, if, as above,
you define "spring" as being between February 1st and March 31st. You can do
a similar formula for other seasons, depending on how you define them.

Dave
--
Brevity is the soul of wit.


"Graham Haughs" wrote:

I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below
which will identify the Spring, Summer, Autumn, Winter each of which is
defined as falling between particular dates. I could just expand the
formula below to accommodate this. However the problem is I want the
formula to operate over many years., eg if B4 is 1/2/2007 I want it
to show Spring but obviously this formula cannot do this as it specifies
the year. Is there anyway to create it so that it is just looking
between 1st February and 31st March in any year, regardless of the year
entry in B4. Grateful for any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

Thanks for that Don.

Graham

Don Guillett wrote:
=if(and(month(b4)=1,month(b4)<=3)

Dates, Years and Seasons
 

If you follow weather conventions:

Dec, Jan, Feb - winter
Mar, Apr, May - spring
Jun, Jul, Aug - summer
Sep, Oct, Nov - fall


then:

=CHOOSE(MONTH(B4),"winter","winter","spring","spri ng","spring","summer","summer","summer","fall","fa ll","fall","winter")

may be of value. You can adjust the list if, for example, you want Feb to
be spring.
--
Gary's Student


"Graham Haughs" wrote:

I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below
which will identify the Spring, Summer, Autumn, Winter each of which is
defined as falling between particular dates. I could just expand the
formula below to accommodate this. However the problem is I want the
formula to operate over many years., eg if B4 is 1/2/2007 I want it
to show Spring but obviously this formula cannot do this as it specifies
the year. Is there anyway to create it so that it is just looking
between 1st February and 31st March in any year, regardless of the year
entry in B4. Grateful for any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

This might be a way to assign any date to a season:

Where (in my fake world) the seasons are ,
Dec-Feb Winter
Mar-May Spring
Jun-Aug Summer
Sep-Nov Autumn

The beginning of each season is coded as the MonthNum and the Day.
So
Autumn begins September first: 901,
Winter begins December first: 1201,
etc

For a date in B4

This formula returns the season:
=LOOKUP(--TEXT(N(B4),"mdd"),{0,101,301,601,901,1201},{"no
date","Winter","Spring","Summer","Autumn","Winter" })

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Graham Haughs" wrote:

I will have various dates entered in B4 for example which will include
the year, ie dd/mm/yyyy format. I want a formula like the one below
which will identify the Spring, Summer, Autumn, Winter each of which is
defined as falling between particular dates. I could just expand the
formula below to accommodate this. However the problem is I want the
formula to operate over many years., eg if B4 is 1/2/2007 I want it
to show Spring but obviously this formula cannot do this as it specifies
the year. Is there anyway to create it so that it is just looking
between 1st February and 31st March in any year, regardless of the year
entry in B4. Grateful for any guidance.

=IF(AND(B4=DATE(2006,2,1),B4<=DATE(2006,3,31)),"S pring",4)

Kind regards
Graham Haughs
Turriff, Scotland

Dates, Years and Seasons
 

Nice one, thanks again to all. By the way I think your "Fall" is far
better terminology than our "Autumn"

Graham

Gary''s Student wrote:
If you follow weather conventions:

Dec, Jan, Feb - winter
Mar, Apr, May - spring
Jun, Jul, Aug - summer
Sep, Oct, Nov - fall


then:

=CHOOSE(MONTH(B4),"winter","winter","spring","spri ng","spring","summer","summer","summer","fall","fa ll","fall","winter")

may be of value. You can adjust the list if, for example, you want Feb to
be spring.