?
Solved

Retrieving the Month value in Numeric form

Posted on 2016-10-18
19
Medium Priority
?
48 Views
Last Modified: 2016-11-21
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
0
Comment
Question by:viks_mehta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
  • +2
19 Comments
 
LVL 8

Assisted Solution

by:Naresh Patel
Naresh Patel earned 1000 total points
ID: 41849048
try this
=MONTH(C3)

Open in new window

0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41849057
Or Your Existing Formula
=TEXT(C3,"mm")

Open in new window

For Moth and year in text format
=TEXT(C3,"mm/YYYY")

Open in new window

For Month and year in date format
=MONTH(C3)&"/"&YEAR(C3)

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41849122
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.
EE-CustomFormat2.png
"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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:viks_mehta
ID: 41849145
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.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41849154
See Attached
Format-question.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41849160
Use "mmmm" (four m's) to get the full name.
1
 

Author Comment

by:viks_mehta
ID: 41849187
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?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41849206
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
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41849208
Assuming Your Data in Cell C3...

Try This Formula
=CHOOSE(MONTH(C3),"January","February","March","April","May","June","July","August","September","October","November","December")

Open in new window

0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41849213
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 ...
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41849214
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 ...
0
 

Author Comment

by:viks_mehta
ID: 41849232
Thanks for all the help itjockey! You have a restful night!
0
 

Author Comment

by:viks_mehta
ID: 41849239
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).
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1000 total points
ID: 41849261
If you just want the date value, the formula would be =INT(C3) and then you'd apply the custom format "mmmm" that I demonstrated earlier.  See the attached workbook.
EE-Format-question.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41849826
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.
0
 

Author Comment

by:viks_mehta
ID: 41850561
Thanks Glenn! This is exactly what I needed! Much appreciated!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41850656
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)
0
 

Author Comment

by:viks_mehta
ID: 41850802
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.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41895620
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question