How to Create a Bird Checklist: Part 1 - Assigning Seasons based on Survey Dates (Using Embedded IF Statements)

THE NEED:

I once had 1,800 lines of bird survey data that I wanted to run a report on for a poster I was to present at the New Mexico Ornithological Society annual meeting.  I wanted to show a sort of checklist of birds by season, with data from certain months assigned to each season as follows:

• December though February would be Winter,
• March through May would be Spring,
• June through August would be Summer, and
• September through November would be Fall.

I certainly wasn't going to go through all 1,800 lines of data and enter which date belonged to which season, so I wrote a formula that I could copy and paste into all the rows of data that identified the season for me.  The "checklist" wound up looking like this:

Read on to see how to use embded "IF" statements in Excel to accomplish this.

FORMULA USE:
I introduced Excel 'IF' statements in a previous blog that calculated Shannon-Wiener Diversity Indices.  (Click on the text in the last sentence to learn the basics there).  Embeded 'IF' statements use the same syntax where:


=IF(Condition You Want to Test, "Value" if True, "Value" if False)


But, in this case the output (or "Value") of the conditional test can be another conditional IF statement.  So, the embeded IF statement would read:


=IF(Condition You Want to Test, "Value" if True, Another IF statement to test if False)


Note that the "Value" if True can also be an IF statement.  The decision to make the True or False portion of the statement another IF statement all depends on what you're intending to do.  The best thing to do is to think through "What do I want Excel to do if the first condition is true?  How about false?"  Or another way to think about it is "What can I make Excel do?".


The formula I used to convert dates into seasons of my choosing was:

=IF(MONTH(A2)=1,"Winter",IF(MONTH(A2)=2,"Winter",IF(MONTH(A2)=3,"Spring",IF(MONTH(A2)=4,"Spring",IF(MONTH(A2)=5,"Spring",IF(MONTH(A2)=6,"Summer",IF(MONTH(A2)=7,"Summer",IF(MONTH(A2)=8,"Summer",IF(MONTH(A2)=9,"Fall",IF(MONTH(A2)=10,"Fall",IF(MONTH(A2)=11,"Fall",IF(MONTH(A2)=12,"Winter"))))))))))))

In this case, the date was in Column A as in this diagram:

Wow.  It's easier to read broken down into color-coded parts:

=IF(MONTH(A2)=1,"Winter",
IF(MONTH(A2)=2,"Winter",
IF(MONTH(A2)=3,"Spring",
IF(MONTH(A2)=4,"Spring",
IF(MONTH(A2)=5,"Spring",
IF(MONTH(A2)=6,"Summer",
IF(MONTH(A2)=7,"Summer",
IF(MONTH(A2)=8,"Summer",
IF(MONTH(A2)=9,"Fall",
IF(MONTH(A2)=10,"Fall",
IF(MONTH(A2)=11,"Fall",
IF(MONTH(A2)=12,"Winter"))))))))))))

When coming up with this formula, I asked myself what I wanted to make Excel do.  I wanted Excel to look at the date, identify the month, and insert text if certain conditions were met.  The 'MONTH()' command specifically looks at the month of a date (in this case in cell A2), so is handy to use for this formula. This IF statement reads: "If the month is '1' (January), then enter "Winter", if not, then test whether month is '2'.  If that's true, then enter "Winter" as well, if false, then test whether month is equal to '3'.  If that's true, then enter "Spring", if not..."  And this sequence continues through all the possible values for Month.

A few things to note here:

• The seasons are not based on typical seasonal date changes.  For instance the first day of Summer is the summer solstice, which is June 20th or 21st, and the last day is just before the Autumnal equinox, which is September 22nd or 23rd.  I didn't intend to match dates to these official seasonal definitions, and instead assigned months to seasons based on fairly rough, but functionally useful monthly categories.  There is a way to use official seasonal definitions and can cover that if there are requests.
• Text entries that you want Excel to make have to be in quotes, otherwise, Excel thinks you're trying to enter another formula argument.
• Parenthesis are used to enclose all the components of an IF statement (i.e., 'arguments').  Remember, the original IF() statement has parenthesis and everything you want Excel to read about that IF statement has to be within the parenthesis.  So, keep track of your arguments and remember to put an open parenthesis after the command 'IF' and put a close parenthesis after the true or false arguments.  If you forget to place an end parenthesis, chances are Excel will offer to automatically fix the error for you when you hit the Enter key.
• Notice that this formula does not tell Excel what to do if MONTH(A2)=12 is false. Excel formulas have mandatory components and optional ones.  In an IF statement, it's mandatory to say what you want to test and what you want it to do if it's either true or false, you don't need to provide both true and false options.  If you neglect to provide one of them, it'll return a '0' or 'FALSE' depending on how it's written.
• Another way to do this would be through a LOOKUP() function or VLOOKUP.  I can cover those methods in another blog entry if there are requests.

And that's it!  As always, let me know if you have any comments, questions, or suggestions.