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

1. 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?

2. 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"]

3. 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. 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. Re: Month format issue - convert 9/1/2013 into Sep

Try TEXT function

=TEXT(A2,"mmm")

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

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

Users Browsing this Thread

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

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