| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
& in formula changing date format to text ?
I have this formula in a cell
=D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
& in formula changing date format to text ?
Since your cell can't be formatted as a date due to the additional data or
text that is being produced, then the output will be automatically displayed as Text. You could get your desired result with: =MONTH(D12)&"/"&DAY(D12)+1&"/"&YEAR(D12)&" - "&MONTH(D12)&"/"&DAY(D12)+5&"/"&YEAR(D12) HTH, Paul -- "Steve" wrote in message ... I have this formula in a cell =D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
& in formula changing date format to text ?
I believe the & is causing the format to change to the date#'s instead of
the date. You're concatenating TEXT into the formula so the result is a TEXT string which ignores number formatting. Try it like this: =TEXT(D12+1,"m/dd/yy")&" - "&TEXT(D12+5,"m/dd/yy") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in a cell =D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
& in formula changing date format to text ?
Thanks guys. Both worked great.
Much appreciated, Steve "T. Valko" wrote: I believe the & is causing the format to change to the date#'s instead of the date. You're concatenating TEXT into the formula so the result is a TEXT string which ignores number formatting. Try it like this: =TEXT(D12+1,"m/dd/yy")&" - "&TEXT(D12+5,"m/dd/yy") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in a cell =D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
& in formula changing date format to text ?
You're welcome!
-- Biff Microsoft Excel MVP "Steve" wrote in message ... Thanks guys. Both worked great. Much appreciated, Steve "T. Valko" wrote: I believe the & is causing the format to change to the date#'s instead of the date. You're concatenating TEXT into the formula so the result is a TEXT string which ignores number formatting. Try it like this: =TEXT(D12+1,"m/dd/yy")&" - "&TEXT(D12+5,"m/dd/yy") -- Biff Microsoft Excel MVP "Steve" wrote in message ... I have this formula in a cell =D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
& in formula changing date format to text ?
Thanks Paul. I was able to use this formula to compare two fields brought in
with two different formattings. "PCLIVE" wrote: Since your cell can't be formatted as a date due to the additional data or text that is being produced, then the output will be automatically displayed as Text. You could get your desired result with: =MONTH(D12)&"/"&DAY(D12)+1&"/"&YEAR(D12)&" - "&MONTH(D12)&"/"&DAY(D12)+5&"/"&YEAR(D12) HTH, Paul -- "Steve" wrote in message ... I have this formula in a cell =D12+1&" - " &D12+5 It produces this : 39528 - 39532 The cell format in the cell shows as m/dd/yy D12 = 3/20/08 I would like it to produce 3/21/08 - 3/25/08 If I lose everyting in the cell except the D12+1, the result is the correct date. I believe the & is causing the format to change to the date#'s instead of the date. How can I get the cell to produce 3/21/08 - 3/25/08 ? Thanks, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to Change Changing Date Format Data to Text | Excel Discussion (Misc queries) | |||
| Changing text to date format | Excel Discussion (Misc queries) | |||
| changing text to date format | Excel Worksheet Functions | |||
| Formula for changing date format | Excel Worksheet Functions | |||
| How to format date as text without changing appearance? | Excel Discussion (Misc queries) | |||
Linear Mode
