Text with link
 

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

Text with link
 

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" wrote in message
...
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

Text with link
 

On Mar 14, 6:25*pm, "Ron Coderre"
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

Regards,
Madiya

Text with link
 

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

Regards,

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

"Madiya" wrote in message
...
On Mar 14, 6:25 pm, "Ron Coderre"
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

Regards,
Madiya