# Tricky formula: Splitting two dates in its count of days per calendar year

#### actjfc

##### Active Member
Excel friends,

I have a table with two columns Start Date and End Date. For example,
Start Date - End Date
7/1/2014 - 6/30/2017

I need help developing a formula that counts the days between these two dates split by the calendar year.

So, I built six columns with the calendar year to count the days.

These should be the results of the formula:

2013 - Results 0
2014 – Days from 7/1/2014 to 12/31/2014 = Results 183
2015 - Results 365
2016 - Results 365
2017 – Days from 1/1/2017 to 6/30/2017 = 180
2018 - Results 0

It becomes tricky when you have two dates like:

Start Date - End Date
10/15/2015 - 11/14/2015

These should be the results of the formula:

2013 - Results 0
2014 –Results 0
2015 - Results 30
2016 - Results 0
2017 – Results 0
2018 - Results 0

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

##### New Member
It's a long shot, and i didn't test it, but i guess you can parse "date - date" with len(), left()/right(), find() and use datevalue() & year() to extract the year, then sumif() to get days or something like that.
But why bother, can you use helper columns and concatenate ?

Last edited:

Replies
17
Views
133
Replies
2
Views
39
Replies
1
Views
43
Replies
4
Views
83
Replies
3
Views
45