View original (French)

[excel] season based on a date?!

jil -  
 kirika -
Hello,

I have an Excel file with a "date" column in the format dd.mm.yyyy
and I would like it to return in a "season" column the season based on my preferences:

if the month is between [December and February] -> winter
if the month is between [June and August] -> summer
if the month is between [September and October] -> autumn

for now I have this but it doesn't give anything: 

=AND(IF(OR(MONTH(C2)=12;MONTH(C2)<=3);"winter";"");IF(OR(MONTH(C2)>=6;MONTH(C2)<=8);"summer";""); IF(OR(MONTH(C2)>=9;MONTH(C2)<=10);"autumn";"error"))


I can't seem to nest my three "if" conditions...

5 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    =CHOOSE(MATCH(MONTH(A1)*100+DAY(A1),{0;320;621;922;1221},1),"winter","spring","summer","autumn","winter")
    with {0;320;621;922;1221} which are your mmdd (month day) season change dates:
    03/20;06/21;....

    Put in the dates that interest you, but you have a strangely placed winter...
    eric

    Never reply to an unsolicited DM...
    Well, that's done.
    1
    1. kirika
       
      Yes, the periods of our reports are named strangely :)
      Thank you for this formula, I was stuck on the SI&!
      Kirika
      0
  2. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
     
    Hello,

    the formula is incorrectly stated. This would surely be better: 

    =IF(OR(MONTH(C2)=12;MONTH(C2)<=3);"winter";IF(OR(MONTH(C2)>=6;MONTH(C2)<=8);"summer";IF(OR(MONTH(C2)>=9;MONTH(C2)<=10);"fall";"error")))

    Best regards.
    --
    This guy wasn't very bright, as he was foolish.
    0
  4. jil
     
    Thank you for your lightning-fast response! :)

    The chossette9 formula seems to work very well.
    Lentzouille2's idea is also good, but unfortunately I am in an absolutely huge database and adding columns is out of the question.

    Then these responses will be useful for others besides me anyway ;)
    0
    1. Lentzouille2 Posted messages 1000 Status Member 39
       
      No need to add columns, just do it in another tab where there's only that ;)

      Anyway, you do as you wish ;) it was just a complementary idea.
      0
    2. jil
       
      which will help me for now because the function only works halfway. I just noticed... it returns "summer" for the month of April (12.04.2011) for example... :S

      EDIT: oh well actually, you need to use AND instead of OR for the last two IFs ;-)
      0
  6. Kirika Posted messages 1 Status Member
     
    Good evening,
    I have the same question, unfortunately my breakdown is not by month but in the middle of the month:
    Winter: between 03/01/2013 and 15/03/2013
    Spring: between 16/03/2013 and 15/05/2013
    etc.
    So the function with the months is no longer relevant, and I haven't managed to find the trick with the extreme dates of each season...
    I'm still searching the internet, if I find anything by then I'll post the answer for others who are looking too!
    Thank you for your response!
    0