Date calculations formula

[xcelnovice]

I have 2 dates i.e. 5-Dec-16 in cell A1 & 10-Nov-17 in cell B1. Is there a way to calculate the number of days broken out relative to each year? Thanks

 

[Big Wade]

Can you clarify what you mean by broken out? As in, what day # 5-dec-16 is in the current year, or the amount of days in between both dates?

 

[xcelnovice]

Sorry. In that time frame how many of those days are in 2016 & how many are in 2017.

 

[Big Wade]

Try this formula in any cell:
=DATE(YEAR(A1)+1,1,1)-A1 & " Days in " & YEAR(A1) & " " & B1 - DATE(YEAR(A1)+1,1,1) + 1 & " Days in " & YEAR(B1)

If you want to separate them then it would be:
=DATE(YEAR(A1)+1,1,1)-A1 for days in year 1
=B1 - DATE(YEAR(A1)+1,1,1) + 1 for days in year 2

EDIT: Format cell as text

 

[mole999]

Toy with this I had it in C1 > =IF(TEXT(A1,"yyyy") < TEXT(B1,"yyyy"),TEXT(A2-A1,"###")&" - "&TEXT(B1-A2,"###"),0)

I put 5/12/2016 in A1
10/11/2017 in B1
31/12/2016 in A2

 

[xcelnovice]

Awesome! Thanks folks...worked like a charm