Calculate Number of days between two Dates (Year wise)

[hardeep.kanwar]

Hello Experts

Today, I stuck with a different problem, I need to calculate numbers of days between 2 dates and also calculate the gap of days in yrs also.

[TABLE="width: 1172"]
<colgroup><col><col><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]See the below example

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68"]Start Date[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 68"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62"]End Date[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 62"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67"]Total Gap No. Days[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]No of Gap (yr) 2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]No of Gap (yr) 2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65"]No of Gap (yr) 2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 472"]
<tbody>[TR]
[TD="class: xl65, width: 472"]Remarks[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]25-11-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62, align: right"]10-01-18[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 62, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]46[/TD]
[TD]0[/TD]
[TD]36[/TD]
[TD]0[/TD]
[TD][TABLE="width: 472"]
<tbody>[TR]
[TD="class: xl65, width: 472"]If years are Differenent than show the Value in both Yrs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]01-01-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62, align: right"]02-02-17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]32[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"] 364 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 472"]
<colgroup><col></colgroup><tbody>[TR]
[TD]if years are same in both in Column than Value should be ZERO in other Yr.[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68, align: right"][/TD]
[/TR]
[TR]
[TD="width: 68, align: right"]01-01-16[/TD]
[/TR]
[TR]
[TD="width: 68, align: right"][/TD]
[/TR]
[TR]
[TD="width: 68, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62, align: right"]06-06-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67, align: right"]157[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]157[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][TABLE="width: 472"]
<colgroup><col></colgroup><tbody>[TR]
[TD]if years are same in both in Column than Value should be ZERO in other Yr.[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl65, width: 68, align: right"]01-01-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl65, width: 62, align: right"]06-06-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67, align: right"]887[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: right"]365[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]365[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: right"]157[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

If the example you posted your expected results?
If so, then either they don't look correct, or I am not understanding you.

- In the first row of data, shouldn't there be a value in the 2018 column (if not, then why)?
- In the second row, why is there no value in the 2017 column? Why is there one in the 2018 column when neither of those dates are in 2018?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
7	Start	End	Total	2016	2017	2018
8	01-01-16	06-06-18	888	366	365	157
9
8b

Cell Formulas
Range	Formula
C8	=B8-A8+1
D8	=MAX(0,MIN($B8,EOMONTH(D7,11))-MAX($A8,D7)+1)
E8	=MAX(0,MIN($B8,EOMONTH(E7,11))-MAX($A8,E7)+1)
F8	=MAX(0,MIN($B8,EOMONTH(F7,11))-MAX($A8,F7)+1)

Named Ranges
Name	Refers To	Cells
Mon	="0111111"

N.B. Ignore the defined name; it doesn't apply to this questions.

2016 etc are dates like 1-1-2016 formatted as Year

 

[hardeep.kanwar]

Sorry ,yes it should be 10 instead of 0

 

[Joe4]

Assuming that your table starts in cell A1, so row 2 is the first row of data and column C is your "Total Gap" column, here are the formulas I came up with:

Cell C2: =B2-A2
Cell D2: =MAX(MIN(B2,DATE(2016,12,31))-MAX(A2,DATE(2016,1,1)),0)
Cell E2: =MAX(MIN(B2,DATE(2017,12,31))-MAX(A2,DATE(2017,1,1)),0)
Cell F2: =MAX(MIN(B2,DATE(2018,12,31))-MAX(A2,DATE(2018,1,1)),0)

I am not sure if those gaps should be inclusive of the start date. If so, you may just need to add 1 to each calculation.