Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)?

share|improve this question
up vote 3 down vote accepted

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

(for Excel)

share|improve this answer
    
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)
share|improve this answer
    
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .