locked
FORMAT(value,"MMM") returning incorrect Month RRS feed

  • Question

  • I'm running Excel 2010 and the PowerPivot for SQL 2008R2 32bit and am getting really weird results from passing =FORMAT(6,"mmm") it seems to return Jan. In fact if I pass in 1 it returns Dec anything >1 it simply returns Jan. I have also tried MMM to no avail.

    Initially I had thought I might be having some issues with the data field I was passing so decided to test against real numbers. The initial formula I had was =FORMAT(MONTH([Create_Date]),"mmm").

    I do have Excel 2013 with PP also enabled on that so a part of me is wondering if PP is having issues co-existing with older/newer versions of Excel?

    I hope someone has some idea on what might be going wrong here or if I am doing something incorrect.

    Thursday, March 21, 2013 2:20 PM

Answers

  • To use a date format (like "MMM" for month name), you need to pass the function a proper date.  Not just an int (unless the int represents the full date, not just the month part).

    Try passing the full date (remove the MONTH() function).  And uppercase MMM is needed for month name abbreviated.  More details on MSDN here.

    =FORMAT([Create_Date],"MMM")

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Thursday, March 21, 2013 4:36 PM
    Answerer

All replies

  • To use a date format (like "MMM" for month name), you need to pass the function a proper date.  Not just an int (unless the int represents the full date, not just the month part).

    Try passing the full date (remove the MONTH() function).  And uppercase MMM is needed for month name abbreviated.  More details on MSDN here.

    =FORMAT([Create_Date],"MMM")

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Thursday, March 21, 2013 4:36 PM
    Answerer
  • *Blush* Yes it looks like I had not RTFM properly! Sorry to have asked such a silly question, I shall find a nice dark hole now to hide in.
    Thursday, March 21, 2013 8:16 PM
  • No worries Vivy.  Glad to help.  That's what the forums are for, so no need to apologize.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Thursday, March 21, 2013 10:09 PM
    Answerer
  • Hi there

    I am using Excel 2013 with the PowerPivot for my tables. I seem to have the same issue as VivyG, however your formula solution does not seem to give me my correct answer...either I am being really blond (which I'm not) or I am doing something wrong.

    I have a table with data in PowerPivot & a column called 'Expected Close Date', I have inserted a new calculated column to extract the month from the 'Expected Close Date', which is e.g. '2014-01-31'. The formula used in the calculated column is :=FORMAT(ClosingOpps[Expected Close Date], "MM"), this returns the full date as in the 'Expected Close Date' column. Then I use this formula instead: =FORMAT(MONTH([Expected Close Date]),"mmmm"), perfect, it gives the name of the month, however, the incorrect month, 'December'. Throughout the table it gives every month name previous to the one in the 'Expected Close Date' column.

    I have tried everything & I can not seem to fix it. This is for my reports for my directors...

    Any help will be greatly appreciated.

    Thanks

    Thursday, February 6, 2014 2:51 PM