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

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
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

Thanks for your help!
 

Some videos you may like

Excel Facts

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

soadfan

New Member
Joined
Jul 30, 2015
Messages
46
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top