If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & current y
I have a cell that has mm/yy = 07/09 , I want it converted to display
Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. Thanks |
#2
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & current y
hi, !
I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. try: =text(datevalue(a1),"mmm-yy") or (for international issues): =text(datevalue(a1),"mmm-")&year(datevalue(a1)) hth, hector. |
#3
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & current y
Why don't you just change the cell numberformat (FormatCellsCustom)?
-- HTH Bob "Excellency" wrote in message ... I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. Thanks |
#4
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & current y
On Mon, 22 Mar 2010 16:29:01 -0700, Excellency
wrote: I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. Thanks I think that what you think is in the cell is not really in the cell. How was the value 07/09 entered into the cell? What is your "short-date" setting in Control Panel/Regional Settings? If, for example, you entered manually 07/09 into some cell, depending on your regional settings, Excel will interpret that as either mm/dd or dd/mm and then add the current year. (It does this in an attempt to be "helpful"). Formatting will not change what is in the cell; nor will it change Excel's behavior in parsing the entry. If you explain more completely exactly what it is you are doing, I'm sure someone can develop an appropriate solution. --ron |
#5
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
Hector
I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 10/09 = Oct - 10 08/07 = Aug-10 11/06 = Nov-10 thanks Keddy "Héctor Miguel" wrote: hi, ! I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. try: =text(datevalue(a1),"mmm-yy") or (for international issues): =text(datevalue(a1),"mmm-")&year(datevalue(a1)) hth, hector. . |
#6
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
Does nothing stays the same value as displayed e.g 10/09
"Bob Phillips" wrote: Why don't you just change the cell numberformat (FormatCellsCustom)? -- HTH Bob "Excellency" wrote in message ... I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. Thanks . |
#7
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
The setting in the control panel is 03/23/2010. It is a spreadsheet I
recieved from someone and they just manually entered 10/09 as month and year nothing unique, when look at the format it displays as text, instead of date or numbers. Any formula i try to get the Dec - 09 or Dec-2009 it does not display the year but defaults to current year 2010..hope this is sufficient information thanks "Ron Rosenfeld" wrote: On Mon, 22 Mar 2010 16:29:01 -0700, Excellency wrote: I have a cell that has mm/yy = 07/09 , I want it converted to display Jul-2009 or Jul-09. But when I use DATEVALUE or format cell, it displays 09/07/2010 and does not let me display just MM:YY. Thanks I think that what you think is in the cell is not really in the cell. How was the value 07/09 entered into the cell? What is your "short-date" setting in Control Panel/Regional Settings? If, for example, you entered manually 07/09 into some cell, depending on your regional settings, Excel will interpret that as either mm/dd or dd/mm and then add the current year. (It does this in an attempt to be "helpful"). Formatting will not change what is in the cell; nor will it change Excel's behavior in parsing the entry. If you explain more completely exactly what it is you are doing, I'm sure someone can develop an appropriate solution. --ron . |
#8
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
hi, Keddy !
I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 10/09 = Oct - 10 08/07 = Aug-10 11/06 = Nov-10 thanks Keddy try this one: =text(20&right(a1,2)&"/"&left(a1,2)&"/01","mmm-yy") hth, hector. |
#9
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
hi (again), Keddy !
I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 10/09 = Oct - 10 08/07 = Aug-10 11/06 = Nov-10 thanks Keddy also this one: =date(20&right(a1,2),left(a1,2),1) and apply a custom number format (i.e.) mmm-yy hth, hector. |
#10
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
This is the outcome..any suggestions Date Code 10/09 Oct-09 8/07 #VALUE! 11/06 Nov-06 3/08 #VALUE! 12/08 Dec-08 3/09 #VALUE! 12/08 Dec-08 "Héctor Miguel" wrote: hi (again), Keddy ! I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 10/09 = Oct - 10 08/07 = Aug-10 11/06 = Nov-10 thanks Keddy also this one: =date(20&right(a1,2),left(a1,2),1) and apply a custom number format (i.e.) mmm-yy hth, hector. . |
|
Thread Tools | |
Display Modes | |
|
|