up vote 3 down vote favorite
share [g+] share [fb]

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

link|improve this question
feedback

2 Answers

up vote 3 down vote accepted

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

(for Excel)

link|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
feedback
up vote 2 down vote

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)
link|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
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.