Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Show Month Name (Excel 2000 >)

    I have a list in excel of a couple of thousand records. I have a date of sale as one of the columns. I am currently using =Month(C2) to pull the month of sale for a pivot table. Is there an easy way of showing the month name....eg, =Format(Month(C2), "MMM"). Its an effort to use IF or a Vlookup table!!
    Tx
    Regards,
    Rudi

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

    Re: Show Month Name (Excel 2000 >)

    You can group dates in a pivot table by month: click any of the date values in the pivot table and select Data | Group and Outline | Group....

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show Month Name (Excel 2000 >)

    How about a mini function to do the job...
    Is the code sound???

    <pre>Function ShowMonth(DateToConvert As Date) As String
    On Error GoTo EH
    Dim lngMonth As Long
    lngMonth = Month(DateToConvert)
    Select Case lngMonth
    Case 1
    ShowMonth = "Jan"
    Case 2
    ShowMonth = "Feb"
    Case 3
    ShowMonth = "Mar"
    Case 4
    ShowMonth = "Apr"
    Case 5
    ShowMonth = "May"
    Case 6
    ShowMonth = "Jun"
    Case 7
    ShowMonth = "Jul"
    Case 8
    ShowMonth = "Aug"
    Case 9
    ShowMonth = "Sep"
    Case 10
    ShowMonth = "Oct"
    Case 11
    ShowMonth = "Nov"
    Case 12
    ShowMonth = "Dec"
    End Select
    Exit Function

    EH:
    MsgBox Err.Description
    End Function
    </pre>

    Regards,
    Rudi

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

    Re: Show Month Name (Excel 2000 >)

    I don't understand why you need a function for this. Pivot tables have the capability to group by month built-in. If you prefer to use a formula: use the simple formula =TEXT(C2,"mmm") in another column and fill down as far as needed.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show Month Name (Excel 2000 >)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>.....Thats the function to use to format to a specific format... similar to =Format in Access..

    I am aware of the grouping feature in Pivot Tables, but I'm just trying to be inventive here!! If a pivot is running, you still need to create the groups for all 12 months. With the function, you add it to the end of the list and autofill, then you have an automatic field that shows month without having to create the groups in the first place!

    PS: I am not a lazy person by nature....
    Tx for TEXT function...forgot about that one!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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