Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
What do you mean "convert"?
Are you trying to retain the entire date but change the way it is displayed or are you trying to extract the month and put it in a cell by itself?
0 
While changing the way the date is displayed is my ultimate goal, extracting the month number, i.e. 5 = May...11=Nov from another column that I hide within the spreadsheet will also solve my problem. I am learning more about Vlookup and this excel function looks like a promising approach.
0
To redesign the way your date’s display you have all the choices offered
in the Date section of cell formatting or you can “roll your own” using a Custom Format.But for now try this:
With your Date in A1 try this in cell B1:
=TEXT(A1,”MMMMM”)
for the first letter of the month=TEXT(A1,”MMMM”)
for the month spelled out completely=TEXT(A1,”MMM”)
for the 3 letter abbreviation of monthIf you have a cell with just a number from 1 thru 12 you are going to
have to use some type of =IF function, or VBA code to match up the
number with the correct Month.
0
re: If you have a cell with just a number from 1 thru 12...
... you can use CHOOSE()
=CHOOSE(A1,"Jan","Feb","Mar","Apr","May", etc.)
You can also use CHOOSE with MONTH() if you have dates:
=CHOOSE(MONTH(D1),"Jan","Feb","Mar","Apr","May", etc.)
0
Forgot about =CHOOSE
But it does make for a rather long formula, especially if you spell out the complete month name.
Not that I’m against long formulas, but I do prefer “short & sweet”.
0
Guys, Thanks a lot! The =Choose is the solution that I just tried and it works like a champ. One thing is for sure, there is more capability within excel than I will ever begin to tap into.
0
If the CHOOSE option is too long for you, please show me how your IF suggestion would be “short & sweet”.
0
The IF option was too long for my taste, that's why I opted for the TEXT solution, which you must admit, is a bit shorter than the CHOOSE solution.
0
Then why did you say "If you have a cell with just a number from 1 thru 12 you are going to have to use some type of =IF function..."
0
In the first post, it was unclear if the contents of the cells in question had a date or simply a number.
If the Cells had only a number then the only option, that came to mind, was an =IF function, but that would have required two statements combined.
A daunting task for someone not accustomed to writing them.I totally forgot about the =CHOOSE function.
When he replied with:
“While changing the way the date is displayed is my ultimate goal, extracting the month number, i.e. 5 = May...11=Nov from another column that I hide within the spreadsheet will also solve my problem.”
That then opened the field to include dates.
My first thought was to use the =MONTH function and a Custom Format of MMMM to get the month spelled out, but that does not work.
I then considered a =TEXT(MONTH(A1),”MMMM”) solution but opted for the shorter and simpler =TEXT(A1,”MMMM”)
that I suggested.
0
re: My first thought was to use the =MONTH function and a Custom Format of MMMM to get the month spelled out, but that does not work.
Works for me in 2003 Pro - all three formats mmm, mmmm & mmmmm
BTW...I was really offering CHOOSE just for the list of numbers, 1 - 12. The CHOOSE(MONTH(A1) was simply to show that many Excel functions can be combined.
0
How did you get =MONTH to work?
In Column A, starting at A1 thru A13 I input he numbers 1 thru 13
In column B, I formatted cells B1 thru B13 as Custom Format, MMMM
I then entered the formula =MONTH(A1) in cell B1,
I then copied the formula down from B1 to B13 and all I got was January in every cell in column B.The =MONTH function always required a Date to work.
Even then the custom format did not convert the number.
0
Sorry, last post it should read:
In Column A, starting at A1 thru A12 I input the Dates 1/1/07 thru 12/1/07.
In column B, I formatted cells B1 thru B12 as Custom Format, MMMM
0
You're right.
I must have tried a date in January, got January after the custom format, and assumed it worked for the other months.
I think what is happening is that as soon as you custom format the cell to be mmm Excel considers the cell to be a date_serial. Therefore, it sees 1 as 1/1/1900, 2 as 1/2/1900, etc. (Format those cells as dates and that's what you'll get.)
Since MONTH will always return 1 - 12, we'll always get January if we format with the m's.
0
Well okay, the =TEXT(DATE(2000,A1,1),"mmm") works perfectly and is much shorter than the =choose approach.
I greatly appreciate all the discussions here.
0  |
 how do I start Office 200...
|
Creating a Time card in E...
|
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
