Thank you Excel Wizard it works well.Please try at Q3
=YEARFRAC(MAX(Q$2,$B3),MIN(EDATE(Q$2,1),$C3+1))*12*(MIN(EDATE(Q$2,1),$C3+1)>MAX(Q$2,$B3))*$D3/12
or
=IFERROR((DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"ym")+DATEDIF(MAX(Q$2,$B7),MIN(EDATE(Q$2,1),$C7+1),"md")/DAY(EOMONTH(Q$2,0)))*$D7/12,0)
1-31 Jan is 31 days
15-31 Jan should be 17 days/31 days
Thank you John,Hi to all!
I think that year must be calculated over 360 days. If so... check this formula:
=$D3*MAX(,DAYS360(MAX($B3,Q$2),MIN(1+$C3,1+EOMONTH(Q$2,0))))/360
Check file. Blessings!
Blessings!
Hi, Ranjit!Thank you John,
360 days is new perspective and it works.
But in the row 7 sample fraction jan-20 value should be 5940.85
For rows 3 ,4 & 5 effectiveness of formula is confirmed in cross checks summed in cell AE & AF.
Regards
Ranjit
Hi John,Hi, Ranjit!
If you work with 360-year, all months must be 30 days (including February). Is like my calculation works with 16 days and not with 17 days. Blessings!