A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Number of days falling in various years



 
 
Thread Tools Display Modes
  #1  
Old March 29th, 2010, 08:02 PM posted to microsoft.public.excel.newusers
Revenue
external usenet poster
 
Posts: 2
Default Number of days falling in various years

I am starting two dates in my example. The first is 6/20/02 and the
second is 1/15/2006. I need to calculate in separate columns for years
2001 through 2006 the number of day in this year that are between
these two dates. For example, in 2001, the answer is -, in 2002, the
answer is 194, and in years 2003 through 2005, the answer is 365, and
finally in 2006, the answer is 15 days.


The formula below almost works, but 2001 it came up with a negative
number.

=MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,31)-$K$2),
365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2-
DATE(O1,1,1)+1))

Surely there is a function to handle this sort of thing.

Thanks,


Bruce
  #2  
Old March 29th, 2010, 08:29 PM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default Number of days falling in various years

"Revenue" wrote:
The first is 6/20/02 and the second is 1/15/2006.
I need to calculate in separate columns for years
2001 through 2006 the number of day in this year
that are between these two dates.


First, I calculate a total of 1306 days, namely: DATE(2006,15,1) -
DATE(2002,6,20) + 1 (formatted as General).

If you agree with that method of counting, and if the start and end dates
are in J2 and K2 respectively, and the years 2001 through 2006 as in O1:O6,
then put the following formula into O1, format as General if necessary, and
copy down through O6:

=MAX(0, MIN(DATE(O1,12,31),$K$2) - MAX(DATE(O1,1,1),$J$2) + 1)


----- original message -----

"Revenue" wrote in message
...
I am starting two dates in my example. The first is 6/20/02 and the
second is 1/15/2006. I need to calculate in separate columns for years
2001 through 2006 the number of day in this year that are between
these two dates. For example, in 2001, the answer is -, in 2002, the
answer is 194, and in years 2003 through 2005, the answer is 365, and
finally in 2006, the answer is 15 days.


The formula below almost works, but 2001 it came up with a negative
number.

=MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,31)-$K$2),
365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2-
DATE(O1,1,1)+1))

Surely there is a function to handle this sort of thing.

Thanks,


Bruce


  #3  
Old March 29th, 2010, 08:40 PM posted to microsoft.public.excel.newusers
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Number of days falling in various years

Note that 2004 was a leap year, thus 366 days.

J2: Smaller/Start Date
K2: Larger/Finish Date

O2:U2 contain the numbers 2001 thought 2008

In O3:
=IF(OR(YEAR($J$2)O2,YEAR($K$2)O2),0,MIN($K$2,DAT E(P2,,))-MAX($J$2,DATE(O2,,)))

--
Best Regards,

Luke M
"Revenue" wrote in message
...
I am starting two dates in my example. The first is 6/20/02 and the
second is 1/15/2006. I need to calculate in separate columns for years
2001 through 2006 the number of day in this year that are between
these two dates. For example, in 2001, the answer is -, in 2002, the
answer is 194, and in years 2003 through 2005, the answer is 365, and
finally in 2006, the answer is 15 days.


The formula below almost works, but 2001 it came up with a negative
number.

=MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,31)-$K$2),
365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2-
DATE(O1,1,1)+1))

Surely there is a function to handle this sort of thing.

Thanks,


Bruce



  #4  
Old March 29th, 2010, 09:22 PM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 1,896
Default Number of days falling in various years

Well, I was getting a negative number for 2006 until I changed the "G2"
reference in your formula to J2, then I got 15 days for 2006.

If we can assume that if the formula results in a negative number, then it's
outside of your timeframe, you can write the formula like this:
=IF(MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,3 1)-$K$2),365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12 ,31)-$K$2),365),$J$2-DATE(O1,1,1)+1))0,0,MIN(IF(DATE(O1,12,31)$K$2,0, MIN((DATE(O1,12,31)-$K$2),365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12 ,31)-$K$2),365),$J$2-DATE(O1,1,1)+1)))

That just evaluates the formula, and if it evaluates to 0, then a 0 is
displayed, otherwise it gets evaluated again and the non-zero result is
displayed.

"Revenue" wrote:

I am starting two dates in my example. The first is 6/20/02 and the
second is 1/15/2006. I need to calculate in separate columns for years
2001 through 2006 the number of day in this year that are between
these two dates. For example, in 2001, the answer is -, in 2002, the
answer is 194, and in years 2003 through 2005, the answer is 365, and
finally in 2006, the answer is 15 days.


The formula below almost works, but 2001 it came up with a negative
number.

=MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,31)-$K$2),
365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2-
DATE(O1,1,1)+1))

Surely there is a function to handle this sort of thing.

Thanks,


Bruce
.

  #5  
Old March 29th, 2010, 09:34 PM posted to microsoft.public.excel.newusers
Revenue
external usenet poster
 
Posts: 2
Default Number of days falling in various years

Thanks, I got your example formula to work.




On Mar 29, 2:40*pm, "Luke M" wrote:
Note that 2004 was a leap year, thus 366 days.

J2: Smaller/Start Date
K2: Larger/Finish Date

O2:U2 contain the numbers 2001 thought 2008

In O3:
=IF(OR(YEAR($J$2)O2,YEAR($K$2)O2),0,MIN($K$2,DAT E(P2,,))-MAX($J$2,DATE(O2*,,)))

--
Best Regards,

Luke M"Revenue" wrote in message

...



I am starting two dates in my example. The first is 6/20/02 and the
second is 1/15/2006. I need to calculate in separate columns for years
2001 through 2006 the number of day in this year that are between
these two dates. For example, in 2001, the answer is -, in 2002, the
answer is 194, and in years 2003 through 2005, the answer is 365, and
finally in 2006, the answer is 15 days.


The formula below almost works, but 2001 it came up with a negative
number.


=MIN(IF(DATE(O1,12,31)$K$2,0,MIN((DATE(O1,12,31)-$K$2),
365)),IF(DATE(O1,12,31)$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2-
DATE(O1,1,1)+1))


Surely there is a function to handle this sort of thing.


Thanks,


Bruce- Hide quoted text -


- Show quoted text -


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.