Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determining a leap year
Rick,
yr last formula is excellent to me |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determining a leap year
=IF(DAY(DATE(YEAR(TODAY()),3,0))=29,366,365)
is a better formula I think |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determining a leap year
thks David
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leap Year | Excel Discussion (Misc queries) | |||
leap year | Excel Discussion (Misc queries) | |||
Leap Year | Charts and Charting in Excel | |||
How to determine if year is a leap year | Excel Worksheet Functions | |||
Leap year indicator | Excel Worksheet Functions |