Text with link

[Madiya]

I have date in cell a1 with dd-mmm-yy format.
On the same sheet, I have another cell with "Allocation for " & A1
The result gives me Allocation for 39512
Required result is Allocation for 05-Mar-08

I dont understand why excel is unhappy with my format of dd-mmm-yy

Any ideas?

Regards,
Madiya

 

[Ron Coderre]

To Excel, dates are represented by the
number of days the date is from 31-DEC-1899.

01-JAN-1900 is 1
11-MAR-2008 is 39,521

When you format a cell as date, Excel is only *displaying*
the number in a format that humans can understand is a date.

So, you'll need to convert the number to text.

Try this:
="Allocation for "&TEXT(A1,"dd-mmm-yy")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Madiya]

To Excel, dates are represented by the
number of days the date is from 31-DEC-1899.

01-JAN-1900 is 1
11-MAR-2008 is 39,521

When you format a cell as date, Excel is only *displaying*
the number in a format that humans can understand is a date.

So, you'll need to convert the number to text.

Try this:
="Allocation for "&TEXT(A1,"dd-mmm-yy")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)









- Show quoted text -

Perfact.
Thanks.
I was trying with format and date instead of text.

Regards,
Madiya

 

[Ron Coderre]

You're welcome.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

To Excel, dates are represented by the
number of days the date is from 31-DEC-1899.

01-JAN-1900 is 1
11-MAR-2008 is 39,521

When you format a cell as date, Excel is only *displaying*
the number in a format that humans can understand is a date.

So, you'll need to convert the number to text.

Try this:
="Allocation for "&TEXT(A1,"dd-mmm-yy")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)









- Show quoted text -

Perfact.
Thanks.
I was trying with format and date instead of text.

Regards,
Madiya