seasons from dates

[robt123]

Hi

I have a student who wants to type in a date in excel in the format 01/01/2004. He then wants excel to identify the season of the year in another cell depending on the month

Any ideas

Cheers and TIA

Rob

 

[SIXTH SENSE]

Hi!
Take a look at this.
Im sorry for the very rough estimate of the seasons. I have
only two seasons. im not familiar with 4 seasons. but more or less, this should do the trick.

Book1
	A	B	C	D
1	datehere	Season		from
2	1/1/2004	winter		3
3	2/1/2004	winter		6
4	3/1/2004	Spring		9
5	4/1/2004	Spring		12
6	5/1/2004	Spring
7	6/1/2004	Summer
8	7/1/2004	Summer
9	8/1/2004	Summer
10	9/1/2004	autumn
11	10/1/2004	autumn
12	11/1/2004	autumn
13	12/1/2004	winter
Sheet4

 

[robt123]

This is fantastic, how does it work?

 

[SIXTH SENSE]

Hi!
You copy the formula in column B. then type any date in column A. the formula in B will test the month in the column A and write the appropariate season.
this is the formula in B

=IF(OR(MONTH(A2)=12,MONTH(A2)<=2),"winter",IF(AND(MONTH(A2)>=9,MONTH(A2)<=11),"autumn",IF(AND(MONTH(A2)>=3,MONTH(A2)<=5),"Spring",IF(AND(MONTH(A2)>=6,MONTH(A2)<=8),"Summer"))))

 

[Patrick Alexander]

You can create following function

Function season(inputdate)
If (Month(inputdate) < 3) Or _
((Month(inputdate) = 3 And Day(inputdate) < 21)) Or _
((Month(inputdate) = 12 And Day(inputdate) > 20)) Then
season = "winter"
End If
If ((Month(inputdate) > 2 And Day(inputdate) > 20)) Or _
(Month(inputdate) = 4 Or Month(inputdate)) = 5 Or _
((Month(inputdate) = 6 And Day(inputdate) < 21)) Then
season = "spring"
End If
If ((Month(inputdate) > 6 And Day(inputdate) > 20)) Or _
(Month(inputdate) = 7 Or Month(inputdate)) = 8 Or _
((Month(inputdate) = 9 And Day(inputdate) < 21)) Then
season = "summer"
End If
If ((Month(inputdate) > 9 And Day(inputdate) > 20)) Or _
(Month(inputdate) = 10 Or Month(inputdate)) = 11 Or _
((Month(inputdate) = 12 And Day(inputdate) < 21)) Then
season = "autumn"
End If
End Function

now you have a funtion "season(input)"

 

[eliW]

Just for the fun... take a look on other way...

Please note that thease solutions are of little resolution- namely based on whole month. It is much greater challenge to define sesons based on days e.g. from 21/10 - 25-3 = winter and so on. But it can be done.
Tell us if you need this kind of resolution...

Eli

Book1
	A	B	C	D	E
1	01/01/04	winter		winter
2	01/02/04	winter		spring
3	01/03/04	winter		summer
4	01/04/04	spring		autumn
5	01/05/04	spring
6	01/06/04	summer
7	01/07/04	summer
8	01/08/04	summer
9	01/09/04	summer
10	01/10/04	autumn
11	01/11/04	autumn
12	01/12/04	winter
13
Sheet1