0

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.

2
  • Do you have a column named "Month" in your list? Oct 30, 2018 at 17:56
  • When you enter the formula, do you see a field "Month" in the "Insert Column" options? Oct 30, 2018 at 18:18

1 Answer 1

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!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.