Excel, transform datas into seasons

Microsoft Office: mac 2008 home and stud...
September 30, 2009 at 02:28:01
Specs: Macintosh

I have a list of dates, and would like to transform them
into seasons (0=Winter, 1=Spring, 2=Summer, 3=Fall)

do you know how to do this?

See More: Excel, transform datas into seasons

Report •

September 30, 2009 at 05:08:50

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.


Report •
Related Solutions

Ask Question