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!

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!

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!

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!