# Too Many Options

In this post, I thought it would be interesting to show just how flexible spreadsheets can be.

For a client's project, I had to create a schedule where the number of days shown would change based on the name of the month in the worksheet.

*Image by Erika Wittlieb from Pixabay*

Cell A1 had the name of the month.

Cell B1 had this formula - =MONTH(A1), which returned a digit equal to the month number. So January was 1, February was 2, and so on.

I decided the best approach was to have a formula that would choose months that had 30 days. If the month had 30 days, the formula would return 1 (TRUE). Otherwise, the formula would return 0 (FALSE).

This was the formula I came up with:

=IF(OR(B1=4,B1=6,B1=9,B1=11),1,0)

I thought, however, that maybe there was a better way to write this formula. So I posted to a developers' group, asking for suggested alternatives. Quite a discussion ensued!

More interesting was the different formulas everyone came up with - here are the 9 alternatives that were offered:

= OR ( B1 = { 4, 6, 9, 11 } )

= DAY ( EOMONTH ( B1 ), 0 ) < 31

= HLOOKUP ( B1, { 4, 6, 9, 11 }, TRUE, 0 )

= CHOOSE (B1, 0, 0, 0, 4, 0, 6, 0, 0, 9, 0, 11, 0 )

= CHOOSE ( MONTH ( A1 ), , , , 1 , , 1 , , , 1 , , 1 , )

= MID ( "303232332323", B1, 1 ) < "3" )

= IFERROR ( DATEVALUE ( "31" & A1 ), A1 )

= MONTH ( B1 ) <> MONTH ( DATE ( , MONTH (B1), 31 ) )

= ISODD ( MONTH ( B1) + ( MONTH ( B1 ) > 7 ) )

Which is the best alternative? Well in my case, the first one on the list was a better solution to what I created because it was just easier to understand.

However, depending on what the spreadsheet was designed to do, one of the other formulas may have been a better answer.

Just goes to show you how incredibly flexible spreadsheets can be. No wonder they make so many people crazy!