Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month as Number (97 SR2)

    HI all

    I have a spreadsheet that evaluates a text field that has the month in it. Using Hlookup I find the budgeted amount for that month.

    The budget has now changed. I want to keep the original budget, so need to add a new sheet with the new budget.

    I now need to say if month => May then use new sheet, else use old sheet.

    Formula Month() requires the day of the month and the year. I only have the month.

    Is there another way to achieve this without if A1 = January then X else if A1=February then X else if ....

    ????

    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Month as Number (97 SR2)

    Do you want to create a worksheet formula, or are you writing a macro in VBA?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month as Number (97 SR2)

    If the month is in the cell as text, then the following formula will give you the month number:

    <pre>=MONTH(DATEVALUE("1-"&A1&"-2002"))
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Month as Number (97 SR2)

    Just to add to Legare's answer, if you are looking at periods longer than 12 months, you may want to investigate the use of the (not always documented) =DATEDIF() (which is also a VBA function). Chip Pearson has a good (and amusing) explanation on his website.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month as Number (97 SR2)

    That formula works great thanks!

Posting Permissions

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