Excel Formula caculating number days of year between 2 dates

Johns90

New Member
Joined
Feb 3, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Thanks for your help
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Just format start & end cells for date, then subtract....
test.xlsm
BCDE
15StartEnd number of days
1619-06-202020-06-20231096
Sheet1
 
Upvote 0
Just format start & end cells for date, then subtract....
test.xlsm
BCDE
15StartEnd number of days
1619-06-202020-06-20231096
Sheet1
Thanks for your help

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

thanks for your help

jimmy
 
Upvote 0
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)))
 
Upvote 0
Solution
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 :)
 
  • Like
Reactions: alz
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
Another way, in B6:

=IF(OR(YEAR($A$2)>A6,YEAR($B$2)<A6),0,SUMPRODUCT(--(YEAR(ROW(INDIRECT($A$2&":"&$B$2)))=A6)))
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top