Determine # of days between two dates for each calendar year

Thank you for taking the time to read through this.

I am trying to develop a formula or way of taking the number of days between two dates and allocating them by calendar year.

For example:

Date 1: 3/4/2014
Date 2: 5/1/2015

I would want to have a formula in 2 different columns.

Column 1: Tells me how many days between Date 1 and Date 2 fall in the 2014 Calendar Year (I believe this should be 303 days [12/31/2014 minus 3/4/2014])
Column 2: Tells me how many days between Date 1 and Date 2 fall in the 2015 Calendar Year (I believe this should be 121 days [5/1/2015 minus 1/1/2015])

Any suggestions?

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Assuming your dates are in A1 and A2:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

PS. this of course raises "old question" - if it is new years eve, do we have 1 day of old year left or 0? And same on New Year - already first day of year or still less than 1 (0) passed.
Anyway adapt the above to your taste and it shall work nicely. As for the more general form, I'd opt for:

Formula:

Please Login or Register  to view this content.

etc.

Thanks for your replies Kaper and mcmahobt. I am not too worried about missing a day. I just want the allocation to be correct. I have attached a sample of how the layout needs to look. I have a data set that I continually add to each month so I need to have the rows for all the new data I receive. So the columns needs to be for the days in each calendar year.

Column C needs to take the number of days between the data entered in each row in column A and B. But Column C should only show the days that fall into the 2014 Calendar Year. So for example row 3 reads:

A3: 8/4/2014
B3: 6/3/2015
C3: needs to show the # of days that fall into the 2014 calendar year. Meaning the difference between 12/31/2014 and 8/4/2014.
D3: needs to show the # of days that fall into the 2015 calendar year. Meaning the difference between 6/3/2015 and 1/1/2015.
E3: needs to show the # of days that fall into the 2016 calendar year. In this case, the formula should read that none of the dates in A3 or B3 fall in the 2016 calendar year, and therefore it should read 0

The other part that makes this a bit complicated, is that if you notice on row 7, both dates in column A and B fall in 2014. Therefore C3 would just be the number of days between B7 and A7. Not the number of days from 12/31/2014 to A7. The formula would need to account for this so that when I drag it down it will work for all types of scenarios with the dates. Any thoughts???

Czeslaw,

The formula technically works except it does not account for all contingencies. If you notice, on row 7, both the dates in A7 and B7 fall in the 2014 calendar year. When this is the case, the formula needs to take the # of days between A7 and B7. In your formula, it is just taking the # of days between 12/31/2014 and A7.

Also, just hypothetically speaking, lets say we added a new row of dates on line 18. A18 reads 3/8/2015 and B18 reads 2/16/2016. If this were the case, your formulas would read C18 as 0, D18 as 412, and E18 as 47. D18 would be incorrect as you can't have more than 365.

This is why I have been struggling because I believe this formula will be require a few nested If statements or something. It isn't as easy as it appears.

If you show just the year in the header row, e,g, in C2 you have 2014, in D2 you have 2015 etc. then you can use this formula in C3 copied across and down

=MAX(0,MIN(DATE(C$2+1,1,0),$B3)-MAX(DATE(C$2,1,1),$A3)+1)

That will count start and end date, e.g. Start date today, end date tomorrow counts 2 - that can be adjusted if you want but if today until tomorrow counts 1 which day are you excluding?

Note: this works for any date ranges, even if you start in 2013 or end in 2017, with a single consistent formula that doesn't need to be changed

I think following meets your request pretty well:
C3 and copy right/down as needed:

Formula:

Please Login or Register  to view this content.

and H3 and copy right/down as needed:

Formula:

Please Login or Register  to view this content.