Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop automation to date field
I have formatted cell A1 to be Custom category, type dd-mm. Then I typed in
1-12 and cell A1 displays 01-12. However, in the formula (fx) bar, it shows 01/12/2006. I don't want A1 to contain /2006 as I want to concatenate this with another cell containing the text "/2007" and other year values. How can I do this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop automation to date field
Hi Melissa
One way to avoid this would be to format the cell as Text (in which case what you type is what you get) - you'll then be able to perform concatentaions as required. The problem that you've observed results from Excel interpreting what you type in as a date in the current year, and to use "ddmm" formatting you need to have a valid date (hence, Excel is being 'helpful' in this respect). Hope this helps! Richard Melissa wrote: I have formatted cell A1 to be Custom category, type dd-mm. Then I typed in 1-12 and cell A1 displays 01-12. However, in the formula (fx) bar, it shows 01/12/2006. I don't want A1 to contain /2006 as I want to concatenate this with another cell containing the text "/2007" and other year values. How can I do this? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop automation to date field
Hi Richard,
the problem with formatting as Text is that I can't do a fill-series for the whole month... sigh... isn't there a way to just turn off the automation? "RichardSchollar" wrote: Hi Melissa One way to avoid this would be to format the cell as Text (in which case what you type is what you get) - you'll then be able to perform concatentaions as required. The problem that you've observed results from Excel interpreting what you type in as a date in the current year, and to use "ddmm" formatting you need to have a valid date (hence, Excel is being 'helpful' in this respect). Hope this helps! Richard Melissa wrote: I have formatted cell A1 to be Custom category, type dd-mm. Then I typed in 1-12 and cell A1 displays 01-12. However, in the formula (fx) bar, it shows 01/12/2006. I don't want A1 to contain /2006 as I want to concatenate this with another cell containing the text "/2007" and other year values. How can I do this? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop automation to date field
Enter it as say
=ROW(A1)&"-12" This will allow you to fill-down nicely. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Melissa" wrote in message ... Hi Richard, the problem with formatting as Text is that I can't do a fill-series for the whole month... sigh... isn't there a way to just turn off the automation? "RichardSchollar" wrote: Hi Melissa One way to avoid this would be to format the cell as Text (in which case what you type is what you get) - you'll then be able to perform concatentaions as required. The problem that you've observed results from Excel interpreting what you type in as a date in the current year, and to use "ddmm" formatting you need to have a valid date (hence, Excel is being 'helpful' in this respect). Hope this helps! Richard Melissa wrote: I have formatted cell A1 to be Custom category, type dd-mm. Then I typed in 1-12 and cell A1 displays 01-12. However, in the formula (fx) bar, it shows 01/12/2006. I don't want A1 to contain /2006 as I want to concatenate this with another cell containing the text "/2007" and other year values. How can I do this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date STOP Accumulator | Excel Discussion (Misc queries) | |||
sorting on a date field | Excel Worksheet Functions | |||
Converting a date field into a month-year only field | Excel Discussion (Misc queries) | |||
How can you strip the time of day out of a date field | Excel Discussion (Misc queries) | |||
Using a date field in mail merge | Excel Discussion (Misc queries) |