A useful trick to learn in Excel is how to convert a date such as 01/01/2014 into a month name, such as January or ‘Jan’. Read on to find out how to convert a date to a month name in Excel 2010…
Converting a date into a month name is an extremely easy thing to do in Excel, but like everything it is only easy if you know how!
There are a lot of reasons why you might have a need to convert a date into a month name and one of the most common ones is for charting purposes. For example you might be given a table of monthly sales where the date field has been stored like so:
If you chart directly from this table then you will end up a rather amateurish looking Excel chart, something like this:
As you can see the dates across the bottom axis (X Axis) are taking up a lot of room and they could also easily confuse the end user, is this monthly sales or do they just relate to one day of the month?
Fortunately fixing this problem is quick and simple.
The Text Function
The Text function in Excel can be used to format values, it takes the form:
This is where Value is the field to convert (in this example the date) and Format is the format Excel should convert it to (in this example a month name).
To convert the date into month name the format “MMMM” is the one to use as this is how Excel will interpret a month name:
As the above table shows the formula =TEXT references the date cell as the value then tells Excel that the preferred format is “MMMM”, the result is the month name in full.
From the new table you can build a chart that looks smarter and also one that does not confuse the end user:
It is possible to take things a step further, the full month name is not really necessary in a chart like this. ‘Jan’ would suffice instead of January, ‘Feb’ instead of February and so on. To do this a quick change in the format type is all that is required.
All that needs to be changed in the formula is take this:
Then amend to this:
All that has been done is one of the M’s have been removed from the format type so now this will show a three letter month name which is the more professional way to display a chart of this nature:
Even more formats!
There are a wide variety of format types that can be utilised on date fields, some of the main ones that are useful are:
- “MMMM” : The full month name, i.e. January
- “MMM” : The three letter month name, i.e. Jan
- “MM”: The numerical month, i.e. January would be 01
- “MMM-YY” : The three letter month name and year separated by a dash, i.e. Jan-14
- “MMM/YY”: The three letter month name and year separated by a slash, i.e. Jan/14
- “DD” : The day, i.e. if the value is the 15th then this will show 15
- “DDD”: The day of the week in three letter format, i.e. Wed
- “DDDD”: The day of the week in full format, i.e. Wednesday
- “YYYY”: The year in full, i.e. 2014
With just the handful of format types above you can create just about any date format you want by combining them, how about getting really crazy and doing something like:
Ok a crazy format like that has no place in a chart but the important knowledge to takeaway is that just by knowing a few of the key format types you can convert a date in Excel into anything you like.