IF/Then formula producing error "refers to a column that does not exist"

Question

I tried to follow the instructions I found on the site to get the CY month number associated with the 3 letter month column.

IF([Month]=JAN,"1",IF([Month]=FEB,"2",IF([Month]=MAR,"3",IF([Month]=APR,"4",IF([Month]=MAY,"5",IF([Month]=JUN,"6",IF([Month]=JUL,"7",IF([Month]=AUG,"8",IF([Month]=SEP,"9",IF([Month]=OCT,"10",IF([Month]=NOV,"11",IF([Month]=DEC,"12",0))))))))))))

The error is: The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

Answer 1

Assuming you have a column named "Month", you will need to add quotes around each month name. (They are just text.)

=IF([Month]="JAN","1",
  IF([Month]="FEB","2",
  IF([Month]="MAR","3",
  IF([Month]="APR","4",
  IF([Month]="MAY","5",
  IF([Month]="JUN","6",
  IF([Month]="JUL","7",
  IF([Month]="AUG","8",
  IF([Month]="SEP","9",
  IF([Month]="OCT","10",
  IF([Month]="NOV","11",
  IF([Month]="DEC","12",
  ""
  ))))))))))))

If you want the result as a number, then remove the quotes from the numbers:

=IF([Month]="JAN",1,
  IF([Month]="FEB",2,
  IF([Month]="MAR",3,
  IF([Month]="APR",4,
  IF([Month]="MAY",5,
  IF([Month]="JUN",6,
  IF([Month]="JUL",7,
  IF([Month]="AUG",8,
  IF([Month]="SEP",9,
  IF([Month]="OCT",10,
  IF([Month]="NOV",11,
  IF([Month]="DEC",12,
  0
  ))))))))))))

Here's a much short formula that does the same thing:

=(FIND(UPPER(D2),"   ,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC")-1)/4

(There are three spaces between the quote and the first comma.)

And here's one that will work with any three to nine letter month name:

=(FIND(UPPER(Month),"         ,JANUARY  ,FEBRUARY ,MARCH    ,APRIL    ,MAY      ,JUNE     ,JULY     ,AUGUST   ,SEPTEMBER,OCTOBER  ,NOVEMBER ,DECEMBER ")-1)/10

(All of the spaces are important!)