So I’m teaching this Dashboarding class with Jon Peltier, when he mentions that if you format your dates with 5 m’s (“mmmmm”) you get the first letter of the month name.
What?! Do you know how long I’ve been typing (J, F, M, A, M, J, J, A, S, O, N, D)? It’s beyond me how I never found this nifty trick.
Here’s the deal:
If you go to the Format Cells Dialog box and Click on the Custom option, you can enter a custom syntax.
Entering mmmmm formats your dates so that they only show the first letter of the month name.
Here’s what happens to a date when applying m formatting.
So what happens if you decide to be an ass and enter 10 m’s? Excel defaults to showing the entire month name.
Being an Access guy, I decided to try this in Access. Here’s what happen in Access.
Not only does Access not give you the first letter of the month, but apparently, Access isn’t smart enough to stop formatting if the user gets spastic with the m’s.
How about it? Am I the last one to know about the 5 m trick?
RELATED STUFF
News to me! Wow, this is why I love your blog, always something new and useful and tricksie…thanks for keeping it up!
Great tip! Didn’t know about it either.
New to me too.
Great tip. New to me. Wish we had the money to have been at you camp this week. Would love to see a blog post from you or Jon about it.
I didn’t know that, either.
So I guess it’s official: Peltier is the smartest man in the whole world.
I stumbled on it a few months ago in Allen Wyatt’s custom formatting e-book, but oddly his corresponding blog post doesn’t list it (http://excelribbon.tips.net/Pages/T008319_Understanding_Date_and_Time_Formatting_Codes.html)
Very cool trick.
News to me. Although, I use Access more than I use Excel. So it is not a surprise.
That formatting also works with the worksheet TEXT function as well…
=TEXT(A1,”mmmmm”)
However, it does NOT work with the VB Format function.
Okay, just fooling around I found some other “odd” formatting codes (I’ll show them in quotes to make them stand out, but don’t use quotes when formatting a cell with them).
1. Using “aaa” seems to do the same thing as “ddd” and “aaaa” seems to do the same thing as “dddd”.
2. Using “bbb” or “bbbb” returns what looks like a year value that is 543 years ahead of the year in the cell (our 1900 is equivalent to 2443 and this year is equivalent to 2553). Also, using “b” or “bb” returns the last two digits of this “year value”.
3. Using “e” seems to do the same thing as “yyy” or “yyyy”.
4. Using “g” seems to make the date display as the empty string.
Okay, just fooling around I found some other “odd” formatting codes (I’ll show them in quotes to make them stand out, but don’t use quotes when formatting a cell with them).
1. Using “aaa” seems to do the same thing as “ddd” and “aaaa” seems to do the same thing as “dddd”.
2. Using “bbb” or “bbbb” returns what looks like a year value that is 543 years ahead of the year in the cell (our 1900 is equivalent to 2443 and this year is equivalent to 2553). Also, using “b” or “bb” returns the last two digits of this “year value”.
3. Using “e” seems to do the same thing as “yyy” or “yyyy”.
4. Using “g” seems to make the date display as the empty string.
These codes, like the “mmmmm” which I pointed out in my previous message, work as described within the worksheet’s TEXT function.
new to me.. thanks for the mmmmm
New to me but why use it?
If you format to display only the first letter of the month name, won’t that lead to confusion: J could be January, June or July.
How is a user to know which is the correct month?
MMMMM… This is tasty. And useful too.
Gr8,,
Great tip! Which has traveled to good old Europe now
I tried it in my German version of Excel. It works as well and I will surely mention it in my training courses.
But in the German version you have to distinguish between (upper-case) M for month and (lower-case) for minute.
awsome
Awesome, great for some microcharts. Thanks Jon and Thanks Mike!
That’s cool.
On the date formatting, I would like to share something that I use.
If you add “b2″ in front of any date format, that would convert the date to Hijri (arabic) calendar date like “b2dd/mm/yyyy.”.
To add to Rick Rothstein’s finding, formatting a date like dd/mm/bbbb, would convert the date to budhist calendar which is 544 years ahead of gregorian calendar