I want to calculate a daily interest rate from an annual rate. I have input
the following formula into a cell labelled "Days in the Current Year" :
"=IF(OR(MOD(TODAY(),400)=0,AND(MOD(TODAY(),4)=0,MO D(TODAY(),100)<0)),366,
365)

This, I think, is supposed to return 366 if the IF condition is True,
otherwise it returns 365. This number is then used in other calculations.

However, the reverse seems to happen. For example, 2008 (as the current year
and taken from the "Today" formula I had assumed) should have returned A
"True" for the IF statement and hence 366. But it returns 365.

Obviously doing something wrong! Is my assumption that the Today statement
weill return the current year correct? Is the IF statement correct?

Many thanks for any advice.

TODAY() is measured in days. Try replacing it by YEAR(TODAY())
--
David Biddulph

"ARGT" wrote in message
...
I want to calculate a daily interest rate from an annual rate. I have input
the following formula into a cell labelled "Days in the Current Year" :
"=IF(OR(MOD(TODAY(),400)=0,AND(MOD(TODAY(),4)=0,MO D(TODAY(),100)<0)),366,
365)

This, I think, is supposed to return 366 if the IF condition is True,
otherwise it returns 365. This number is then used in other calculations.

However, the reverse seems to happen. For example, 2008 (as the current
year
and taken from the "Today" formula I had assumed) should have returned A
"True" for the IF statement and hence 366. But it returns 365.

Obviously doing something wrong! Is my assumption that the Today statement
weill return the current year correct? Is the IF statement correct?

Many thanks for any advice.

Today() returns today's date not the year

yr IF statement says:

if ((today's date/4 gives the rest of 0 AND today's date/100 gives the
rest < 0) OR today's date/400 gives the rest of 0) then the result is
366

otherwise it is 365

=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,366,365)

is a better formula I think

The reason for your formula failing is that TODAY() returns a caount to the
number of days not the year number:

=IF(OR(MOD(YEAR(TODAY()),400)=0,AND(MOD(YEAR(TODAY ()),4)=0,MOD(YEAR(TODAY()),100)<0)),366,
365)

But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ARGT" wrote in message
...
I want to calculate a daily interest rate from an annual rate. I have input
the following formula into a cell labelled "Days in the Current Year" :
"=IF(OR(MOD(TODAY(),400)=0,AND(MOD(TODAY(),4)=0,MO D(TODAY(),100)<0)),366,
365)

This, I think, is supposed to return 366 if the IF condition is True,
otherwise it returns 365. This number is then used in other calculations.

However, the reverse seems to happen. For example, 2008 (as the current
year
and taken from the "Today" formula I had assumed) should have returned A
"True" for the IF statement and hence 366. But it returns 365.

Obviously doing something wrong! Is my assumption that the Today statement
weill return the current year correct? Is the IF statement correct?

Many thanks for any advice.

Simpler, but wrong for 1900
(but as Excel didn't exist in 1900, it would only be a problem if TODAY()
were replaced in the formula by a date input or if you changed your
computer's date to 1900).
--
David Biddulph

"Jarek Kujawa" wrote in message
...
=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,366,365)

is a better formula I think

thks David

But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

I came up with a similar idea, just expressed differently...

=365+ISNUMBER(MONTH(--(YEAR(TODAY())&"-02-29")))

Same number of function calls, but yours avoids the concatenation, so I'm
guessing yours is more efficient.

Rick

Actually, I take that back... at one function call less, I think this one is
more efficient...

