# Date calculations formula

#### xcelnovice

##### Board Regular
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

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

##### Board Regular
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

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

##### Board Regular
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

Last edited:

#### mole999

##### Well-known Member
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

Last edited:

#### xcelnovice

##### Board Regular
Awesome! Thanks folks...worked like a charm

Replies
3
Views
79
Replies
3
Views
324
Replies
3
Views
175
Replies
4
Views
322
Replies
5
Views
74

1,191,183
Messages
5,985,170
Members
439,944
Latest member
Vangelis74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back