software

All,

I need some help sorting out a problem with dates.

In G8: 12/31/1995 (serial value of: 35064)
G9, has the formula: =DATE(YEAR(G8),MONTH(G8) 2,1)-1 and yields #NUM!
(the purpose of the formula is to give me the last day of the next
month)

If I copy these two cells into a new workbook, I get the desired,
1/31/1996.

What could be causing this erratic behavior with my excel? The
workbook that I am modifying was created originally in Excel '97

Thanks!

JonathanHi!

Not sure why you're getting #NUM!.

The formula works for me. It can be written as:

=DATE(YEAR(G8),MONTH(G8) 2,0)

But either way it returns the correct date. Even if G8 was a TEXT date it
would still work so I can't see why you'd get a #NUM! error.

Biff

gt; wrote in message oups.com...
gt; All,
gt;
gt; I need some help sorting out a problem with dates.
gt;
gt; In G8: 12/31/1995 (serial value of: 35064)
gt; G9, has the formula: =DATE(YEAR(G8),MONTH(G8) 2,1)-1 and yields #NUM!
gt; (the purpose of the formula is to give me the last day of the next
gt; month)
gt;
gt; If I copy these two cells into a new workbook, I get the desired,
gt; 1/31/1996.
gt;
gt; What could be causing this erratic behavior with my excel? The
gt; workbook that I am modifying was created originally in Excel '97
gt;
gt; Thanks!
gt;
gt; Jonathan
gt;


wrote...
....
gt;In G8: 12/31/1995 (serial value of: 35064)
gt;G9, has the formula: =DATE(YEAR(G8),MONTH(G8) 2,1)-1 and yields #NUM!
gt;(the purpose of the formula is to give me the last day of the next
gt;month)
gt;
gt;If I copy these two cells into a new workbook, I get the desired,
gt;1/31/1996.
gt;
gt;What could be causing this erratic behavior with my excel? The
gt;workbook that I am modifying was created originally in Excel '97

Tools gt; Options, select the Transition tab, uncheck 'Transition formula
evaluation'. Since [123] transition settings are workbook-specific, you
could copy the date constant and the formula returning #NUM! into
another workbook with this setting disabled, and the formula would
work, returning 1/31/1996 as expected.Toolsgt;optionsgt;transition, de-select transition formula evaluation, a long
time I ago I rebuilt a time sheet that was originally created in Lotus for a
company , I wanted to make it work by just changing the year in one cell so
I built a lot of formulas using the date function and I got this error, I
almost climbed the walls before I checked the transition options, the idea
came when I saw other formulas preceded with instead of =

--

Regards,

Peo Sjoblom

nwexcelsolutions.com
gt; wrote in message oups.com...
gt; All,
gt;
gt; I need some help sorting out a problem with dates.
gt;
gt; In G8: 12/31/1995 (serial value of: 35064)
gt; G9, has the formula: =DATE(YEAR(G8),MONTH(G8) 2,1)-1 and yields #NUM!
gt; (the purpose of the formula is to give me the last day of the next
gt; month)
gt;
gt; If I copy these two cells into a new workbook, I get the desired,
gt; 1/31/1996.
gt;
gt; What could be causing this erratic behavior with my excel? The
gt; workbook that I am modifying was created originally in Excel '97
gt;
gt; Thanks!
gt;
gt; Jonathan
gt;
Thanks guys!

That did it -- wow I thought I was going insane. You all were ar great
help.

Does unselecting the transition option have any adverse affects?

Thanks again,

JonathanNo

--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;jonathanquot; gt; wrote in message ups.com...
gt; Thanks guys!
gt;
gt; That did it -- wow I thought I was going insane. You all were ar great
gt; help.
gt;
gt; Does unselecting the transition option have any adverse affects?
gt;
gt; Thanks again,
gt;
gt; Jonathan
gt;