Determining a leap year

[ARGT]

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,MOD(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.

 

[David Biddulph]

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

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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

is a better formula I think

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

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).

 

[Jarek Kujawa]

thks David

 

[Rick Rothstein \(MVP - VB\)]

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 Rothstein \(MVP - VB\)]

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\)]

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

 

[Jarek Kujawa]

Rick,
yr last formula is excellent to me

 

[Pete_UK]

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

Pete

 

[ARGT]

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?

 

[Roger Govier]

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

 

[Jarek Kujawa]

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

 

[ARGT]

Thanks Jarek. Much appreciated.

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[James Silverton]

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

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 Sjoblom]

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]

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


Peo Sjoblom

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