Auto populate commission (Dynamic Columns)

senaka.obeysekera

Board Regular
Joined
Apr 4, 2012
Messages
53
Hi All,

I have the below data set and I need your help to write a formula which will populate the "Contract Period" and distribute the commission (equally). The column should be dynamic and populate according to the expiry date month.
Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodJan-2018Feb-2018Mar-2018Apr-2018May-2018Jun-2018Jul-2018Aug-2018Sep-2018Oct-2018Nov-2018Dec-2018Jan-2019
03-Jan-201831-Jul-201818000003Head Office915 2,500.007
03-Jan-201831-May-201818000001DOWN SOUTH22597 1,500.00
03-Jan-201811-Jun-201818000005Head Office1196 2,500.00
03-Jan-201802-Apr-201818000004Head Office915 2,959.00
03-Jan-201802-Jul-201818000002Head Office913 2,500.00
04-Jan-201831-Dec-201818000006UP TOWN63345 14,877.00
05-Jan-201820-Dec-201818000008Head Office20328 19,054.00
05-Jan-201815-Apr-201818000009Head Office913 2,500.00
05-Jan-201809-May-201818000010Head Office915 2,500.00
08-Jan-201802-Apr-201818000013DOWN SOUTH22597 1,500.00
08-Jan-201803-Apr-201818000011Head Office1228 67,137.00
08-Jan-201803-Jan-201918000012Head Office1228 93,939.00
09-Jan-201820-Feb-201818000002Head Office20328 2,500.00
09-Jan-201808-Jan-201918000014Head Office41265 10,000.00
09-Jan-201802-Apr-201818000015Head Office1196 14,269.00
09-Jan-201820-Feb-201818000001Head Office20328 2,500.00
11-Jan-201802-Mar-201818000017Head Office1196 10,074.00
11-Jan-201818-May-201818000016Head Office18653 2,500.00
16-Jan-201831-Dec-201818000021Head Office1530 2,500.00
16-Jan-201831-Dec-201818000020Head Office1530 2,500.00
16-Jan-201831-Dec-201818000022Head Office1530 2,500.00
16-Jan-201803-May-201818000019Head Office913 2,500.00
17-Jan-201802-Jul-201818000027DOWN SOUTH22597 1,500.00
17-Jan-201801-Jun-201818000024Head Office913 5,962.00
17-Jan-201818-Jun-201818000025Head Office1118 15,720.00
17-Jan-201825-Apr-201818000026Head Office913 2,500.00
18-Jan-201830-May-201818000030DOWN SOUTH22597 1,500.00
18-Jan-201829-Jun-201818000029DOWN SOUTH22597 1,500.00
18-Jan-201828-Feb-201818000028VAVUNIYA16380 2,500.00
19-Jan-201831-Oct-201818000032DOWN SOUTH22597 2,402.00
19-Jan-201817-Jul-201918000031Head Office1674 97,418.00
19-Jan-201819-Feb-201818000033Head Office1196 12,642.00
22-Jan-201824-Jan-201918000034Head Office913 2,500.00
22-Jan-201802-May-201818000035Head Office913 2,500.00
23-Jan-201804-Jul-201818000038Head Office915 2,500.00
23-Jan-201824-May-201818000041Head Office24135 33,425.00
23-Jan-201831-May-201818000037Head Office913 2,500.00
23-Jan-201824-Jul-201918000040Head Office1280 5,255.00
23-Jan-201802-Apr-201818000039DOWN SOUTH22597 1,500.00
24-Jan-201825-Jan-202318000044Head Office18653 72,326.00
24-Jan-201824-May-201818000047Head Office913 2,500.00
24-Jan-201823-Jan-201918000045Head Office1674 66,329.00
24-Jan-201825-Jan-202318000043Head Office18653 49,281.00
24-Jan-201823-Jan-201918000046MID MARKET47535 20,000.00
25-Jan-201818-Jul-201818000052Head Office8728 35,600.00
25-Jan-201801-Jun-201818000050Head Office913 2,500.00
25-Jan-201809-Aug-201818000049Head Office1118 107,946.00
25-Jan-201830-May-201818000051Head Office913 2,500.00
25-Jan-201801-Feb-201818000003Head Office31951 2,500.00
25-Jan-201804-Jul-201818000048Head Office1118 54,586.00
26-Jan-201809-Jul-201818000054Head Office8728 152,569.00
26-Jan-201802-May-201818000053Head Office913 2,500.00
26-Jan-201825-Jan-201918000055MID MARKET64943 16,000.00
26-Jan-201830-Apr-201818000056EAST35208 9,918.00
26-Jan-201825-Jul-201818000057Head Office1670 2,500.00
29-Jan-201829-Apr-201818000061EAST35208 4,239.00
29-Jan-201801-May-201818000060Head Office913 2,500.00
29-Jan-201830-May-201818000059Head Office915 3,114.00
29-Jan-201830-Apr-201818000058Head Office17977 1,500.00
30-Jan-201830-Apr-201818000065Head Office345 45,276.00
30-Jan-201830-Jul-201818000062Head Office1196 57,491.00
30-Jan-201814-May-201818000067Head Office915 2,500.00
30-Jan-201830-Jan-201918000063Head Office1196 115,613.00
30-Jan-201826-Mar-201818000064Head Office685 2,500.00
30-Jan-201801-Feb-201918000066Head Office915 6,871.00
04-Jan-201804-May-201818000004Head Office915 1,500.00
09-Jan-201801-Jul-201917001620MAITLAND1616 58,220.00
05-Jan-201818-Apr-201817001033Head Office915 5,478.00
18-Jan-201831-Oct-201817001544DOWN SOUTH22597 1,500.00
10-Jan-201830-May-201817000816Head Office8728 32,918.00
19-Jan-201802-Feb-201817001187Head Office913 1,500.00
16-Jan-201815-Jan-201916000020Head Office27069 2,099.00
04-Jan-201831-Dec-201817000378Head Office7779 5,984.00
10-Jan-201830-May-201817000804Head Office8728 38,405.00
19-Jan-201828-Dec-201817001622Head Office915 10,767.00
08-Jan-201831-Mar-201817000849Head Office48747 1,988.00
19-Jan-201808-Apr-201817001032Head Office49316 77,572.00
17-Jan-201822-Mar-201817001174MAITLAND50878 145,479.45
10-Jan-201816-May-201817000776Head Office1172 24,740.00
03-Jan-201831-Jan-201817000354Head Office915 2,548.00
17-Jan-201802-Apr-201816000898DOWN SOUTH22597 1,500.00
17-Jan-201802-Apr-201817000068DOWN SOUTH22597 1,500.00
05-Jan-201816-Jan-201917000001MID MARKET36689 2,000.00
10-Jan-201830-May-201817000806Head Office8728 38,405.00
10-Jan-201830-May-201817000805Head Office8728 40,233.00
12-Jan-201816-May-201817000746Head Office1280 29,173.00
10-Jan-201816-May-201817000775Head Office1172 23,562.00
11-Jan-201820-Apr-201816000156Head Office3326 248,548.00
04-Jan-201824-Jan-201917000065MID MARKET37522 180,000.00
10-Jan-201830-May-201817000803Head Office8728 42,062.00
10-Jan-201830-May-201817000809Head Office8728 34,747.00
10-Jan-201830-May-201817000815Head Office8728 36,576.00
09-Jan-201811-Apr-201817000761MID MARKET30389 13,186.00
03-Jan-201822-Apr-201817001610Head Office915 1,500.00
10-Jan-201830-May-201817000810Head Office8728 38,405.00
10-Jan-201830-May-201817000817Head Office8728 38,405.00
25-Jan-201806-Mar-201817000233Head Office1280 1,500.00
29-Jan-201801-Aug-201817001115Head Office8728 140,240.00
30-Jan-201814-Aug-20181700866Head Office1118 30,994.00

