Split Amount between quarters or months within date range

[Sudhir Gawade]

Hi Friends,

Please help me with the excel formula that will Split Amount between quarters or months within date range.

I have start date, end date , frequency to split, Amount column and I have data for four years.My date range with in year only (start date and End date).

start date | end date | frequency to split |Amount |1/1/2018 | 1/2/2018 .....31/12/2022
1/1/2018 |31/12/2018|quarterly |10000
1/10/2018|30/9/2019 |quarterly |25000
1/05/2018|30/4/2019 |Monthly |35000


Thanks in Advance

Regards,
Sudhir G

 

[bosco_yip]

Please upload a file with 10 lines of data as per described and together with the expected results.

Regards
Bosco

 

[Sudhir Gawade]

Hi Bosco,

Please find attached sample data.

Thanks,
Sudhir Gawade

 

[bosco_yip]

Hi Bosco,

Please find attached sample data.

Thanks,
Sudhir Gawade

Try..........

1] In E3, copied down :

=IF(C3="quarterly",ROUNDUP(DATEDIF($A3,$B3,"m")/3,0),IF(C3="monthly",DATEDIF(EOMONTH($A3,-1)+1,EOMONTH($B3,0),"m")+1,1))

2] In F3, copied across and down :

=IF((EOMONTH($A3,-1)+1<=F$2)*($B3>=F$2),IF($C3="quarterly",IFERROR(1/(1/((MOD((MONTH(F$2)-MONTH($A3)),3)=0)*$D3/$E3)),""),IF($C3="monthly",$D3/$E3,$D3)),"")

Regards
Bosco

 

[Sudhir Gawade]

Thank you Bosco. its working fine. Great!

Regards,
Sudhir Gawade