# Excel Formula caculating number days of year between 2 dates

#### Johns90

##### New Member
Hi everyone,

I am looking for an excel formula to calculating the number of days from years between "Start date" & "end date"

Example :

My start date is 06/19/2020
My end date is 06/20/2023

I would to know on this example the number of days from year 2020 => from start date ; year 2021 (full year) ; year 2022 (full year) ; 2023 => from end date
in my databse i have different start date and end date by line

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### sykes

##### Well-known Member
Just format start & end cells for date, then subtract....
test.xlsm
BCDE
15StartEnd number of days
1619-06-202020-06-20231096
Sheet1

#### Johns90

##### New Member
Just format start & end cells for date, then subtract....
test.xlsm
BCDE
15StartEnd number of days
1619-06-202020-06-20231096
Sheet1

it's little more complex
if my start date is 06/19/2020 & my end date is 06/20/2023

Result :

number of days of 2020 : 195
number of days of 2021 : 365
number of days of 2022 : 365
number of days of 2023 : 170

And i have differents start date & end date from 2020 to x year

jimmy

#### alz

##### Board Regular
This One?

Excel Message board.xlsm
AB
1Start DateEnd Date
219/06/202020/06/2023
3
4
5Number Of Days In
62020195
72021365
82022365
92023170
56
Cell Formulas
RangeFormula
B6:B9B6=IF(DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2))=364,365,DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2)))

#### Johns90

##### New Member

This One?

Excel Message board.xlsm
AB
1Start DateEnd Date
219/06/202020/06/2023
3
4
5Number Of Days In
62020195
72021365
82022365
92023170
56
Cell Formulas
RangeFormula
B6:B9B6=IF(DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2))=364,365,DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2)))
It's working, thanks a lot

alz

#### jtakw

##### Well-known Member
Hi,

I'm not so sure the formula in Post #4 will give you the correct results, try my Column B formulas:

Book3.xlsx
ABC
1Start DateEnd Date
26/19/20206/20/2022
3
4
5Number Of Days InFormula from Post #4
62020195195
72021365365
82022171170
920230-195
10
11731535
12731
Sheet737
Cell Formulas
RangeFormula
B6B6=DAYS(MIN(B\$2,DATE(A6,12,31)),A\$2)
C6:C9C6=IF(DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2))=364,365,DAYS(IF(DATE(A6,12,31)>\$B\$2,\$B\$2,DATE(A6,12,31)),IF(DATE(A6,1,1)>\$A\$2,DATE(A6,1,1),\$A\$2)))
B7:B9B7=DAYS(MIN(B\$2,DATE(A7,12,31)),A\$2)-SUM(B\$6:B6)
B11:C11C11=SUM(C6:C9)
B12B12=DAYS(B2,A2)

#### jtakw

##### Well-known Member

Hi,

Updated my formulas to cover all Start/End Date scenarios:

Book3.xlsx
AB
1Start DateEnd Date
26/19/20216/20/2024
3
4
5Number Of Days In
620200
72021195
82022365
92023365
102024172
1120250
1220260
1320270
1420280
15
161097
171097
Sheet737
Cell Formulas
RangeFormula
B6B6=IF(YEAR(A2)>A6,0,DAYS(MIN(B\$2,DATE(A6,12,31)),A\$2))
B7:B14B7=IF(YEAR(A\$2)>A7,0,DAYS(MIN(B\$2,DATE(A7,12,31)),A\$2)-SUM(B\$6:B6))
B16B16=SUM(B6:B14)
B17B17=DAYS(B2,A2)

#### Phuoc

##### Active Member
Another way, in B6:

=IF(OR(YEAR(\$A\$2)>A6,YEAR(\$B\$2)<A6),0,SUMPRODUCT(--(YEAR(ROW(INDIRECT(\$A\$2&":"&\$B\$2)))=A6)))

#### Dave Patton

##### Well-known Member
2020 - 2023 in second column are dates like Dec 31, 2020 custom formatted to yyy

Date and Time 2021.xlsm
ABCDE
1Start DateEnd Date
219-Jun-2020-Jun-23
3
4
5Number of Days In
620201952020195
720213652021365
820223652022365
920231712023171
10
4a
Cell Formulas
RangeFormula
B6:B9B6=SUMPRODUCT(--(YEAR(ROW(INDIRECT(\$A\$2+1&":"&\$B\$2)))=A6))
E6:E9E6=MAX(0,MIN(D6,\$B\$2)-MAX(\$A\$2,EDATE(D6,-12)))

Last edited:

Replies
1
Views
214
Replies
7
Views
268
Replies
0
Views
165
Replies
4
Views
198
Replies
2
Views
152

1,129,529
Messages
5,636,853
Members
416,945
Latest member
Himu

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