allocation of contract period in different financial year

[arun198322654]

Hi Guys

I need your help. I have one contract which is having 47 months maturity period. I am trying to allocation it in different financial years ie
a) Suppose Financial year start from 01 Nov and end on 31St Oct in next year
b) Contract start date: 01 Sept 2014 and end on 31st July 2018
c) Then my allocation should be
FY 14 ( 01st Sept 2014 to 31st Oct 2014) : 2 months
FY 14 ( 01st Nov 2014 to 31st Oct 2015) : 12 months
FY 15 ( 01st Nov 2015 to 31st Oct 2016) : 12 months
FY 16 ( 01st Nov 2016 to 31st Oct 2017) : 12 months
FY 17 ( 01st Nov 2017 to 31st Oct 2018) : 9 months

I am uploading the file
Pls help

 

[NARAYANK991]

Hi ,

See if this is OK. Verify thoroughly.

Narayan

 

[bosco_yip]

Hi,

1] Adjusting is making to the Month heading (F7:L7), of which changed to true date with custom cell formatting.

For example :
In F7, enter : 01/01/2014 and
Custom cell formatting >> "Financial Year "yy

2] In F8, formula copy across and down :

=IF(AND($C8<=(EDATE(F$7,22)-1),$D8>(EDATE(F$7,10)-1)),MIN(ROUND((EDATE(F$7,22)-1-$C8)/30.5,0),ROUND(($D8-EDATE(F$7,10)-1)/30.5,0),12),"")

3] See attachment

Regards
Bosco

 

[bosco_yip]

OR,

If you don't wanted to change your layout, a longer formula will be used.

In "Sheet 2" F8, formula copy across and down :

=IF(AND($C8<=(EDATE(--(1&"/"&RIGHT(F$7,2)),22)-1),$D8>(EDATE(--(1&"/"&RIGHT(F$7,2)),10)-1)),MIN(ROUND((EDATE(--(1&"/"&RIGHT(F$7,2)),22)-1-$C8)/30.5,0),ROUND(($D8-EDATE(--(1&"/"&RIGHT(F$7,2)),10)-1)/30.5,0),12),"")

See attached file

Regards
Bosco

 

[arun198322654]

Thanks guys for help