Trying to convert mm/yy in text to Dec-07, it adds day & current y

[Excellency]

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

 

[Héctor Miguel]

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.

 

[Bob Phillips]

Why don't you just change the cell numberformat (Format>Cells>Custom)?

 

[Ron Rosenfeld]

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

 

[Excellency]

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

 

[Excellency]

Does nothing stays the same value as displayed e.g 10/09

 

[Excellency]

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

 

[Héctor Miguel]

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.

 

[Héctor Miguel]

hi (again), 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.

 

[Excellency]

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

 

[Excellency]

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]

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 __

 

[Héctor Miguel]

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 __