Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel month name to number

Posted on 2000-05-14
13
Medium Priority
?
3,710 Views
Last Modified: 2009-12-21
Hi..I have an Excel sheet with month in 3 letters..Jan  Feb ...Jul  etc
I wish to put the month number like
"01" for Jan and "12" for Dec etc in
a new column. What is a neat way to do this?
leo
0
Comment
Question by:leojl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 2807950
Hi leojl,

Well discarding the VBA way, which would be a bit smoother :O)

A kludge, excel seems to have problems putting more then 8 if then else nested formulas into a cell

so bit of a working workaround....

+in column A got the jan>dec values
+in column B i copied this formula into each cell 1 > 12

=IF(A1="Jan";"01";IF(A1="Feb";"02";IF(A1="Mrt";"03";IF(A1="Apr";"04";IF(A1="May";"05";IF(A1="Jun";"06";""))))))

+in column C i copied down this formula

=IF(A7="jul";"07";IF(A7="aug";"08";IF(A7="sep";"09";IF(A7="oct";"10";IF(A7="nov";"11";IF(A7="dec";"12";""))))))

+in column D i copied this one

=IF(B1<>"";B1;C1)

+which should give you the result you want.

you can make column B and C invisible with ... column > hide so you're left with 2 columns.

HTH:O)Bruintje
0
 
LVL 3

Author Comment

by:leojl
ID: 2807965
hi bruintje,
thanks for the idea,what is the neater VBA way to do it please. leo
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2807981
OK,

took a shower and decided to make this anyway :O)


+go ALT+F11
+insert a new module
+paste this code

Public Function NameToNumber(ByRef cName As String) As String

  Select Case UCase(cName)
    Case Is = "JAN"
      NameToNumber = "01"
    Case Is = "FEB"
      NameToNumber = "02"
    Case Is = "MRT"
      NameToNumber = "03"
    Case Is = "APR"
      NameToNumber = "04"
    Case Is = "MAY"
      NameToNumber = "05"
    Case Is = "JUN"
      NameToNumber = "06"
    Case Is = "JUL"
      NameToNumber = "07"
    Case Is = "AUG"
      NameToNumber = "08"
    Case Is = "SEP"
      NameToNumber = "09"
    Case Is = "OCT"
      NameToNumber = "10"
    Case Is = "NOV"
      NameToNumber = "11"
    Case Is = "DEC"
      NameToNumber = "12"
    Case Is = ""
      NameToNumber = ""
  End Select
   
End Function

+back to the sheet
+choose insert function
+choose user defined
+then you can use your new function
+just click on the cell to evaluate with the "jan" in it...
+click ok and voila

HTH:O)Bruintje
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 2807985
MONTH(DATEVALUE("1 Feb 1")) will return what you want.
As you Excel cell contains only the month name, you can do like this (replace A1 by your cell reference):

=MONTH(DATEVALUE("1 " & A1 & " 1"))
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2807993
looked at those date functions, but i guess it doesn't work with a string....
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2807996
ah well if you translate that it should work :P
0
 
LVL 9

Expert Comment

by:antrat
ID: 2808037
Hi leojl

I would change your months to dates and then format them as mmm to get "Jan", "Feb" etc then you can perform many types of time and date functions on your month names. To do this try this.

supposing your first month "Jan" is in cell A1 and "Dec" is in A12.

in cell A1 type 1/1/2000 then with the cell still selected go to Format>Cells>Number and select "Custom" now using any one of the pre-defined formats as a starting point type mmm

Now with A1 still selected LEFT click on the "fill handle" (little black square on the bottom right of the cell)and drag down to cell A12, now release the left mouse button and select "fill months".

Now in cell B1 type =MONTH(A1) and then simply copy this down.

The big advantage is you can now return many different types of aspects from you Months.


antrat
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2808039
It does work, i tried it before posting :-)

Actually, the Date Functions rely on regional settings, so there may araise some problems...
0
 
LVL 9

Expert Comment

by:antrat
ID: 2808040
Oops sorry leojl

I should have said RIGHT mouse click for the fill drag.

antrat
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2808041
As you wanted "01", i complete my suggestion:
=TEXT(MONTH(DATEVALUE("1 " & A1 & " 1"));"00")
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2808060
angelIII > it does indeed, had to translate the functions first....:O)
0
 
LVL 9

Expert Comment

by:antrat
ID: 2808232
Ok I just spotted you wanted 01 for jan etc. In this case simply custom format your numeric month cells as 00


antrat
0
 
LVL 3

Author Comment

by:leojl
ID: 2808820
I am overwhelmed by the outstanding solutions to my question.I did select
=MONTH(DATEVALUE("1 " & A1 & " 1"))
because it is so neat and easy to use.
But I did learn so much from the other
solutions. many thanks leo
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question