Calculate the number of days per year between two different dates Excel [closed]

Solution 1:

This formula should work up to 25 November 4770, by which time Excel may have been superseded by something else.

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt &":"&endDt)))=C$1))

where C1 contains the year in question.

For example:

However, you write you want to know the "difference between the dates". That phraseology usually indicates that you don't want to count the first date. The formula counts all of the dates.

If you really want the "difference" and not the number of days from Start to End, then add one to the start date in the formula:

=SUMPRODUCT(--(YEAR(ROW(INDIRECT(StartDt+1 &":"&endDt)))=C$1))

Edit: (provoked by @fixer1234) To understand how this is working, you need to understand that

• dates are stored in Excel as serial numbers with, usually, 1-jan-1900 equal to 1.
• ROW(INDIRECT(n:m)) is a way of returning an array of numbers equal to the row numbers represented by n and m
• The YEAR function then returns the the equivalent year from each of those values, with we compare with the year at the top of the relevant column, generating an array of TRUE;FALSE.
• Since, in Excel, --TRUE = 1, SUMPRODUCT will effectively return the SUM of all the dates that are in the year at the top of the column.
• The date limitation I mentioned is because the are only 2^20 rows in Excel. It could be overcome by applying an offset to the dates within the ROW(INDIRECT(… construct, and then adding that back after, if the dates involved are later than 11/25/4770.