Calculate duration (day/month) of period, between a selected period

[Stensol]

Dear Excel-goeroes,

For my work I need to know how many contractual funding is available in a certain period in time. Funding covers a certain period between 2 dates, and the selected period covers a period between 2 dates as well.

I've visualized an example of this in below screenshot. With this I've visualized the fundingperiod and selectedperiod overlap with a timeline. Timeline is just to make you understand the relation, not for final formula, as I only need results as marked in green cells. Original workbook attached to this post.

• In the examples my core fundingperiod (blue cells) covers 3 months (91 days exactly) with a total fund of € 9.000,-.
• But I only want to know the available funds in a selected period (red cells)
  • Examples A,B,C,E have different selected periods, with each different outcomes and available funds for that period.
    • all four examples have correct outcome
  • Example D and F is where my formula results in wrong outcome
    • ex.D should be 1 month
    • ex.F should be 6 months

For this I've build quite a large and complex formula, of which I think there should be an easier way and shorter formula. The formula calculates the "amount of months within a period overlap", which is enough for me to calculate the funds per month. Besides that, the current formula doesn't show correct result, when funding period overlaps selected period.

Current formula in English (translated):

=IF(IF(AND($C2>=$C3;$D2<=$D3);(IFERROR(DATEDIF($C2;$D2;"m")+1;0));(IF(SUM(IFERROR(DATEDIF($C2;$D3;"m")+1;0))-(IFERROR(DATEDIF($C3;$D3;"m")+1;0))<=0;(IFERROR(DATEDIF($C2;$D3;"m")+1;0));(SUM(IFERROR(DATEDIF($C2;$D2;"m")+1;0))-(SUM(IFERROR(DATEDIF($C2;$D3;"m")+1;0))-(IFERROR(DATEDIF($C3;$D3;"m")+1;0)))))))<=0;0;IF(AND($C2>=$C3;$D2<=$D3);(IFERROR(DATEDIF($C2;$D2;"m")+1;0));(IF(SUM(IFERROR(DATEDIF($C2;$D3;"m")+1;0))-(IFERROR(DATEDIF($C3;$D3;"m")+1;0))<=0;(IFERROR(DATEDIF($C2;$D3;"m")+1;0));(SUM(IFERROR(DATEDIF($C2;$D2;"m")+1;0))-(SUM(IFERROR(DATEDIF($C2;$D3;"m")+1;0))-(IFERROR(DATEDIF($C3;$D3;"m")+1;0))))))))

Current formula in Dutch (original)

=ALS(ALS(EN($C2>=$C3;$D2<=$D3);(ALS.FOUT(DATUMVERSCHIL($C2;$D2;"m")+1;0));(ALS(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0))-(ALS.FOUT(DATUMVERSCHIL($C3;$D3;"m")+1;0))<=0;(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0));(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D2;"m")+1;0))-(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0))-(ALS.FOUT(DATUMVERSCHIL($C3;$D3;"m")+1;0)))))))<=0;0;ALS(EN($C2>=$C3;$D2<=$D3);(ALS.FOUT(DATUMVERSCHIL($C2;$D2;"m")+1;0));(ALS(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0))-(ALS.FOUT(DATUMVERSCHIL($C3;$D3;"m")+1;0))<=0;(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0));(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D2;"m")+1;0))-(SOM(ALS.FOUT(DATUMVERSCHIL($C2;$D3;"m")+1;0))-(ALS.FOUT(DATUMVERSCHIL($C3;$D3;"m")+1;0))))))))

I hope someone can give me a fresh start why this goes wrong, and how I could fix this. But maybe there's a way in which this can be done with an easier formula.

Sidenote: I don't prefer to use VBA as this isn't supported in our company...

Thanks for your interest and valuable braintime...

Best regards,

Stensol, The Netherlands

 

[Xiq]

Hi Stensol,

For the calulation of months, you can simply use something like this (place in H2, then you can copy and paste in the other cells):

=ROUND(G2/30,)

Here is also a heavy weight calulation of #days (place in G2, then you can copy and paste in the other cells):

=SUMPRODUCT((ROW(A:A)>=$C2)*(ROW(A:A)<=$D2)*(ROW(A:A)>=$C3)*(ROW(A:A)<=$D3))

 

[Xiq]

Here is a little less process-heavy formula for calculation the #days:

=SUMPRODUCT((ROW(INDIRECT("A"&MIN($C2:$C3)):INDIRECT("A"&MAX($D2:$D3)))>=$C2)*(ROW(INDIRECT("A"&MIN($C2:$C3)):INDIRECT("A"&MAX($D2:$D3)))>=$C3)*(ROW(INDIRECT("A"&MIN($C2:$C3)):INDIRECT("A"&MAX($D2:$D3)))<=$D2)*(ROW(INDIRECT("A"&MIN($C2:$C3)):INDIRECT("A"&MAX($D2:$D3)))<=$D3))

I'm sure there should be an easier way, but I can't think of it now

 

[Luke M]

Here another way to get # of months, w/o using DATEDIF (buggy) or INDIRECT (volatile)
=SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDEX(A:A,TEXT(D2,"yyyymm")):INDEX(A:A,TEXT(C2,"yyyymm"))),ROW(INDEX(A:A,TEXT(D3,"yyyymm")):INDEX(A:A,TEXT(C3,"yyyymm"))),0))))

 

[SirJB7]

Hi, Stensol!
Regarding DATEDIF function maybe you want to give a look at this:
http://chandoo.org/forum/threads/excel-2007.3402/#post-16831
Just advise if any issue or question about it.
Regards!

@Luke M
Hi!
I'm watching you... the orange alert DATEDIF light is on...
Regards!

 

[Sajan]

Hi,
Just for fun, here is one more approach for calculating number of overlapping months:
=TEXT((NPV(0,TEXT(MIN(D2,D3),{"y","m"})/{1,12})-NPV(0,TEXT(MAX(C2,C3),{"y","m"})/{1,12}))*12+1,"#;0")+0

Cheers,
Sajan.

 

[NARAYANK991]

Here another way to get # of months, w/o using DATEDIF (buggy) or INDIRECT (volatile)
=SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDEX(A:A,TEXT(D2,"yyyymm")):INDEX(A:A,TEXT(C2,"yyyymm"))),ROW(INDEX(A:A,TEXT(D3,"yyyymm")):INDEX(A:A,TEXT(C3,"yyyymm"))),0))))

Hi Luke ,

The formula considers an array of numbers which does not reflect the way the calendar changes e.g. from 201312 the next number should be 201401.

The formula will work with a set of dates which coincide in the same year , but when more than one year is considered , it will not ; check it out with dates such as :

1/1/2013    12/31/2014
1/1/2012      1/1/2015

Narayan

 

[Luke M]

Hi Luke ,

The formula considers an array of numbers which does not reflect the way the calendar changes e.g. from 201312 the next number should be 201401...

Doh! You are, of course, right. How silly of me to forget!