Converting dates to year/season

Hi There!

I have a spreadsheet of 55,000 rows each of which contains a date in the format dd/mm/yyyy and I would like to create a column which contains yyyy/season. So far I can get excel to convert a cell into a season given the relevant date but I can't figure out how to append the year in front of the season. This is what I'm using to convert month to collelating season: =IF(MONTH(B2)<2,"WINTER",IF(MONTH(B2)<5,"SPRING",IF(MONTH(B2)<8,"SUMMER",IF(MONTH(B2)<11,"AUTUMN",IF(MONTH(B2)<=12,"WINTER","NONE")))))


Thanks!!

Nat

Please Login or Register  to view this content.

For some reason, when I hit Save, the formula is changed from all uppercase to all lowercase. I have no idea why, but if you want your strings to be uppercase you can just add this immediately after your "="

Please Login or Register  to view this content.

Biodiversity,

In cell C2 enter (and copy down):

=YEAR(B2)&"/"&IF(MONTH(B2)<2,"WINTER",IF(MONTH(B2)<5,"SPRING",IF(MONTH(B2)<8,"SUMMER",IF(MONTH(B2)<11,"AUTUMN",IF(MONTH(B2)<=12,"WINTER","NONE")))))

You could get the Season with a LOOKUP, i.e.

=LOOKUP(MONTH(A2),{1,2,5,8,11;"Winter","Spring","Summer","Autumn","Winter"})

What do you want to show for the year in winter? Is Nov and Dec 2010 and Jan 2011 all shown as 2010 or is it just the calendar year?

Assuming that all seasons start on 21st month except autumn which starts on 23rd then you can use this formula for any date in any year

=LOOKUP(A2,DATE(YEAR(A2),{0,3,6,9,12},{21,21,21,23,21}),{"winter","spring","summer","autumn","winter"})

......or are the season start dates variable by year?

Dumy, unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.