Dates, Years and Seasons

[Graham Haughs]

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)),"Spring",4)

Kind regards
Graham Haughs
Turriff, Scotland

 

[Don Guillett]

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

 

[Guest]

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

 

[Guest]

I think I mis-read your post. Don's formula is a more accurate one.

 

[Graham Haughs]

Thanks for that Don.

Graham

 

[Guest]

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","spring","spring","summer","summer","summer","fall","fall","fall","winter")

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

 

[Guest]

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]

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

Graham