=365+ISNUMBER(--(YEAR(TODAY())&"-02-29"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

I came up with a similar idea, just expressed differently...

=365+ISNUMBER(MONTH(--(YEAR(TODAY())&"-02-29")))

Same number of function calls, but yours avoids the concatenation, so I'm
guessing yours is more efficient.

Rick

And, generalizing this, here is the formula that returns TRUE if a given
year is a leap year...

=ISNUMBER(--(A1&"-02-29"))

where A1 contains a year value (2000, 2007, 2008, etc.). If A1 contains a
date, then that date is in a leap year if this formula returns TRUE...

=ISNUMBER(--(YEAR(A1)&"-02-29"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Actually, I take that back... at one function call less, I think this one
is more efficient...

=365+ISNUMBER(--(YEAR(TODAY())&"-02-29"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

I came up with a similar idea, just expressed differently...

=365+ISNUMBER(MONTH(--(YEAR(TODAY())&"-02-29")))

Same number of function calls, but yours avoids the concatenation, so I'm
guessing yours is more efficient.

Rick

Rick,
yr last formula is excellent to me

Caveat - don't try to use years before 1901.

Pete

On Jun 30, 11:00*am, "Rick Rothstein \(MVP - VB\)"
wrote:
And, generalizing this, here is the formula that returns TRUE if a given
year is a leap year...

=ISNUMBER(--(A1&"-02-29"))

where A1 contains a year value (2000, 2007, 2008, etc.). If A1 contains a
date, then that date is in a leap year if this formula returns TRUE...

=ISNUMBER(--(YEAR(A1)&"-02-29"))

Rick

"Rick Rothstein (MVP - VB)" wrote in
. ..



Actually, I take that back... at one function call less, I think this one
is more efficient...

=365+ISNUMBER(--(YEAR(TODAY())&"-02-29"))

Rick

"Rick Rothstein (MVP - VB)" wrote in
l...
But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

I came up with a similar idea, just expressed differently...

=365+ISNUMBER(MONTH(--(YEAR(TODAY())&"-02-29")))

Same number of function calls, but yours avoids the concatenation, so I'm
guessing yours is more efficient.

Rick- Hide quoted text -

- Show quoted text -

Wow... I just took a break for dinner and all the answers came back! Many
thanks to everyone.

It seems that the "equation" sort of evolved, through your on-line
interaction while I was having dinner, to the final one supplied by Jarek.
Is this the reality? Replacement of my formula with the final one
incorporated in Jarek's reply certainly does the trick for the current year.
I assume it will return 365 next year. Can you explain how it works, ie the
reasoning behind the IF statement?



"Jarek Kujawa" wrote:

=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,366,365)

is a better formula I think

Hi Rick
=ISNUMBER(--(A1&"-02-29"))
where A1 contains a year value (2000, 2007, 2008, etc.).

A nice solution, with the caveat that numbers 1 through 28 entered in cell
A1, will give a false TRUE as Excel will "helpfully?" treat these as 01 Feb
2029 through 28 Feb 2009
--
Regards
Roger Govier

"Rick Rothstein (MVP - VB)" wrote in
message ...
And, generalizing this, here is the formula that returns TRUE if a given
year is a leap year...

If A1 contains a
date, then that date is in a leap year if this formula returns TRUE...

=ISNUMBER(--(YEAR(A1)&"-02-29"))

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Actually, I take that back... at one function call less, I think this one
is more efficient...

=365+ISNUMBER(--(YEAR(TODAY())&"-02-29"))

Rick


"Rick Rothstein (MVP - VB)" wrote
in message ...
But won't something like:

=365+(MONTH(DATE(YEAR(TODAY()),2,28)+1)=2)

do just as well?

I came up with a similar idea, just expressed differently...

=365+ISNUMBER(MONTH(--(YEAR(TODAY())&"-02-29")))

Same number of function calls, but yours avoids the concatenation, so
I'm guessing yours is more efficient.

Rick

try:
=IF(DAY(DATE(YEAR(TODAY())+365,3,0))=29,366,365)

or put some future date in B1 and use:
=IF(DAY(DATE(YEAR(B1),3,0))=29,366,365)

the formula says:
DAY(DATE(YEAR(TODAY()),3,0)) = 29) - if February 29 exists for a
given year (which is current year for TODAY) [3 is for March, 0 is for
one day before the 1st day of March)
so if the February 29 exists in a given year then the number of days
is 366 in this year, else it is 365

Thanks Jarek. Much appreciated.

"Jarek Kujawa" wrote:

try:
=IF(DAY(DATE(YEAR(TODAY())+365,3,0))=29,366,365)

or put some future date in B1 and use:
=IF(DAY(DATE(YEAR(B1),3,0))=29,366,365)

the formula says:
DAY(DATE(YEAR(TODAY()),3,0)) = 29) - if February 29 exists for a
given year (which is current year for TODAY) [3 is for March, 0 is for
one day before the 1st day of March)
so if the February 29 exists in a given year then the number of days
is 366 in this year, else it is 365

And, generalizing this, here is the formula that returns TRUE if a given
year is a leap year...
=ISNUMBER(--(A1&"-02-29"))
where A1 contains a year value (2000, 2007, 2008, etc.).

A nice solution, with the caveat that numbers 1 through 28 entered
in cell A1, will give a false TRUE as Excel will "helpfully?" treat these
as 01 Feb 2029 through 28 Feb 2009

Thanks. To me, all years should be specified with 4-digits... I probably
should have mentioned that in my note...

"where A1 contains a 4-digit year value (2000, 2007, 2008, etc.)"

Rick

Rick wrote on Mon, 30 Jun 2008 14:30:40 -0400:

And, generalizing this, here is the formula that returns
TRUE if a given year is a leap
year... =ISNUMBER(--(A1&"-02-29")) where A1 contains a year
value (2000, 2007, 2008, etc.).

A nice solution, with the caveat that numbers 1 through 28
entered in cell A1, will give a false TRUE as Excel will
"helpfully?" treat these as 01 Feb 2029 through 28 Feb 2009

Thanks. To me, all years should be specified with 4-digits...
I probably should have mentioned that in my note...

"where A1 contains a 4-digit year value (2000, 2007, 2008,
etc.)"

But the ISNUMBER formula believes that 1900 is a leap year.

--

James Silverton
Potomac, Maryland

E-mail, with obvious alterations: not.jim.silverton.at.verizon.not

Lotus had a bug in their program and MS used it to be compatible when Lotus
was the main spreadsheet program.
I am sure in the unlikely event that someone is using 1900 one can remove
that using IF

--


Regards,


Peo Sjoblom


"James Silverton" wrote in message
...
Rick wrote on Mon, 30 Jun 2008 14:30:40 -0400:

And, generalizing this, here is the formula that returns
TRUE if a given year is a leap
year... =ISNUMBER(--(A1&"-02-29")) where A1 contains a year value
(2000, 2007, 2008, etc.).

A nice solution, with the caveat that numbers 1 through 28
entered in cell A1, will give a false TRUE as Excel will
"helpfully?" treat these as 01 Feb 2029 through 28 Feb 2009

Thanks. To me, all years should be specified with 4-digits... I probably
should have mentioned that in my note...

"where A1 contains a 4-digit year value (2000, 2007, 2008,
etc.)"

But the ISNUMBER formula believes that 1900 is a leap year.

--

James Silverton
Potomac, Maryland

E-mail, with obvious alterations: not.jim.silverton.at.verizon.not

Peo wrote on Mon, 30 Jun 2008 13:34:34 -0700:

Lotus had a bug in their program and MS used it to be
compatible when Lotus was the main spreadsheet program.
I am sure in the unlikely event that someone is using 1900 one
can remove that using IF

--

Regards,

Peo Sjoblom

"James Silverton" wrote in message
...
Rick wrote on Mon, 30 Jun 2008 14:30:40 -0400:

And, generalizing this, here is the formula that returns
TRUE if a given year is a leap
year... =ISNUMBER(--(A1&"-02-29")) where A1 contains a
year value (2000, 2007, 2008, etc.).

A nice solution, with the caveat that numbers 1 through 28
entered in cell A1, will give a false TRUE as Excel will
"helpfully?" treat these as 01 Feb 2029 through 28 Feb 2009

Thanks. To me, all years should be specified with
4-digits... I probably should have mentioned that in my
note...

"where A1 contains a 4-digit year value (2000, 2007, 2008,
etc.)"