<colgroup><col><col><col><col><col><col><col><col span="5"><col span="8"></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Excel 2010
ABCDEFGHIJKLMNOPQRST
1Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
23-Jan-1831-Jul-1818000003Head Office9152,500.007357.142857357.14357.14357.14357.14357.14357.1
33-Jan-1831-May-1818000001DOWN SOUTH225971,500.005300300300300300
43-Jan-1811-Jun-1818000005Head Office11962,500.006416.666667416.67416.67416.67416.67416.67
53-Jan-182-Apr-1818000004Head Office9152,959.004739.75739.75739.75739.75
63-Jan-182-Jul-1818000002Head Office9132,500.007357.142857357.14357.14357.14357.14357.14357.1
74-Jan-1831-Dec-1818000006UP TOWN6334514,877.00121239.751239.81239.81239.81239.81239.812401239.81239.81239.81239.81239.8
85-Jan-1820-Dec-1818000008Head Office2032819,054.00121587.833331587.81587.81587.81587.81587.815881587.81587.81587.81587.81587.8
95-Jan-1815-Apr-1818000009Head Office9132,500.004625625625625
105-Jan-189-May-1818000010Head Office9152,500.005500500500500500
118-Jan-182-Apr-1818000013DOWN SOUTH225971,500.004375375375375
128-Jan-183-Apr-1818000011Head Office122867,137.00416784.25167841678416784
138-Jan-183-Jan-1918000012Head Office122893,939.00137226.076927226.17226.17226.17226.17226.172267226.17226.17226.17226.17226.17226
149-Jan-1820-Feb-1818000002Head Office203282,500.00212501250
159-Jan-188-Jan-1918000014Head Office4126510,000.0013769.230769769.23769.23769.23769.23769.23769.2769.23769.23769.23769.23769.23769.2
169-Jan-182-Apr-1818000015Head Office119614,269.0043567.253567.33567.33567.3
179-Jan-1820-Feb-1818000001Head Office203282,500.00212501250
1811-Jan-182-Mar-1818000017Head Office119610,074.003335833583358
1911-Jan-1818-May-1818000016Head Office186532,500.005500500500500500
2016-Jan-1831-Dec-1818000021Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2116-Jan-1831-Dec-1818000020Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2216-Jan-1831-Dec-1818000022Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2316-Jan-183-May-1818000019Head Office9132,500.005500500500500500
2417-Jan-182-Jul-1818000027DOWN SOUTH225971,500.007214.285714214.29214.29214.29214.29214.29214.3
2517-Jan-181-Jun-1818000024Head Office9135,962.006993.666667993.67993.67993.67993.67993.67
2617-Jan-1818-Jun-1818000025Head Office111815,720.006262026202620262026202620
2717-Jan-1825-Apr-1818000026Head Office9132,500.004625625625625
2818-Jan-1830-May-1818000030DOWN SOUTH225971,500.005300300300300300
2918-Jan-1829-Jun-1818000029DOWN SOUTH225971,500.006250250250250250250
3018-Jan-1828-Feb-1818000028VAVUNIYA163802,500.00212501250
3119-Jan-1831-Oct-1818000032DOWN SOUTH225972,402.0010240.2240.2240.2240.2240.2240.2240.2240.2240.2240.2
3219-Jan-1817-Jul-1918000031Head Office167497,418.00195127.263165127.35127.35127.35127.35127.351275127.35127.35127.35127.35127.35127
3319-Jan-1819-Feb-1818000033Head Office119612,642.00263216321
3422-Jan-1824-Jan-1918000034Head Office9132,500.0013192.307692192.31192.31192.31192.31192.31192.3192.31192.31192.31192.31192.31192.3
3522-Jan-182-May-1818000035Head Office9132,500.005500500500500500
3623-Jan-184-Jul-1818000038Head Office9152,500.007357.142857357.14357.14357.14357.14357.14357.1
3723-Jan-1824-May-1818000041Head Office2413533,425.00566856685668566856685
3823-Jan-1831-May-1818000037Head Office9132,500.005500500500500500
3923-Jan-1824-Jul-1918000040Head Office12805,255.0019276.578947276.58276.58276.58276.58276.58276.6276.58276.58276.58276.58276.58276.6
4023-Jan-182-Apr-1818000039DOWN SOUTH225971,500.004375375375375
4124-Jan-1825-Jan-2318000044Head Office1865372,326.00611185.672131185.71185.71185.71185.71185.711861185.71185.71185.71185.71185.71186
4224-Jan-1824-May-1818000047Head Office9132,500.005500500500500500
4324-Jan-1823-Jan-1918000045Head Office167466,329.00135102.230775102.25102.25102.25102.25102.251025102.25102.25102.25102.25102.25102
4424-Jan-1825-Jan-2318000043Head Office1865349,281.0061807.885246807.89807.89807.89807.89807.89807.9807.89807.89807.89807.89807.89807.9
4524-Jan-1823-Jan-1918000046MID MARKET4753520,000.00131538.461541538.51538.51538.51538.51538.515381538.51538.51538.51538.51538.51538
4625-Jan-1818-Jul-1818000052Head Office872835,600.0075085.714295085.75085.75085.75085.75085.75086
4725-Jan-181-Jun-1818000050Head Office9132,500.006416.666667416.67416.67416.67416.67416.67
4825-Jan-189-Aug-1818000049Head Office1118107,946.00813493.2513493134931349313493134931349313493
4925-Jan-1830-May-1818000051Head Office9132,500.005500500500500500
5025-Jan-181-Feb-1818000003Head Office319512,500.00212501250
5125-Jan-184-Jul-1818000048Head Office111854,586.0077798779877987798779877987798
5226-Jan-189-Jul-1818000054Head Office8728152,569.00721795.5714217962179621796217962179621796
5326-Jan-182-May-1818000053Head Office9132,500.005500500500500500
5426-Jan-1825-Jan-1918000055MID MARKET6494316,000.00131230.769231230.81230.81230.81230.81230.812311230.81230.81230.81230.81230.81231
5526-Jan-1830-Apr-1818000056EAST352089,918.0042479.52479.52479.52479.5
5626-Jan-1825-Jul-1818000057Head Office16702,500.007357.142857357.14357.14357.14357.14357.14357.1
5729-Jan-1829-Apr-1818000061EAST352084,239.0041059.751059.81059.81059.8
5829-Jan-181-May-1818000060Head Office9132,500.005500500500500500
5929-Jan-1830-May-1818000059Head Office9153,114.005622.8622.8622.8622.8622.8
6029-Jan-1830-Apr-1818000058Head Office179771,500.004375375375375
6130-Jan-1830-Apr-1818000065Head Office34545,276.00411319113191131911319
6230-Jan-1830-Jul-1818000062Head Office119657,491.0078213821382138213821382138213
6330-Jan-1814-May-1818000067Head Office9152,500.005500500500500500
6430-Jan-1830-Jan-1918000063Head Office1196115,613.00138893.307698893.38893.38893.38893.38893.388938893.38893.38893.38893.38893.38893
6530-Jan-1826-Mar-1818000064Head Office6852,500.003833.333333833.33833.33
6630-Jan-181-Feb-1918000066Head Office9156,871.0014490.785714490.79490.79490.79490.79490.79490.8490.79490.79490.79490.79490.79490.8
674-Jan-184-May-1818000004Head Office9151,500.005300300300300300
689-Jan-181-Jul-1917001620MAITLAND161658,220.00193064.210533064.23064.23064.23064.23064.230643064.23064.23064.23064.23064.23064
695-Jan-1818-Apr-1817001033Head Office9155,478.0041369.51369.51369.51369.5
7018-Jan-1831-Oct-1817001544DOWN SOUTH225971,500.0010150150150150150150150150150150
7110-Jan-1830-May-1817000816Head Office872832,918.0056583.66583.66583.66583.66583.6
7219-Jan-182-Feb-1817001187Head Office9131,500.002750750
7316-Jan-1815-Jan-1916000020Head Office270692,099.0013161.461538161.46161.46161.46161.46161.46161.5161.46161.46161.46161.46161.46161.5
744-Jan-1831-Dec-1817000378Head Office77795,984.0012498.666667498.67498.67498.67498.67498.67498.7498.67498.67498.67498.67498.67
7510-Jan-1830-May-1817000804Head Office872838,405.00576817681768176817681
7619-Jan-1828-Dec-1817001622Head Office91510,767.0012897.25897.25897.25897.25897.25897.25897.3897.25897.25897.25897.25897.25
778-Jan-1831-Mar-1817000849Head Office487471,988.003662.666667662.67662.67
7819-Jan-188-Apr-1817001032Head Office4931677,572.00419393193931939319393
7917-Jan-1822-Mar-1817001174MAITLAND50878145,479.45348493.154849348493
8010-Jan-1816-May-1817000776Head Office117224,740.00549484948494849484948
813-Jan-1831-Jan-1817000354Head Office9152,548.0012548
8217-Jan-182-Apr-1816000898DOWN SOUTH225971,500.004375375375375
8317-Jan-182-Apr-1817000068DOWN SOUTH225971,500.004375375375375
845-Jan-1816-Jan-1917000001MID MARKET366892,000.0013153.846154153.85153.85153.85153.85153.85153.8153.85153.85153.85153.85153.85153.8
8510-Jan-1830-May-1817000806Head Office872838,405.00576817681768176817681
8610-Jan-1830-May-1817000805Head Office872840,233.0058046.68046.68046.68046.68046.6
8712-Jan-1816-May-1817000746Head Office128029,173.0055834.65834.65834.65834.65834.6
8810-Jan-1816-May-1817000775Head Office117223,562.0054712.44712.44712.44712.44712.4
8911-Jan-1820-Apr-1816000156Head Office3326248,548.00462137621376213762137
904-Jan-1824-Jan-1917000065MID MARKET37522180,000.001313846.1538138461384613846138461384613846138461384613846138461384613846
9110-Jan-1830-May-1817000803Head Office872842,062.0058412.48412.48412.48412.48412.4
9210-Jan-1830-May-1817000809Head Office872834,747.0056949.46949.46949.46949.46949.4
9310-Jan-1830-May-1817000815Head Office872836,576.0057315.27315.27315.27315.27315.2
949-Jan-1811-Apr-1817000761MID MARKET3038913,186.0043296.53296.53296.53296.5
953-Jan-1822-Apr-1817001610Head Office9151,500.004375375375375
9610-Jan-1830-May-1817000810Head Office872838,405.00576817681768176817681
9710-Jan-1830-May-1817000817Head Office872838,405.00576817681768176817681
9825-Jan-186-Mar-1817000233Head Office12801,500.003500500500
9929-Jan-181-Aug-1817001115Head Office8728140,240.0081753017530175301753017530175301753017530
10030-Jan-1814-Aug-181700866Head Office111830,994.0083874.253874.33874.33874.33874.33874.338743874.3
Sheet15
Cell Formulas
RangeFormula
G2=DATEDIF(A2-DAY(A2)+1,B2,"m")+1
H2=IF(AND(H$1>=EOMONTH($A2,0),H$1<=EOMONTH($B2,0)),$F2/$G2,"")


