# Excel, transform datas into seasons

Microsoft Office: mac 2008 home and stud...
September 30, 2009 at 02:28:01
Specs: Macintosh
 Hey,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

#1
September 30, 2009 at 05:08:50
 Hi,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 E32. Put a date (in the current year) in cell B33. Enter the following formula in cell C3=VLOOKUP(B3,\$D\$3:\$E\$7,2)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 yearor2. make the table of dates change each yearTo 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.Regards

Report •
Related Solutions