| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Obtain days in a given year
Is there a fuction to return the days in a given year?
Entry Return 2008 366 2010 365 Igbert |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
assume 2008 is in a2
in b2 =DATE(A2+1,1,0)-DATE(A2,1,0) Success - click yes. -- Wag more, bark less "igbert" wrote: Is there a fuction to return the days in a given year? Entry Return 2008 366 2010 365 Igbert |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
=date(2010,1,1)-date(2009,1,1)
would give the number of days in 2009. If you put the interested year in A1, you could use: =date(a1+1,1,1)-date(a1,1,1) (well, except for 1900.) On 05/14/2010 11:23, igbert wrote: Is there a fuction to return the days in a given year? Entry Return 2008 366 2010 365 Igbert |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
Assume that you are having the Year in A1. Copy and paste the below formula
in B1 cell. =IF(A1="","",DATEDIF(DATE(A1,1,1),DATE(A1+1,1,1)," D")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "igbert" wrote: Is there a fuction to return the days in a given year? Entry Return 2008 366 2010 365 Igbert |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
Thanks, formula works preferct for any year.
"Brad" wrote: assume 2008 is in a2 in b2 =DATE(A2+1,1,0)-DATE(A2,1,0) Success - click yes. -- Wag more, bark less "igbert" wrote: Is there a fuction to return the days in a given year? Entry Return 2008 366 2010 365 Igbert |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
You're welcome
Another approach that would also work =365+(MONTH(DATE(A2,2,29))=2) Success click yes -- Wag more, bark less "igbert" wrote: Thanks, formula works preferct for any year. "Brad" wrote: assume 2008 is in a2 in b2 =DATE(A2+1,1,0)-DATE(A2,1,0) Success - click yes. -- Wag more, bark less "igbert" wrote: Is there a fuction to return the days in a given year? Entry Return 2008 366 2010 365 Igbert |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
On 14 Mai, 18:19, Brad wrote:
You're welcome Another approach that would also work =365+(MONTH(DATE(A2,2,29))=2) Success click yes -- Wag more, bark less "igbert" wrote: Thanks, formula works preferct for any year. "Brad" wrote: assume 2008 is in a2 in b2 =DATE(A2+1,1,0)-DATE(A2,1,0) Success - click yes. -- Wag more, bark less "igbert" wrote: Is there a fuction to return the days in a given year? Entry * * *Return 2008 * * *366 2010 * * *365 Igbert =337+DAY(DATE(A1,3,0)) Regards, Bernd |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtain days in a given year
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Obtain rate for work center, depending on year | Excel Worksheet Functions | |||
| Obtain a Year to Date result without all cells of data being fille | Excel Worksheet Functions | |||
| Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
| i have two days and i want the difference in days, months, year | Excel Worksheet Functions | |||
| days in a year | Excel Worksheet Functions | |||
Linear Mode