(the header dates I used were month end)
 
Last edited:
Upvote 0
Thank you for your support.

However the commission starts less one month.

Can you help.






Excel 2010
ABCDEFGHIJKLMNOPQRST
1Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
23-Jan-1831-Jul-1818000003Head Office9152,500.007357.142857357.14357.14357.14357.14357.14357.1
33-Jan-1831-May-1818000001DOWN SOUTH225971,500.005300300300300300
43-Jan-1811-Jun-1818000005Head Office11962,500.006416.666667416.67416.67416.67416.67416.67
53-Jan-182-Apr-1818000004Head Office9152,959.004739.75739.75739.75739.75
63-Jan-182-Jul-1818000002Head Office9132,500.007357.142857357.14357.14357.14357.14357.14357.1
74-Jan-1831-Dec-1818000006UP TOWN6334514,877.00121239.751239.81239.81239.81239.81239.812401239.81239.81239.81239.81239.8
85-Jan-1820-Dec-1818000008Head Office2032819,054.00121587.833331587.81587.81587.81587.81587.815881587.81587.81587.81587.81587.8
95-Jan-1815-Apr-1818000009Head Office9132,500.004625625625625
105-Jan-189-May-1818000010Head Office9152,500.005500500500500500
118-Jan-182-Apr-1818000013DOWN SOUTH225971,500.004375375375375
128-Jan-183-Apr-1818000011Head Office122867,137.00416784.25167841678416784
138-Jan-183-Jan-1918000012Head Office122893,939.00137226.076927226.17226.17226.17226.17226.172267226.17226.17226.17226.17226.17226
149-Jan-1820-Feb-1818000002Head Office203282,500.00212501250
159-Jan-188-Jan-1918000014Head Office4126510,000.0013769.230769769.23769.23769.23769.23769.23769.2769.23769.23769.23769.23769.23769.2
169-Jan-182-Apr-1818000015Head Office119614,269.0043567.253567.33567.33567.3
179-Jan-1820-Feb-1818000001Head Office203282,500.00212501250
1811-Jan-182-Mar-1818000017Head Office119610,074.003335833583358
1911-Jan-1818-May-1818000016Head Office186532,500.005500500500500500
2016-Jan-1831-Dec-1818000021Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2116-Jan-1831-Dec-1818000020Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2216-Jan-1831-Dec-1818000022Head Office15302,500.0012208.333333208.33208.33208.33208.33208.33208.3208.33208.33208.33208.33208.33
2316-Jan-183-May-1818000019Head Office9132,500.005500500500500500
2417-Jan-182-Jul-1818000027DOWN SOUTH225971,500.007214.285714214.29214.29214.29214.29214.29214.3
2517-Jan-181-Jun-1818000024Head Office9135,962.006993.666667993.67993.67993.67993.67993.67
2617-Jan-1818-Jun-1818000025Head Office111815,720.006262026202620262026202620
2717-Jan-1825-Apr-1818000026Head Office9132,500.004625625625625
2818-Jan-1830-May-1818000030DOWN SOUTH225971,500.005300300300300300
2918-Jan-1829-Jun-1818000029DOWN SOUTH225971,500.006250250250250250250
3018-Jan-1828-Feb-1818000028VAVUNIYA163802,500.00212501250
3119-Jan-1831-Oct-1818000032DOWN SOUTH225972,402.0010240.2240.2240.2240.2240.2240.2240.2240.2240.2240.2
3219-Jan-1817-Jul-1918000031Head Office167497,418.00195127.263165127.35127.35127.35127.35127.351275127.35127.35127.35127.35127.35127
3319-Jan-1819-Feb-1818000033Head Office119612,642.00263216321
3422-Jan-1824-Jan-1918000034Head Office9132,500.0013192.307692192.31192.31192.31192.31192.31192.3192.31192.31192.31192.31192.31192.3
3522-Jan-182-May-1818000035Head Office9132,500.005500500500500500
3623-Jan-184-Jul-1818000038Head Office9152,500.007357.142857357.14357.14357.14357.14357.14357.1
3723-Jan-1824-May-1818000041Head Office2413533,425.00566856685668566856685
3823-Jan-1831-May-1818000037Head Office9132,500.005500500500500500
3923-Jan-1824-Jul-1918000040Head Office12805,255.0019276.578947276.58276.58276.58276.58276.58276.6276.58276.58276.58276.58276.58276.6
4023-Jan-182-Apr-1818000039DOWN SOUTH225971,500.004375375375375
4124-Jan-1825-Jan-2318000044Head Office1865372,326.00611185.672131185.71185.71185.71185.71185.711861185.71185.71185.71185.71185.71186
4224-Jan-1824-May-1818000047Head Office9132,500.005500500500500500
4324-Jan-1823-Jan-1918000045Head Office167466,329.00135102.230775102.25102.25102.25102.25102.251025102.25102.25102.25102.25102.25102
4424-Jan-1825-Jan-2318000043Head Office1865349,281.0061807.885246807.89807.89807.89807.89807.89807.9807.89807.89807.89807.89807.89807.9
4524-Jan-1823-Jan-1918000046MID MARKET4753520,000.00131538.461541538.51538.51538.51538.51538.515381538.51538.51538.51538.51538.51538
4625-Jan-1818-Jul-1818000052Head Office872835,600.0075085.714295085.75085.75085.75085.75085.75086
4725-Jan-181-Jun-1818000050Head Office9132,500.006416.666667416.67416.67416.67416.67416.67
4825-Jan-189-Aug-1818000049Head Office1118107,946.00813493.2513493134931349313493134931349313493
4925-Jan-1830-May-1818000051Head Office9132,500.005500500500500500
5025-Jan-181-Feb-1818000003Head Office319512,500.00212501250
5125-Jan-184-Jul-1818000048Head Office111854,586.0077798779877987798779877987798
5226-Jan-189-Jul-1818000054Head Office8728152,569.00721795.5714217962179621796217962179621796
5326-Jan-182-May-1818000053Head Office9132,500.005500500500500500
5426-Jan-1825-Jan-1918000055MID MARKET6494316,000.00131230.769231230.81230.81230.81230.81230.812311230.81230.81230.81230.81230.81231
5526-Jan-1830-Apr-1818000056EAST352089,918.0042479.52479.52479.52479.5
5626-Jan-1825-Jul-1818000057Head Office16702,500.007357.142857357.14357.14357.14357.14357.14357.1
5729-Jan-1829-Apr-1818000061EAST352084,239.0041059.751059.81059.81059.8
5829-Jan-181-May-1818000060Head Office9132,500.005500500500500500
5929-Jan-1830-May-1818000059Head Office9153,114.005622.8622.8622.8622.8622.8
6029-Jan-1830-Apr-1818000058Head Office179771,500.004375375375375
6130-Jan-1830-Apr-1818000065Head Office34545,276.00411319113191131911319
6230-Jan-1830-Jul-1818000062Head Office119657,491.0078213821382138213821382138213
6330-Jan-1814-May-1818000067Head Office9152,500.005500500500500500
6430-Jan-1830-Jan-1918000063Head Office1196115,613.00138893.307698893.38893.38893.38893.38893.388938893.38893.38893.38893.38893.38893
6530-Jan-1826-Mar-1818000064Head Office6852,500.003833.333333833.33833.33
6630-Jan-181-Feb-1918000066Head Office9156,871.0014490.785714490.79490.79490.79490.79490.79490.8490.79490.79490.79490.79490.79490.8
674-Jan-184-May-1818000004Head Office9151,500.005300300300300300
689-Jan-181-Jul-1917001620MAITLAND161658,220.00193064.210533064.23064.23064.23064.23064.230643064.23064.23064.23064.23064.23064
695-Jan-1818-Apr-1817001033Head Office9155,478.0041369.51369.51369.51369.5
7018-Jan-1831-Oct-1817001544DOWN SOUTH225971,500.0010150150150150150150150150150150
7110-Jan-1830-May-1817000816Head Office872832,918.0056583.66583.66583.66583.66583.6
7219-Jan-182-Feb-1817001187Head Office9131,500.002750750
7316-Jan-1815-Jan-1916000020Head Office270692,099.0013161.461538161.46161.46161.46161.46161.46161.5161.46161.46161.46161.46161.46161.5
744-Jan-1831-Dec-1817000378Head Office77795,984.0012498.666667498.67498.67498.67498.67498.67498.7498.67498.67498.67498.67498.67
7510-Jan-1830-May-1817000804Head Office872838,405.00576817681768176817681
7619-Jan-1828-Dec-1817001622Head Office91510,767.0012897.25897.25897.25897.25897.25897.25897.3897.25897.25897.25897.25897.25
778-Jan-1831-Mar-1817000849Head Office487471,988.003662.666667662.67662.67
7819-Jan-188-Apr-1817001032Head Office4931677,572.00419393193931939319393
7917-Jan-1822-Mar-1817001174MAITLAND50878145,479.45348493.154849348493
8010-Jan-1816-May-1817000776Head Office117224,740.00549484948494849484948
813-Jan-1831-Jan-1817000354Head Office9152,548.0012548
8217-Jan-182-Apr-1816000898DOWN SOUTH225971,500.004375375375375
8317-Jan-182-Apr-1817000068DOWN SOUTH225971,500.004375375375375
845-Jan-1816-Jan-1917000001MID MARKET366892,000.0013153.846154153.85153.85153.85153.85153.85153.8153.85153.85153.85153.85153.85153.8
8510-Jan-1830-May-1817000806Head Office872838,405.00576817681768176817681
8610-Jan-1830-May-1817000805Head Office872840,233.0058046.68046.68046.68046.68046.6
8712-Jan-1816-May-1817000746Head Office128029,173.0055834.65834.65834.65834.65834.6
8810-Jan-1816-May-1817000775Head Office117223,562.0054712.44712.44712.44712.44712.4
8911-Jan-1820-Apr-1816000156Head Office3326248,548.00462137621376213762137
904-Jan-1824-Jan-1917000065MID MARKET37522180,000.001313846.1538138461384613846138461384613846138461384613846138461384613846
9110-Jan-1830-May-1817000803Head Office872842,062.0058412.48412.48412.48412.48412.4
9210-Jan-1830-May-1817000809Head Office872834,747.0056949.46949.46949.46949.46949.4
9310-Jan-1830-May-1817000815Head Office872836,576.0057315.27315.27315.27315.27315.2
949-Jan-1811-Apr-1817000761MID MARKET3038913,186.0043296.53296.53296.53296.5
953-Jan-1822-Apr-1817001610Head Office9151,500.004375375375375
9610-Jan-1830-May-1817000810Head Office872838,405.00576817681768176817681
9710-Jan-1830-May-1817000817Head Office872838,405.00576817681768176817681
9825-Jan-186-Mar-1817000233Head Office12801,500.003500500500
9929-Jan-181-Aug-1817001115Head Office8728140,240.0081753017530175301753017530175301753017530
10030-Jan-1814-Aug-181700866Head Office111830,994.0083874.253874.33874.33874.33874.33874.338743874.3

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet15

