Prev: Need help with macro for conditional formatting using number forma
Next: combo box
From: Excellency on 22 Mar 2010 19:29
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: H�ctor Miguel on 22 Mar 2010 20:41
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: Bob Phillips on 23 Mar 2010 05:37
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: Ron Rosenfeld on 23 Mar 2010 06:58
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
 | 
Pages: 1
Prev: Need help with macro for conditional formatting using number forma
Next: combo box