Today, I had a member call and ask how she could convert a list of numbers in excel to the names of a month. Apparently, she was having issues sorting her table by the name of the month and to get around the issue she replaced the names of the month with the number of the month.
After she finished working on her spreadsheet, she had several hundred months that she needed to convert to the month name so that it was easier to read. To make the issue more difficult due to the way she set up her formulas every time she sorted the spreadsheet it messed up her output.
My solution was to introduce her to the text function of excel. The text function returns a value converted to text with a specified format. In plain English, you convert a number to a predetermined text format.
So if I had a list of numbers like below:
To return a list of dates I would put the following formula in cell B1
=text(a1*29,”mmm”) this will return the short-name value Jan
If you want the full name use
=text(a1*29,”mmmm”)
What the formula is doing is multiplying the value of the number in A1 by 29 which is how Excel “sees” January.
If you want to convert a month to its calendar number value then you would use:
=MONTH(1&A1) where A1 is the cell that contains your month name
No comments yet.
Comments RSS TrackBack Identifier URI
Leave a comment
No Comments