How to format cell with text and date.
 

We are trying to set up a custom format for a column of cells that contain a
letter, a hyphen, and a date. The letter is variable; it can change from one
row to another (e.g., L-09/28/05, A-09/29/05, etc.). Is it possible to
create a custom format for something like this?

Thanks for your help and advice.

Hi Darlene,
Since your prefix can change from one row to another, I think you will need
to use a TEXT Worksheet Function instead of a custom cell format.

F2: =LEFT(E2,1) & "-" & Text(A2,"mm/dd/yy)

If you have a choice I would really suggest a more readable
date yyyy-mm-dd so there is no confusion and you can sort on it
as text.

If you had the same letter prefix all the way then would be able to
use the value of the cell as a date, and a custom format (format, cells, cuxstom) like
"L-"yyyy-mm-dd or "L-"mm/dd/yy

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Darlene Niebruegge" wrote in message
...
We are trying to set up a custom format for a column of cells that contain a
letter, a hyphen, and a date. The letter is variable; it can change from one
row to another (e.g., L-09/28/05, A-09/29/05, etc.). Is it possible to
create a custom format for something like this?

Thanks for your help and advice.

I use the concoctenate function, actually breaking the date apart first, so
that I can substitute spanish for the month names and then bring it all
together with the name of the city; So what the customer sees on his quote
is
"Santa Cruz, 28 de Septiembre 2005" which is actually:
="Santa Cruz, "&J64&L64&M64&" "&N64


"David McRitchie" wrote:

Hi Darlene,
Since your prefix can change from one row to another, I think you will need
to use a TEXT Worksheet Function instead of a custom cell format.

F2: =LEFT(E2,1) & "-" & Text(A2,"mm/dd/yy)

If you have a choice I would really suggest a more readable
date yyyy-mm-dd so there is no confusion and you can sort on it
as text.

If you had the same letter prefix all the way then would be able to
use the value of the cell as a date, and a custom format (format, cells, cuxstom) like
"L-"yyyy-mm-dd or "L-"mm/dd/yy

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Darlene Niebruegge" wrote in message
...
We are trying to set up a custom format for a column of cells that contain a
letter, a hyphen, and a date. The letter is variable; it can change from one
row to another (e.g., L-09/28/05, A-09/29/05, etc.). Is it possible to
create a custom format for something like this?

Thanks for your help and advice.

Thanks to both of you for your help and suggestions.

Darlene

"widman" wrote:

I use the concoctenate function, actually breaking the date apart first, so
that I can substitute spanish for the month names and then bring it all
together with the name of the city; So what the customer sees on his quote
is
"Santa Cruz, 28 de Septiembre 2005" which is actually:
="Santa Cruz, "&J64&L64&M64&" "&N64


"David McRitchie" wrote:

Hi Darlene,
Since your prefix can change from one row to another, I think you will need
to use a TEXT Worksheet Function instead of a custom cell format.

F2: =LEFT(E2,1) & "-" & Text(A2,"mm/dd/yy)

If you have a choice I would really suggest a more readable
date yyyy-mm-dd so there is no confusion and you can sort on it
as text.

If you had the same letter prefix all the way then would be able to
use the value of the cell as a date, and a custom format (format, cells, cuxstom) like
"L-"yyyy-mm-dd or "L-"mm/dd/yy

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Darlene Niebruegge" wrote in message
...
We are trying to set up a custom format for a column of cells that contain a
letter, a hyphen, and a date. The letter is variable; it can change from one
row to another (e.g., L-09/28/05, A-09/29/05, etc.). Is it possible to
create a custom format for something like this?

Thanks for your help and advice.

You'e welcome, I failed to mention how you could sort your
worksheets: http://www.mvps.org/dmcritchie/excel...#sortallsheets

"Darlene Niebruegge" wrote///
Thanks to both of you for your help and suggestions.