Number Days In Month Formula Not Working

[Greg]

Hi,

I have searched around a bit and found Chip's formla for determining
the number of days in a month:

=DAY(DATE(YEAR(A6),MONTH(A6)+1,0))

My problem is that I can't get it to work on my existing monster
spreadsheet. I point it to a cell that has the date in the following
format:

=date(2004,1,1) and I get a #NUM! error. If I open a blank worksheet,
enter the date in the same format the formula works.

One more thing, when entering dates directly into my existing workbook
eg. 1/1/2004 they will not be seen as dates. Excel sees the / as
division and adds the = before the date and displays the product of
the division versus converting to a date.

On my new blank worksheet where I get the formula to work dates can be
entered in as dates eg. 1/1/2004.

I have compared the settings on the options tab, but have not found a
reason for the problem.

Much thanks in advance for some help.

Greg

 

[Harlan Grove]

I have searched around a bit and found Chip's formla for determining
the number of days in a month:

=DAY(DATE(YEAR(A6),MONTH(A6)+1,0))

My problem is that I can't get it to work on my existing monster
spreadsheet. I point it to a cell that has the date in the following
format:

=date(2004,1,1) and I get a #NUM! error. If I open a blank worksheet,
enter the date in the same format the formula works.

One more thing, when entering dates directly into my existing workbook
eg. 1/1/2004 they will not be seen as dates. Excel sees the / as
division and adds the = before the date and displays the product of
the division versus converting to a date.

On my new blank worksheet where I get the formula to work dates can be
entered in as dates eg. 1/1/2004.

I have compared the settings on the options tab, but have not found a
reason for the problem.

...

You didn't dig deep enough into the Options dislog. Go to the Transition tab and
uncheck 'Transition formula evaluation' and 'Transition formula entry'. What you
describe can only be caused by 'transition' functionality.

 

[Greg]

Thanks I obviously missed that option. Works like a charm now.