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.

See the below example Start Date <tbody> </tbody> End Date <tbody> </tbody> Total Gap No. Days <tbody> </tbody> No of Gap (yr) 2016 <tbody> </tbody> No of Gap (yr) 2017 <tbody> </tbody> No of Gap (yr) 2018 <tbody> </tbody> Remarks <tbody> </tbody> 25-11-17 <tbody> </tbody> 10-01-18 <tbody> </tbody> 46 0 36 0 If years are Differenent than show the Value in both Yrs <tbody> </tbody> 01-01-17 <tbody> </tbody> 02-02-17 <tbody> </tbody> 32 0 364 <tbody> </tbody> if years are same in both in Column than Value should be ZERO in other Yr. <colgroup><col></colgroup><tbody> </tbody> 01-01-16 <tbody> </tbody> 06-06-16 <tbody> </tbody> 157 <tbody> </tbody> 157 0 0 if years are same in both in Column than Value should be ZERO in other Yr. <colgroup><col></colgroup><tbody> </tbody> 01-01-16 <tbody> </tbody> 06-06-18 <tbody> </tbody> 887 <tbody> </tbody> 365 <tbody> </tbody> 365 <tbody> </tbody> 157 <tbody> </tbody> <tbody> </tbody>

<colgroup><col><col><col><col span="2"><col span="2"><col></colgroup><tbody>
</tbody>

 

[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.