The basic method for this solution was provided by DerbyDad03 in response to another, similar, question.
1. Create a two column table of dates in column D and Season names in Column E. To make this work you need Winter to appear twice - see example below. First date (01-January-2009) is in cell D3 and Winter is in cell E3
2. Put a date (in the current year) in cell B3
3. Enter the following formula in cell C3
The spreadsheet looks like this
B C D E
2 Date Season 2009
3 22-Jun-09 Summer 01-Jan-09 Winter
4 20-Mar-09 Spring
5 21-Jun-09 Summer
6 22-Sep-09 Fall
7 21-Dec-09 Winter
In it's simplest form just enter the dates into cells D3 to D7.
Dates must be entered in a way that Excel recognizes as a date. (If cells D3 to D7 respond to different date formatting such as "dd-mm-yy" and "dd-mmm-yyyy", then they are in an Excel date format and this will work).
Note the $ signs in the formula in cell C3. This allows you to drag or move the formula around and it will still refer to the table of dates and seasons. The 2 in the formula is so that the formula returns the information in the second column of the table.
To make this change and work each year, there are two approaches:
1. remove the dependency on year
2. make the table of dates change each year
To do number 2:
In cell D2 enter =YEAR(NOW())
In cell D3 enter =DATE($D$2,1,1)
,1,1) are month and day (January 01)
D4 will be =DATE($D$2,3,20)
and so on down the list.
This way the dates will update for 2010 and subsequent years automatically.