Prev: Convert Date in Text to month and Year
Next: formula question...
From: Excellency on 23 Mar 2010 21:51
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.
>
>
> .
>
From: Excellency on 23 Mar 2010 21:55
Does nothing stays the same value as displayed e.g 10/09

"Bob Phillips" wrote:

> Why don't you just change the cell numberformat (Format>Cells>Custom)?
>
> --
>
> HTH
>
> Bob
>
> "Excellency" <Excellency(a)discussions.microsoft.com> wrote in message
> news:F89F07DB-8EE1-4017-9239-E0757B0051FA(a)microsoft.com...
> >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
>
>
> .
>
From: Excellency on 23 Mar 2010 22:01
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
> <Excellency(a)discussions.microsoft.com> 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
> .
>
From: H�ctor Miguel on 24 Mar 2010 00:40
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.


From: H�ctor Miguel on 24 Mar 2010 00:44
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.


 |  Next  |  Last
Pages: 1 2
Prev: Convert Date in Text to month and Year
Next: formula question...