Worksheet Formulas
CellFormula
G2=DATEDIF(A2-DAY(A2)+1,B2,"m")+1
H2=IF(AND(H$1>=EOMONTH($A2,0),H$1<=EOMONTH($B2,0)),$F2/$G2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



(the header dates I used were month end)
 
Upvote 0
Do the end dates have to be shifted over 1 also?


Excel 2010
ABCDEFGHIJKLMNOPQRSTU
1Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodDec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
23-Jan-1831-Jul-1818000003Head Office9152,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
33-Jan-1831-May-1818000001DOWN SOUTH225971,500.005300300300300300
43-Jan-1811-Jun-1818000005Head Office11962,500.006416.666667416.666667416.666667416.666667416.666667416.666667
53-Jan-182-Apr-1818000004Head Office9152,959.004739.75739.75739.75739.75
63-Jan-182-Jul-1818000002Head Office9132,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
74-Jan-1831-Dec-1818000006UP TOWN6334514,877.00121239.751239.751239.751239.751239.751239.751239.751239.751239.751239.751239.751239.75
85-Jan-1820-Dec-1818000008Head Office2032819,054.00121587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.83333
95-Jan-1815-Apr-1818000009Head Office9132,500.004625625625625
105-Jan-189-May-1818000010Head Office9152,500.005500500500500500
118-Jan-182-Apr-1818000013DOWN SOUTH225971,500.004375375375375
128-Jan-183-Apr-1818000011Head Office122867,137.00416784.2516784.2516784.2516784.25
138-Jan-183-Jan-1918000012Head Office122893,939.00137226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.07692
149-Jan-1820-Feb-1818000002Head Office203282,500.00212501250
159-Jan-188-Jan-1918000014Head Office4126510,000.0013769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769
169-Jan-182-Apr-1818000015Head Office119614,269.0043567.253567.253567.253567.25
179-Jan-1820-Feb-1818000001Head Office203282,500.00212501250
1811-Jan-182-Mar-1818000017Head Office119610,074.003335833583358
1911-Jan-1818-May-1818000016Head Office186532,500.005500500500500500
2016-Jan-1831-Dec-1818000021Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2116-Jan-1831-Dec-1818000020Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2216-Jan-1831-Dec-1818000022Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2316-Jan-183-May-1818000019Head Office9132,500.005500500500500500
2417-Jan-182-Jul-1818000027DOWN SOUTH225971,500.007214.285714214.285714214.285714214.285714214.285714214.285714214.285714
2517-Jan-181-Jun-1818000024Head Office9135,962.006993.666667993.666667993.666667993.666667993.666667993.666667
2617-Jan-1818-Jun-1818000025Head Office111815,720.006262026202620262026202620
2717-Jan-1825-Apr-1818000026Head Office9132,500.004625625625625
2818-Jan-1830-May-1818000030DOWN SOUTH225971,500.005300300300300300
2918-Jan-1829-Jun-1818000029DOWN SOUTH225971,500.006250250250250250250
3018-Jan-1828-Feb-1818000028VAVUNIYA163802,500.00212501250
3119-Jan-1831-Oct-1818000032DOWN SOUTH225972,402.0010240.2240.2240.2240.2240.2240.2240.2240.2240.2240.2
3219-Jan-1817-Jul-1918000031Head Office167497,418.00195127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127
3319-Jan-1819-Feb-1818000033Head Office119612,642.00263216321
3422-Jan-1824-Jan-1918000034Head Office9132,500.0013192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692
3522-Jan-182-May-1818000035Head Office9132,500.005500500500500500
3623-Jan-184-Jul-1818000038Head Office9152,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
3723-Jan-1824-May-1818000041Head Office2413533,425.00566856685668566856685
3823-Jan-1831-May-1818000037Head Office9132,500.005500500500500500
3923-Jan-1824-Jul-1918000040Head Office12805,255.0019276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.6
4023-Jan-182-Apr-1818000039DOWN SOUTH225971,500.004375375375375
4124-Jan-1825-Jan-2318000044Head Office1865372,326.00611185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131186
4224-Jan-1824-May-1818000047Head Office9132,500.005500500500500500
4324-Jan-1823-Jan-1918000045Head Office167466,329.00135102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.23077
4424-Jan-1825-Jan-2318000043Head Office1865349,281.0061807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.9
4524-Jan-1823-Jan-1918000046MID MARKET4753520,000.00131538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.46154
4625-Jan-1818-Jul-1818000052Head Office872835,600.0075085.714295085.714295085.714295085.714295085.714295085.714295085.71429
4725-Jan-181-Jun-1818000050Head Office9132,500.006416.666667416.666667416.666667416.666667416.666667416.666667
4825-Jan-189-Aug-1818000049Head Office1118107,946.00813493.2513493.2513493.2513493.2513493.2513493.2513493.2513493.25
4925-Jan-1830-May-1818000051Head Office9132,500.005500500500500500
5025-Jan-181-Feb-1818000003Head Office319512,500.00212501250
5125-Jan-184-Jul-1818000048Head Office111854,586.0077798779877987798779877987798
5226-Jan-189-Jul-1818000054Head Office8728152,569.00721795.571421795.571421795.571421795.571421795.571421795.571421795.5714
5326-Jan-182-May-1818000053Head Office9132,500.005500500500500500
5426-Jan-1825-Jan-1918000055MID MARKET6494316,000.00131230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.76923
5526-Jan-1830-Apr-1818000056EAST352089,918.0042479.52479.52479.52479.5
5626-Jan-1825-Jul-1818000057Head Office16702,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
5729-Jan-1829-Apr-1818000061EAST352084,239.0041059.751059.751059.751059.75
5829-Jan-181-May-1818000060Head Office9132,500.005500500500500500
5929-Jan-1830-May-1818000059Head Office9153,114.005622.8622.8622.8622.8622.8
6029-Jan-1830-Apr-1818000058Head Office179771,500.004375375375375
6130-Jan-1830-Apr-1818000065Head Office34545,276.00411319113191131911319
6230-Jan-1830-Jul-1818000062Head Office119657,491.0078213821382138213821382138213
6330-Jan-1814-May-1818000067Head Office9152,500.005500500500500500
6430-Jan-1830-Jan-1918000063Head Office1196115,613.00138893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.30769
6530-Jan-1826-Mar-1818000064Head Office6852,500.003833.333333833.333333833.333333
6630-Jan-181-Feb-1918000066Head Office9156,871.0014490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.8
674-Jan-184-May-1818000004Head Office9151,500.005300300300300300
689-Jan-181-Jul-1917001620MAITLAND161658,220.00193064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064
695-Jan-1818-Apr-1817001033Head Office9155,478.0041369.51369.51369.51369.5
7018-Jan-1831-Oct-1817001544DOWN SOUTH225971,500.0010150150150150150150150150150150
7110-Jan-1830-May-1817000816Head Office872832,918.0056583.66583.66583.66583.66583.6
7219-Jan-182-Feb-1817001187Head Office9131,500.002750750
7316-Jan-1815-Jan-1916000020Head Office270692,099.0013161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538
744-Jan-1831-Dec-1817000378Head Office77795,984.0012498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667
7510-Jan-1830-May-1817000804Head Office872838,405.00576817681768176817681
7619-Jan-1828-Dec-1817001622Head Office91510,767.0012897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25
778-Jan-1831-Mar-1817000849Head Office487471,988.003662.666667662.666667662.666667
7819-Jan-188-Apr-1817001032Head Office4931677,572.00419393193931939319393
7917-Jan-1822-Mar-1817001174MAITLAND50878145,479.45348493.1548493.1548493.15
8010-Jan-1816-May-1817000776Head Office117224,740.00549484948494849484948
813-Jan-1831-Jan-1817000354Head Office9152,548.0012548
8217-Jan-182-Apr-1816000898DOWN SOUTH225971,500.004375375375375
8317-Jan-182-Apr-1817000068DOWN SOUTH225971,500.004375375375375
845-Jan-1816-Jan-1917000001MID MARKET366892,000.0013153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154
8510-Jan-1830-May-1817000806Head Office872838,405.00576817681768176817681
8610-Jan-1830-May-1817000805Head Office872840,233.0058046.68046.68046.68046.68046.6
8712-Jan-1816-May-1817000746Head Office128029,173.0055834.65834.65834.65834.65834.6
8810-Jan-1816-May-1817000775Head Office117223,562.0054712.44712.44712.44712.44712.4
8911-Jan-1820-Apr-1816000156Head Office3326248,548.00462137621376213762137
904-Jan-1824-Jan-1917000065MID MARKET37522180,000.001313846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.1538
9110-Jan-1830-May-1817000803Head Office872842,062.0058412.48412.48412.48412.48412.4
9210-Jan-1830-May-1817000809Head Office872834,747.0056949.46949.46949.46949.46949.4
9310-Jan-1830-May-1817000815Head Office872836,576.0057315.27315.27315.27315.27315.2
949-Jan-1811-Apr-1817000761MID MARKET3038913,186.0043296.53296.53296.53296.5
953-Jan-1822-Apr-1817001610Head Office9151,500.004375375375375
9610-Jan-1830-May-1817000810Head Office872838,405.00576817681768176817681
9710-Jan-1830-May-1817000817Head Office872838,405.00576817681768176817681
9825-Jan-186-Mar-1817000233Head Office12801,500.003500500500
9929-Jan-181-Aug-1817001115Head Office8728140,240.0081753017530175301753017530175301753017530
10030-Jan-1814-Aug-181700866Head Office111830,994.0083874.253874.253874.253874.253874.253874.253874.253874.25
Sheet15
Cell Formulas
RangeFormula
G2=DATEDIF(A2-DAY(A2)+1,B2,"m")+1
H2=IF(AND(H$1>=EOMONTH($A2,-1),H$1<=EOMONTH($B2,-1)),$F2/$G2,"")
 
Upvote 0
I need the commission to be spread as follows ;

Eg : Issue Date : 19th June 2018
Expiry Date : 1st January 2019
Commission Amount : 800

The commission amount of 800 should be distributed from June 2018 to January 2019 (100*8)





Do the end dates have to be shifted over 1 also?

Excel 2010
ABCDEFGHIJKLMNOPQRSTU
1Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodDec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
23-Jan-1831-Jul-1818000003Head Office9152,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
33-Jan-1831-May-1818000001DOWN SOUTH225971,500.005300300300300300
43-Jan-1811-Jun-1818000005Head Office11962,500.006416.666667416.666667416.666667416.666667416.666667416.666667
53-Jan-182-Apr-1818000004Head Office9152,959.004739.75739.75739.75739.75
63-Jan-182-Jul-1818000002Head Office9132,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
74-Jan-1831-Dec-1818000006UP TOWN6334514,877.00121239.751239.751239.751239.751239.751239.751239.751239.751239.751239.751239.751239.75
85-Jan-1820-Dec-1818000008Head Office2032819,054.00121587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.833331587.83333
95-Jan-1815-Apr-1818000009Head Office9132,500.004625625625625
105-Jan-189-May-1818000010Head Office9152,500.005500500500500500
118-Jan-182-Apr-1818000013DOWN SOUTH225971,500.004375375375375
128-Jan-183-Apr-1818000011Head Office122867,137.00416784.2516784.2516784.2516784.25
138-Jan-183-Jan-1918000012Head Office122893,939.00137226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.076927226.07692
149-Jan-1820-Feb-1818000002Head Office203282,500.00212501250
159-Jan-188-Jan-1918000014Head Office4126510,000.0013769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769769.230769
169-Jan-182-Apr-1818000015Head Office119614,269.0043567.253567.253567.253567.25
179-Jan-1820-Feb-1818000001Head Office203282,500.00212501250
1811-Jan-182-Mar-1818000017Head Office119610,074.003335833583358
1911-Jan-1818-May-1818000016Head Office186532,500.005500500500500500
2016-Jan-1831-Dec-1818000021Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2116-Jan-1831-Dec-1818000020Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2216-Jan-1831-Dec-1818000022Head Office15302,500.0012208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333208.333333
2316-Jan-183-May-1818000019Head Office9132,500.005500500500500500
2417-Jan-182-Jul-1818000027DOWN SOUTH225971,500.007214.285714214.285714214.285714214.285714214.285714214.285714214.285714
2517-Jan-181-Jun-1818000024Head Office9135,962.006993.666667993.666667993.666667993.666667993.666667993.666667
2617-Jan-1818-Jun-1818000025Head Office111815,720.006262026202620262026202620
2717-Jan-1825-Apr-1818000026Head Office9132,500.004625625625625
2818-Jan-1830-May-1818000030DOWN SOUTH225971,500.005300300300300300
2918-Jan-1829-Jun-1818000029DOWN SOUTH225971,500.006250250250250250250
3018-Jan-1828-Feb-1818000028VAVUNIYA163802,500.00212501250
3119-Jan-1831-Oct-1818000032DOWN SOUTH225972,402.0010240.2240.2240.2240.2240.2240.2240.2240.2240.2240.2
3219-Jan-1817-Jul-1918000031Head Office167497,418.00195127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127.263165127
3319-Jan-1819-Feb-1818000033Head Office119612,642.00263216321
3422-Jan-1824-Jan-1918000034Head Office9132,500.0013192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692192.307692
3522-Jan-182-May-1818000035Head Office9132,500.005500500500500500
3623-Jan-184-Jul-1818000038Head Office9152,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
3723-Jan-1824-May-1818000041Head Office2413533,425.00566856685668566856685
3823-Jan-1831-May-1818000037Head Office9132,500.005500500500500500
3923-Jan-1824-Jul-1918000040Head Office12805,255.0019276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.578947276.6
4023-Jan-182-Apr-1818000039DOWN SOUTH225971,500.004375375375375
4124-Jan-1825-Jan-2318000044Head Office1865372,326.00611185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131185.672131186
4224-Jan-1824-May-1818000047Head Office9132,500.005500500500500500
4324-Jan-1823-Jan-1918000045Head Office167466,329.00135102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.230775102.23077
4424-Jan-1825-Jan-2318000043Head Office1865349,281.0061807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.885246807.9
4524-Jan-1823-Jan-1918000046MID MARKET4753520,000.00131538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.461541538.46154
4625-Jan-1818-Jul-1818000052Head Office872835,600.0075085.714295085.714295085.714295085.714295085.714295085.714295085.71429
4725-Jan-181-Jun-1818000050Head Office9132,500.006416.666667416.666667416.666667416.666667416.666667416.666667
4825-Jan-189-Aug-1818000049Head Office1118107,946.00813493.2513493.2513493.2513493.2513493.2513493.2513493.2513493.25
4925-Jan-1830-May-1818000051Head Office9132,500.005500500500500500
5025-Jan-181-Feb-1818000003Head Office319512,500.00212501250
5125-Jan-184-Jul-1818000048Head Office111854,586.0077798779877987798779877987798
5226-Jan-189-Jul-1818000054Head Office8728152,569.00721795.571421795.571421795.571421795.571421795.571421795.571421795.5714
5326-Jan-182-May-1818000053Head Office9132,500.005500500500500500
5426-Jan-1825-Jan-1918000055MID MARKET6494316,000.00131230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.769231230.76923
5526-Jan-1830-Apr-1818000056EAST352089,918.0042479.52479.52479.52479.5
5626-Jan-1825-Jul-1818000057Head Office16702,500.007357.142857357.142857357.142857357.142857357.142857357.142857357.142857
5729-Jan-1829-Apr-1818000061EAST352084,239.0041059.751059.751059.751059.75
5829-Jan-181-May-1818000060Head Office9132,500.005500500500500500
5929-Jan-1830-May-1818000059Head Office9153,114.005622.8622.8622.8622.8622.8
6029-Jan-1830-Apr-1818000058Head Office179771,500.004375375375375
6130-Jan-1830-Apr-1818000065Head Office34545,276.00411319113191131911319
6230-Jan-1830-Jul-1818000062Head Office119657,491.0078213821382138213821382138213
6330-Jan-1814-May-1818000067Head Office9152,500.005500500500500500
6430-Jan-1830-Jan-1918000063Head Office1196115,613.00138893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.307698893.30769
6530-Jan-1826-Mar-1818000064Head Office6852,500.003833.333333833.333333833.333333
6630-Jan-181-Feb-1918000066Head Office9156,871.0014490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.785714490.8
674-Jan-184-May-1818000004Head Office9151,500.005300300300300300
689-Jan-181-Jul-1917001620MAITLAND161658,220.00193064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064.210533064
695-Jan-1818-Apr-1817001033Head Office9155,478.0041369.51369.51369.51369.5
7018-Jan-1831-Oct-1817001544DOWN SOUTH225971,500.0010150150150150150150150150150150
7110-Jan-1830-May-1817000816Head Office872832,918.0056583.66583.66583.66583.66583.6
7219-Jan-182-Feb-1817001187Head Office9131,500.002750750
7316-Jan-1815-Jan-1916000020Head Office270692,099.0013161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538161.461538
744-Jan-1831-Dec-1817000378Head Office77795,984.0012498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667498.666667
7510-Jan-1830-May-1817000804Head Office872838,405.00576817681768176817681
7619-Jan-1828-Dec-1817001622Head Office91510,767.0012897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25897.25
778-Jan-1831-Mar-1817000849Head Office487471,988.003662.666667662.666667662.666667
7819-Jan-188-Apr-1817001032Head Office4931677,572.00419393193931939319393
7917-Jan-1822-Mar-1817001174MAITLAND50878145,479.45348493.1548493.1548493.15
8010-Jan-1816-May-1817000776Head Office117224,740.00549484948494849484948
813-Jan-1831-Jan-1817000354Head Office9152,548.0012548
8217-Jan-182-Apr-1816000898DOWN SOUTH225971,500.004375375375375
8317-Jan-182-Apr-1817000068DOWN SOUTH225971,500.004375375375375
845-Jan-1816-Jan-1917000001MID MARKET366892,000.0013153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154153.846154
8510-Jan-1830-May-1817000806Head Office872838,405.00576817681768176817681
8610-Jan-1830-May-1817000805Head Office872840,233.0058046.68046.68046.68046.68046.6
8712-Jan-1816-May-1817000746Head Office128029,173.0055834.65834.65834.65834.65834.6
8810-Jan-1816-May-1817000775Head Office117223,562.0054712.44712.44712.44712.44712.4
8911-Jan-1820-Apr-1816000156Head Office3326248,548.00462137621376213762137
904-Jan-1824-Jan-1917000065MID MARKET37522180,000.001313846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.153813846.1538
9110-Jan-1830-May-1817000803Head Office872842,062.0058412.48412.48412.48412.48412.4
9210-Jan-1830-May-1817000809Head Office872834,747.0056949.46949.46949.46949.46949.4
9310-Jan-1830-May-1817000815Head Office872836,576.0057315.27315.27315.27315.27315.2
949-Jan-1811-Apr-1817000761MID MARKET3038913,186.0043296.53296.53296.53296.5
953-Jan-1822-Apr-1817001610Head Office9151,500.004375375375375
9610-Jan-1830-May-1817000810Head Office872838,405.00576817681768176817681
9710-Jan-1830-May-1817000817Head Office872838,405.00576817681768176817681
9825-Jan-186-Mar-1817000233Head Office12801,500.003500500500
9929-Jan-181-Aug-1817001115Head Office8728140,240.0081753017530175301753017530175301753017530
10030-Jan-1814-Aug-181700866Head Office111830,994.0083874.253874.253874.253874.253874.253874.253874.253874.25

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet15

Worksheet Formulas
CellFormula
G2=DATEDIF(A2-DAY(A2)+1,B2,"m")+1
H2=IF(AND(H$1>=EOMONTH($A2,-1),H$1<=EOMONTH($B2,-1)),$F2/$G2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
The first formula I gave you does that:


Excel 2010
ABCDEFGHIJKLMNOPQRSTU
1Issue/Amendment DateExpiry DateRefOutletCustomer NoCommissionContact PeriodDec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19
219-Jun-181-Jan-1918000003Head Office915800.008 100100100100100100100100
Sheet15
Cell Formulas
RangeFormula
G2=DATEDIF(A2-DAY(A2)+1,B2,"m")+1
H2=IF(AND(H$1>=EOMONTH($A2,0),H$1<=EOMONTH($B2,0)),$F2/$G2,"")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top