Excel Formula caculating number days of year between 2 dates

Johns90

New Member
Joined
Feb 3, 2021
Messages
11
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

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
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
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)))
 
Solution

Johns90

New Member
Joined
Feb 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 29, 2016
Messages
320
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
Joined
Feb 15, 2002
Messages
4,654
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top