Days in year between certain dates

In OpenOffice (or Excel), I need to calculate the exact number of days that a certain date range falls within a certain year. Eg. the date range 2009/1/1 to 2010/11/01 has 365 days in 2009 and 305 days in 2010. So far it seems a function days_in_year(year;startdate;enddate) does not exist and doing something similar in a formula using WHEN would be quite a long formular.

Does anybody know something small for this purpose (without macros)?

-

``` =MAX(MIN(DATE(year,12,31),enddate)-MAX(DATE(year,1,1),startdate)+1,0) ```

(for Excel)

-
Thank you, this worked perfectly. No idea what my brain did, but my formula was at least thrice that long :-( –  Franky Nov 1 '10 at 20:47
@Franky: No problem. Welcome to Super User :) –  Mike Fitzpatrick Nov 2 '10 at 2:44

To calculate the number of days between two dates, you can use use the subtraction (-) operator.

``````A
Date
6/8/2007
6/20/2007

Formula     Description
--------    ------------
=A3-A2      Days between the two dates (12)
``````
-
To split it into years, you can use the subtraction formula for each year (taking the end of year as `YYYY/12/31`). –  Mehper C. Palavuzlar Nov 1 '10 at 9:07