| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Joining dates and text in formula
Hi,
I have a simple formula which is joins 2 dates. To demonstrate Cell D17 = 1/1/07 Cell E17 = 8/1/07 When I join them using the formula "=D17&" to "&E17" the date formats aren't retained and I get the answer "39083 to 39090". The cell has date format set. How do I get the cell to return "1st Jan 07 to 8th Jan 07"? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Joining dates and text in formula
Use the text function:
=TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy") but notice no "st" or "rd", etc. -- Gary's Student gsnu200703 "RJS" wrote: Hi, I have a simple formula which is joins 2 dates. To demonstrate Cell D17 = 1/1/07 Cell E17 = 8/1/07 When I join them using the formula "=D17&" to "&E17" the date formats aren't retained and I get the answer "39083 to 39090". The cell has date format set. How do I get the cell to return "1st Jan 07 to 8th Jan 07"? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Joining dates and text in formula
Thanks v. much.
Gary''s Student wrote: Use the text function: =TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy") but notice no "st" or "rd", etc. -- Gary's Student gsnu200703 "RJS" wrote: Hi, I have a simple formula which is joins 2 dates. To demonstrate Cell D17 = 1/1/07 Cell E17 = 8/1/07 When I join them using the formula "=D17&" to "&E17" the date formats aren't retained and I get the answer "39083 to 39090". The cell has date format set. How do I get the cell to return "1st Jan 07 to 8th Jan 07"? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Joining dates and text in formula
Just a late note here.
If you did want the "st" or "rd" eyc. you could employ a user defined function to provide those. Post back if interested. Gord Dibben MS Excel MVP On 1 Feb 2007 01:48:36 -0800, "RJS" wrote: Thanks v. much. Gary''s Student wrote: Use the text function: =TEXT(D17,"d mmm yy") & " to " & TEXT(E17,"d mmm yy") but notice no "st" or "rd", etc. -- Gary's Student gsnu200703 "RJS" wrote: Hi, I have a simple formula which is joins 2 dates. To demonstrate Cell D17 = 1/1/07 Cell E17 = 8/1/07 When I join them using the formula "=D17&" to "&E17" the date formats aren't retained and I get the answer "39083 to 39090". The cell has date format set. How do I get the cell to return "1st Jan 07 to 8th Jan 07"? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format text 'dates' to real dates | Excel Worksheet Functions | |||
| Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
| COUNTIF formula multiple text exclusions | Excel Discussion (Misc queries) | |||
| Text Colour Within Formula | Excel Discussion (Misc queries) | |||
| dates and text | Excel Discussion (Misc queries) | |||
Linear Mode
