Tough Problem 5 (formula)

[pgc01]

This is an equivalent formula, I just did a small change, but I think it makes more sense like this:

=SUMPRODUCT(LOOKUP(((ROW(1:12)<MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>=DAY(A1))+(DAY(T1+1)=1))>0))*((YEAR(T1)-YEAR(A1))*12-MONTH(A1)+ROW(1:12)),{-12,0;24,3;60,7;84,10;180,20}))

 

[Andrew Fergus]

Hi Pedro

With a start date of 30 June 2004 and an 'as at' date of 28 Feb 2009, your two different formulas return different results - I believe the second formula is correct with 6 hours.

Also, try a start date of 31 July 2004 and an 'as at' date of 30 June 2012 and check your result.

Andrew

 

[pgc01]

Hi Andrew

Thanks for looking into the formulas.

With a start date of 30 June 2004 and an 'as at' date of 28 Feb 2009, your two different formulas return different results - I believe the second formula is correct with 6 hours.

I tried and got the same result in both, 6 hours.(?)

Also, try a start date of 31 July 2004 and an 'as at' date of 30 June 2012 and check your result.

That was wrong. I had read in the table 7 years (84 months) when it is, in fact 8 years (96 months) (in the other formula it should be 108).

This is the formula with the correction:

=SUMPRODUCT(LOOKUP(((ROW(1:12)<MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>=DAY(A1))+(DAY(T1+1)=1))>0))*((YEAR(T1)-YEAR(A1))*12-MONTH(A1)+ROW(1:12)),{-12,0;24,3;60,7;96,10;180,20}))<?XML:NAMESPACE PREFIX = MONTH(T1))+(ROW(1 /><MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)><MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>

Thank you again
Pedro
</MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>
</MONTH(T1))+(ROW(1:12)=MONTH(T1))*(((DAY(T1)>

 

[barry houdini]

Thank you Pedro, that seems to return exactly the results I expected in all scenarios (unless anybody knows any different ), although for robustness might it be better to replace ROW(1:12) with ROW(INDIRECT("1:12"))?

I have a version of my own which uses DATEDIF like Lewiy's. It works but I'm just trying to see if I can shorten it......I'll post back soon.

Thanks to everybody who has contributed so far, any more?

 

[pgc01]

for robustness might it be better to replace ROW(1:12) with ROW(INDIRECT("1:12"))?

Yes, Barry, I agree. Another option is to use directly the array {1,2...,12}. This way the formula would not be volatile. Or use this array inside a name.

 

[barry houdini]

Sorry for the delay, I know many of you were waiting with bated breath

Initially I thought that this formula was best

=IF(MONTH(T$1)*(DAY(A1)>DAY(T$1))=1,0,SUMPRODUCT(LOOKUP(ROW(INDIRECT(DATEDIF(A1,DATE(YEAR(T$1),1,0),"m")&":"&DATEDIF(A1+1,T$1+1,"m")-1))+1,{0,24,60,96,180;0,3,7,10,20})))

I wanted to remove the IF function but couldn't find a way......

But I noticed that the formula gave incorrect results in some rare cases, e.g. when A1 was 28th February (in a non leap year) and T1 was a date like 29th April 2009.

....so kudos to pgc for coming up with the best formula, in my opinion.

 

[Andrew Fergus]

....so kudos to pgc for coming up with the best formula, in my opinion.

I agree - try as I might I couldn't break Pedro's formula....