But the ISNUMBER formula believes that 1900 is a leap year.


There is a general solution given in
http://support.microsoft.com/kb/214019

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT
a Leap Year")


James Silverton
Potomac, Maryland

E-mail, with obvious alterations: not.jim.silverton.at.verizon.not

There is a general solution given in
http://support.microsoft.com/kb/214019

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a
Leap Year")

Perhaps a little less calculation intensive (2 function calls as compared to
6)...

=IF(ISNUMBER(--(A1&"-02-29"))-(A1=1900),"Leap Year", "Not a Leap Year")

Rick

And if you wanted to find leap years in the 19th century, Rick?

Pete


"Rick Rothstein (MVP - VB)" wrote in
message ...
There is a general solution given in
http://support.microsoft.com/kb/214019

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a
Leap Year")

Perhaps a little less calculation intensive (2 function calls as compared
to 6)...

=IF(ISNUMBER(--(A1&"-02-29"))-(A1=1900),"Leap Year", "Not a Leap Year")

Rick

And if you wanted to find leap years in the 19th century, Rick?

In a worksheet? Using a worksheet formula? Well, I guess that question could
be asked academically (given that none of those dates could be used within
the worksheet); but, personally, I would probably use this UDF (which also
works as a VB function as well)...

Function IsLeapYear(YearIn As Long) As Boolean
IsLeapYear = IsDate("2/29/" & YearIn)
End Function

Rick