viks_mehta
asked on
Retrieving the Month value in Numeric form
Hi,
I have attached a file where I need the Month from another data field. I need the Month to NOT be in Text format. Ideally it should say the Month Name but should have a date in the field too. Eg. - October appears as "October" but when I click on the cell it should show a date too (10/01/16).
Format-question.xlsx
I have attached a file where I need the Month from another data field. I need the Month to NOT be in Text format. Ideally it should say the Month Name but should have a date in the field too. Eg. - October appears as "October" but when I click on the cell it should show a date too (10/01/16).
Format-question.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you're not trying to extract the month number from the date (as in the good examples from itjockey above), but want the actual date value to display as the word "October" or "10-2016", then you just need to apply a custom format to the cell.
"mm-yyyy" will show a two-digit month, a hyphen, then a four-digit year
"mmmm" will show the full month name.
The underlying data will remain unchanged; it will still be a date/time value.
-Glenn
"mm-yyyy" will show a two-digit month, a hyphen, then a four-digit year
"mmmm" will show the full month name.
The underlying data will remain unchanged; it will still be a date/time value.
-Glenn
ASKER
When I use all the solutions above. the month displays as "Jan" instead of "October". Could you please send me the solution in the attached file back to me as just applying your formula does not work for me.
See Attached
Format-question.xlsx
Format-question.xlsx
Use "mmmm" (four m's) to get the full name.
ASKER
itjockey - Please see my request. I need the Month to SAY the NAME of the month. When I try and change the format of the Cell E5 the name keeps getting assigned as "Jan". Why is the Month name defaulting to Jan irrespective of the date?
See my example file. Note that the four example cells have the exact same date-time value as in C3, but use custom formats.
EE-Format-question.xlsx
EE-Format-question.xlsx
Assuming Your Data in Cell C3...
Try This Formula
Try This Formula
=CHOOSE(MONTH(C3),"January","February","March","April","May","June","July","August","September","October","November","December")
itjockey - Please see my request. I need the Month to SAY the NAME of the month. When I try and change the format of the Cell E5 the name keeps getting assigned as "Jan". Why is the Month name defaulting to Jan irrespective of the date?
This is the case then your system date format is different ...i guess that is why you facing the problem ...
just type this in any cell 18oct2016 ---then use =MONTH(Cell Reference) and let us know what is the result ..
i am sleeping now ...i am sure Mr.Glenn will assist you better then me ...
i am sleeping now ...i am sure Mr.Glenn will assist you better then me ...
ASKER
Thanks for all the help itjockey! You have a restful night!
ASKER
Glen, The issue is that I need a formula that can possibly show the Cell Value (C3) which is 10/13/2016 11:02:25 AM to appear as October but I need the actual cell value to be changed to 10/13/16 WITHOUT the time (11:02:25 AM).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
October appears as "October" but when I click on the cell it should show a date too (10/01/16).
I assume you mean in the formula bar at the top of the screen.
All of the above will currently show a formula in the formula bar. when you click on the cell. If you want the actual date to show in the formula bar it will have to be copied and pasted as value.
ASKER
Thanks Glenn! This is exactly what I needed! Much appreciated!
Glad I could help, but was the solution with the formatting ("mmmm") or converting the date-time value to just a date with =INT(C3)
ASKER
It was converting the value to a date. Formatting the date is easy. This works as the actual value is in a date form with the month name.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Split:
-- Glenn Ray (https:#a41849261)
-- itjockey (https:#a41849048)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Split:
-- Glenn Ray (https:#a41849261)
-- itjockey (https:#a41849048)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
frankhelk
Experts-Exchange Cleanup Volunteer
Open in new window
For Moth and year in text formatOpen in new window
For Month and year in date formatOpen in new window