Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. . |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
this is the outcome on this formula..it works but puts it in this format,
formatting does not change or do anything Date Code 10/09 Oct-09 8/07 2007/8//01 11/06 Nov-06 3/08 2008/3//01 12/08 Dec-08 3/09 2009/3//01 12/08 Dec-08 "Héctor Miguel" wrote: 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. . |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
hi, Keddy !
just... decide if the month part... will be allways two digits (as per your previous example: 08/07 = Aug-10) or could it be only one digit (as per example in this post: 8/07 #VALUE!) -???- so you could change/adapt/... the "left" (function part) in proposed formulae consider also *IF* the date-separator will be allways " / " -???- if any doubts (or further information)... would you please comment ? regards, hector. p.s. same to your "next" reply __ OP __ 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 __ previous __ 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to convert mm/yy in text to Dec-07, it adds day & curre
hi (again), Keddy !
try with this changes: op1: =text(20&right(a1,2)&"/"&left(a1,search("/",a1)-1)&"/01","mmm-yy") op2: =date(20&right(a1,2),left(a1,search("/",a1)-1),1) (cell format as date: mmm-yy) hth, hector. __ previous __ just... decide if the month part... will be allways two digits (as per your previous example: 08/07 = Aug-10) or could it be only one digit (as per example in this post: 8/07 #VALUE!) -???- so you could change/adapt/... the "left" (function part) in proposed formulae consider also *IF* the date-separator will be allways " / " -???- if any doubts (or further information)... would you please comment ? regards, hector. p.s. same to your "next" reply __ OP __ 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 __ previous __ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro that adds formula to replace text | Excel Worksheet Functions | |||
Excel 2007 Adds Spaces when Wrap Text is Used | Excel Discussion (Misc queries) | |||
excel adds quotation marks when pasting text | Excel Discussion (Misc queries) | |||
Special custom fill function which adds periods at the end of text | Excel Discussion (Misc queries) | |||
how do i make a hyperlink that adds text typed to the end of it? | Excel Discussion (Misc queries) |