+ Reply to Thread
Results 1 to 11 of 11

Making month to number formula more concise

  1. #1
    Registered User
    Join Date
    06-03-2015
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    12

    Making month to number formula more concise

    This is the formula I have. I am making a macro including this formula and I don't want to have to type it in manually every time I try to make the macro. Anyone have a way to make this more concise?

    =IF((LEFT(C2,3))="Jan", 1, IF((LEFT(C2,3))="Feb", 2, IF((LEFT(C2,3))="Mar",3, IF((LEFT(C2,3))="Apr", 4, IF((LEFT(C2,3))="May", 5, IF((LEFT(C2,3))="Jun", 6, IF((LEFT(C2,3))="Jul", 7, IF((LEFT(C2,3))="Aug", 8, IF((LEFT(C2,3))="Sep", 9, IF((LEFT(C2,3))="Oct", 10, IF((LEFT(C2,3))="Nov", 11, IF((LEFT(C2,3))="Dec", 12,))))))))))))

  2. #2
    Registered User
    Join Date
    08-17-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: Making month to number formula more concise

    You can use this, just expand for the rest of the months...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-03-2015
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    12

    Re: Making month to number formula more concise

    Ugh that's so much easier. Thanks

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: Making month to number formula more concise

    You're welcome! Please add to my reputation if you feel satisfied with my solution

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Making month to number formula more concise

    ....
    The lookup values need to be in alphabetical order, i.e.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ...etc
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Making month to number formula more concise

    Use VLOOKUP

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (Above formula won't work with Copy-paste for your regional settings so look in the file)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-03-2015
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    12

    Re: Making month to number formula more concise

    Using VLOOKUP do the values have to be in alphabetical order?

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Making month to number formula more concise

    Try this one. It will work in any order

    =INDEX({1,2,3,4,5,6,7,8,9,10,11,12},MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0))

    Row\Col
    A
    B
    1
    Jan
    1
    2
    Feb
    2
    3
    Mar
    3
    4
    Apr
    4
    5
    May
    5
    6
    Jun
    6
    7
    Jul
    7
    8
    Aug
    8
    9
    Sep
    9
    10
    Oct
    10
    11
    Nov
    11
    12
    Dec
    12


    Or use the shortest method

    =MONTH(1&A1)
    Last edited by AlKey; 06-05-2015 at 02:18 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  9. #9
    Registered User
    Join Date
    06-03-2015
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    12

    Re: Making month to number formula more concise

    Well the dates are formatted as such:

    Jan, 2012
    Dec, 2009
    Jul, 2008
    Etc.

    So that is why I had the LEFT function in there to just grab the three letters on the lefthand side.

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Making month to number formula more concise

    You can still use left with these formulas

    =MONTH(1&LEFT(A1,3))

    Row\Col
    A
    B
    1
    Jan, 2012
    1
    2
    Dec, 2009
    12
    3
    Jul, 2008
    7

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Making month to number formula more concise

    Quote Originally Posted by lbl159 View Post
    Using VLOOKUP do the values have to be in alphabetical order?
    No.
    They can be in any order.
    If no match it will return error (i.e. if you enter some other word, different than in list).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cannot figure out concise what to write if-or formula
    By jam320 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-17-2014, 05:06 PM
  2. making a lookup formula more concise
    By ammartino44 in forum Excel General
    Replies: 3
    Last Post: 02-21-2014, 08:40 PM
  3. Make repetitive IF formula more concise?
    By leaning in forum Excel General
    Replies: 7
    Last Post: 03-18-2011, 05:48 AM
  4. making my data more concise
    By chedges in forum Excel General
    Replies: 2
    Last Post: 02-11-2009, 12:22 PM
  5. a more concise formula
    By jwongsf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2009, 10:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1