Obtain days in a given year
in [General]
|
From: Mike H on 15 May 2010 04:31 Micky, > If you recognize the following formula as the one suggested by you - check it No I don't recognize that formula! I posted :- =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),366, 365) You posted:- =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A9,100)<>0)),366, 365) Note the error in your formula referring to A1 & A9 and in the one in the image you posted (A7 & A14) and hence the erronious results you are getting. My formula returns 366 for 1908, 2008 & 2108 & 365 for year 2200 which isn't a leap year by the following definition:- A year will be a leap year if it is divisible by 4 but not by 100. If a year is divisible by 4 and by 100, it is not a leap year unless it is also divisible by 400. Regards, -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "מיכאל (מיקי) אבידן" wrote: > If you recognize the following formula as the one suggested by you - check it > against 1908, 2008, 2108 - it returns 365 instead of 366. > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A9,100)<>0)),366, 365) > Here is how I chacked. > http://img64.imageshack.us/img64/2067/nonameo.png > Micky > > > "Mike H" wrote: > > > Mickey, > > > > For clarification:- > > > > A leap year is every 4 years, but not every 100 years, then again every 400 > > years > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "Mike H" wrote: > > > > > Mickey, > > > > > > > Would you be so kind and check your suggested formula for the years: > > > > 1908, 2008, 2108 and 2200(!) > > > > > > Of course > > > > > > > > > 1908 = leap year > > > 2008 = leap year > > > 2108 - leap year > > > 2200 - Not a leap year > > > > > > > > > -- > > > Mike > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > introduces the fewest assumptions while still sufficiently answering the > > > question. > > > > > > > > > "מיכאל (מיקי) אבידן" wrote: > > > > > > > Mike, > > > > Would you be so kind and check your suggested formula for the years: > > > > 1908, 2008, 2108 and 2200(!) > > > > Micky > > > > > > > > > > > > "Mike H" wrote: > > > > > > > > > Micky, > > > > > > > > > > I like the second formula, very neat, but I'm afraid the first gives errors, > > > > > you would need to do this to get the correct result using MOD > > > > > > > > > > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),366, 365) > > > > > -- > > > > > Mike > > > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > > introduces the fewest assumptions while still sufficiently answering the > > > > > question. > > > > > > > > > > > > > > > "מיכאל (מיקי) אבידן" wrote: > > > > > > > > > > > 1) =IF(MOD(A1,4)=0,366,365) > > > > > > 2) =IF(ISNUMBER(--(A1&"/2/29")),366,365) > > > > > > Micky > > > > > > > > > > > > > > > > > > "igbert" wrote: > > > > > > > > > > > > > Is there a fuction to return the days in a given year? > > > > > > > > > > > > > > Entry Return > > > > > > > > > > > > > > 2008 366 > > > > > > > 2010 365 > > > > > > > > > > > > > > Igbert > > > > > > >
From: Mike H on 15 May 2010 05:58 Micky. I'm glad we resolved that. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "מיכאל (מיקי) אבידן" wrote: > Sorry for the typo on my end... > Anyhow - your formula can be a little bit shorter: > =365+(--(MOD(A7,4)=0)*(MOD(A7,100)<>0)+(MOD(A7,400)=0)>0) > and so can the one you liked: > =365+ISNUMBER(--(A1&"/2/29")) > Micky > > > "Mike H" wrote: > > > Micky, > > > > > If you recognize the following formula as the one suggested by you - check it > > > > No I don't recognize that formula! I posted :- > > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),366, 365) > > > > You posted:- > > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A9,100)<>0)),366, 365) > > > > Note the error in your formula referring to A1 & A9 and in the one in the > > image you posted (A7 & A14) and hence the erronious results you are getting. > > > > My formula returns 366 for 1908, 2008 & 2108 & 365 for year 2200 which isn't > > a leap year by the following definition:- > > > > A year will be a leap year if it is divisible by 4 but not by 100. If a year > > is divisible by 4 and by 100, it is not a leap year unless it is also > > divisible by 400. > > > > Regards, > > > > -- > > Mike > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > introduces the fewest assumptions while still sufficiently answering the > > question. > > > > > > "מיכאל (מיקי) אבידן" wrote: > > > > > If you recognize the following formula as the one suggested by you - check it > > > against 1908, 2008, 2108 - it returns 365 instead of 366. > > > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A9,100)<>0)),366, 365) > > > Here is how I chacked. > > > http://img64.imageshack.us/img64/2067/nonameo.png > > > Micky > > > > > > > > > "Mike H" wrote: > > > > > > > Mickey, > > > > > > > > For clarification:- > > > > > > > > A leap year is every 4 years, but not every 100 years, then again every 400 > > > > years > > > > -- > > > > Mike > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > introduces the fewest assumptions while still sufficiently answering the > > > > question. > > > > > > > > > > > > "Mike H" wrote: > > > > > > > > > Mickey, > > > > > > > > > > > Would you be so kind and check your suggested formula for the years: > > > > > > 1908, 2008, 2108 and 2200(!) > > > > > > > > > > Of course > > > > > > > > > > > > > > > 1908 = leap year > > > > > 2008 = leap year > > > > > 2108 - leap year > > > > > 2200 - Not a leap year > > > > > > > > > > > > > > > -- > > > > > Mike > > > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > > introduces the fewest assumptions while still sufficiently answering the > > > > > question. > > > > > > > > > > > > > > > "מיכאל (מיקי) אבידן" wrote: > > > > > > > > > > > Mike, > > > > > > Would you be so kind and check your suggested formula for the years: > > > > > > 1908, 2008, 2108 and 2200(!) > > > > > > Micky > > > > > > > > > > > > > > > > > > "Mike H" wrote: > > > > > > > > > > > > > Micky, > > > > > > > > > > > > > > I like the second formula, very neat, but I'm afraid the first gives errors, > > > > > > > you would need to do this to get the correct result using MOD > > > > > > > > > > > > > > =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),366, 365) > > > > > > > -- > > > > > > > Mike > > > > > > > > > > > > > > When competing hypotheses are otherwise equal, adopt the hypothesis that > > > > > > > introduces the fewest assumptions while still sufficiently answering the > > > > > > > question. > > > > > > > > > > > > > > > > > > > > > "מיכאל (מיקי) אבידן" wrote: > > > > > > > > > > > > > > > 1) =IF(MOD(A1,4)=0,366,365) > > > > > > > > 2) =IF(ISNUMBER(--(A1&"/2/29")),366,365) > > > > > > > > Micky > > > > > > > > > > > > > > > > > > > > > > > > "igbert" wrote: > > > > > > > > > > > > > > > > > Is there a fuction to return the days in a given year? > > > > > > > > > > > > > > > > > > Entry Return > > > > > > > > > > > > > > > > > > 2008 366 > > > > > > > > > 2010 365 > > > > > > > > > > > > > > > > > > Igbert > > > > > > > > >
First
|
Prev
|
Pages: 1 2 3 Prev: Summing several IF's in a column. Next: column widths keep changing in shared file |