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

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

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

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

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

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

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

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

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

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

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

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