|
Determining a leap year
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. |
Determining a leap year
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. |
Determining a leap year
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 |
Determining a leap year
=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,366,365)
is a better formula I think |
Determining a leap year
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 |
Determining a leap year
thks David
|
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
Rick,
yr last formula is excellent to me |
Determining a leap year
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 - |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining a leap year
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 |
Determining 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") 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 |
Determining a leap year
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 |
Determining a leap year
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 |
| All times are GMT +1. The time now is 03:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com