Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Darlene Niebruegge
 
Posts: n/a
Default 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.
  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

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.



  #3   Report Post  
widman
 
Posts: n/a
Default

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.




  #4   Report Post  
Darlene Niebruegge
 
Posts: n/a
Default

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.




  #5   Report Post  
David McRitchie
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel keeps converting text to date format John T via OfficeKB.com Excel Discussion (Misc queries) 4 September 12th 05 06:48 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 10:20 PM
date format within a cell containing a formula Brushie76 Excel Discussion (Misc queries) 1 January 22nd 05 04:50 AM
Formula with text and reference to a date cell [email protected] Excel Discussion (Misc queries) 1 January 11th 05 09:15 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 05:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"