Date format problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

- Butch - 01 May 2008 19:28 GMT

I have several workbooks with date-formatted cells that work properly. I
just created a new one, however, that will not display properly. I have
tried several "date" settings and also the "custom, d-mmm-yy" setting, yet
information placed in the cells come up as a date in "Jan 1900" or as
"###########" with the message that negative dates aren't allowed. I have
tried to use "format painter" from the sheets that work properly but to no
avail. What am I missing?

Thanks in advance.

Reply to this Message

Dave Peterson - 01 May 2008 20:03 GMT

If the value in the cell is negative, then the only way for excel to show that
value as a date is to toggle the 1904 system date setting.

In xl2003, it's under
Tools|Options|Calculation tab|check 1904 date system

But be aware that any date in that workbook has just changed by 4 years plus a
day.

> I have several workbooks with date-formatted cells that work properly. I
> just created a new one, however, that will not display properly. I have
[quoted text clipped - 5 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

- Butch - 01 May 2008 21:00 GMT

No good. Dates I enter in the formula space as "4-26-2008"
automatically has an = sign added to it and displays on the sheet as
"-23-Jul-09" . "4-30-08" changes itself to "=4-30-8" in the formula space
and displays as "-4-Feb-04" . They are both formatted with "d-mmm-yy".
But, a date formatted as a Date and "5-1-2008" entered in the formula space
ads an = sign and displays as "-June 27, 1909". What's with the minus
signs? BTW - I'm using XL2002.

> If the value in the cell is negative, then the only way for excel to show
> that
[quoted text clipped - 20 lines]
>>
>> Thanks in advance.

Reply to this Message

Dave Peterson - 01 May 2008 21:09 GMT

You're not entering dates. You're entering a formula.

4-26-2008
4-26=-22
-22-2008 = -2030

Change this setting:
Tools|Options|transition setting|Uncheck "transition formula entry"

In fact, I'd recommend that you uncheck all those Lotus 123 settings.

> No good. Dates I enter in the formula space as "4-26-2008"
> automatically has an = sign added to it and displays on the sheet as
[quoted text clipped - 32 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message