+ Reply to Thread
Results 1 to 6 of 6

Month format issue - convert 9/1/2013 into Sep

  1. #1
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,377

    Month format issue - convert 9/1/2013 into Sep

    Hello friends, this should be an easy one for someone but I have been having an issue with it for a while. I have a column of "normalized" dates that are each month / 1 / year so each is date formatted and 1/1/2013, 2/1/2013 etc. I want to use a simple month formula for how I use the data in graphs so I'd like to use =MONTH(A2) to return Sep in the attached example but for some reason it keeps giving me Jan. It appears in col B that it is returning the correct # but in col C (pointed it at col B and col A) it is returning J and same in col D. The differences between each of these columns is how I tried to format them. Check the formatting on them and you'll see.

    Currently I'm using a lookup formula to a reference table as a workaround but it isn't what I'd like to do.
    Thoughts?

    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Month format issue - convert 9/1/2013 into Sep

    You don't need MONTH function, make C2

    =A2

    ...and the same in D2

    format as required

    [when you use MONTH(A2) the result is a number between 1 and 12 but these are interpreted as dates 1-Jan 1900 to 12th Jan 1900, all of which are in January hence "J" or "Jan"]
    Audere est facere

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,377

    Re: Month format issue - convert 9/1/2013 into Sep

    That on the surface seems to work but the problem is, I have a rolling 18 months of data that always has overlaps in the months - 2 Januarys or 2 Marchs etc and I produce several bar graphs that include 12 month graph showing the overlapping months side by side (ie. April 2012 beside April 2013). So I need it to actually be Apr but when I did that it was still being counted as either April 2012 and April 2013. The bar graph was no longer side by side, just an 18 month straight line.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,377

    Re: Month format issue - convert 9/1/2013 into Sep

    Apparently this works and I can drop the hlookup I was using though it isn't very elegant, was hoping for something simplier.
    =IF(MONTH(A2)=1,"Jan",IF(MONTH(A2)=2,"Feb",IF(MONTH(A2)=3,"Mar",IF(MONTH(A2)=4,"Apr",IF(MONTH(A2)=5,"May",IF(MONTH(A2)=6,"Jun",IF(MONTH(A2)=7,"Jul",IF(MONTH(A2)=8,"Aug",IF(MONTH(A2)=9,"Sep",IF(MONTH(A2)=10,"Oct",IF(MONTH(A2)=11,"Nov","Dec")))))))))))
    Thanks daddylonglegs for trying.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Month format issue - convert 9/1/2013 into Sep

    Try TEXT function

    =TEXT(A2,"mmm")

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.54 for Mac (home)
    Posts
    7,377

    Re: Month format issue - convert 9/1/2013 into Sep

    That worked too! And much simplier! Great, and thanks again!

+ 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. [SOLVED] Pivot Table Not Grouping by Month for Latest Month (groups > 7/20/2013)
    By justforthis1 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-04-2013, 12:28 PM
  2. Replies: 10
    Last Post: 08-01-2013, 11:40 AM
  3. Replies: 2
    Last Post: 11-04-2012, 04:57 AM
  4. Convert date into month format not working
    By arlu1201 in forum Excel General
    Replies: 4
    Last Post: 11-08-2011, 09:28 AM
  5. Excel 2007 : Excel date format issue (convert?)
    By Chris.db in forum Excel General
    Replies: 1
    Last Post: 06-08-2011, 11:33 PM

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