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.

  • Do you have a column named "Month" in your list? – Ganesh Sanap Oct 30 '18 at 17:56
  • When you enter the formula, do you see a field "Month" in the "Insert Column" options? – harshal gite Oct 30 '18 at 18:18
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, privacy policy and cookie policy

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