Distribute Amount Based on month days between two dates

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,614
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I have below data now I want to distribute amount in column F based on start and end date to particular month based on their days

So for example
in first two dates 27 days of July, 31 days of August and 16 days of September and allocate amount based on number of days of a particular month

Links Tuition 22-23.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
120222023
2District/ParentStudentDurationAmountMayJuneJulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctober
3Expected932.431070.57552.55
4
5Murray, LisaMurray, JakeStartEnd187.50
6StamfordCharly, Peter7/5/20229/17/2022742555.55
7Darien P.S.Johns, Judah6/29/20229/23/20228610591.27
8Norwalk P.S.Botens Daniel12/1/20223/10/20239922700.00
9Leonard SantorelliBates, Sienna11/21/20221/13/20235314750.00
10Darien P.S.Herne, Anabel "Tobi"12/5/20221/20/20234615250.00
11Merrill, JohnMerrill, Poppy12/5/20221/6/2023328000.00
12Stamford P.S.Academic Support for ESS/BGC12/5/20221/20/20234626735.00
13Norwalk P.S.Sullivan Jennifer12/5/20221/20/2023465900.00
14Darien P.S.Steinkraus Griffin1/3/20234/7/20239428375.00
15Stamford P.S.Reicin, Tina12/16/202212/22/20233711260.00
16Greenwich P.S.Ginste, Elsa1/3/20233/31/20238722700.00
17VanWyk, MarkVanWyk, Beckett1/3/20234/7/20239428375.00
18Lamendola, KristinFallon, Kyle12/27/202212/30/20223910.00
19Kornstein, AndrewKornstein, Lleyton1/1/20233/4/20236214750.00
20Blanchard, RobertBlanchard, Henry1/9/20231/24/2023152800.00
21Greenwich P.S.Casey, Thalia1/9/20232/24/20234611800.00
22Edwards, JohnEdwards, Hayley1/6/20232/24/20234911800.00
23Yahong, ChenLi, Leon2/1/20234/21/20237923050.00
24Prescott, FinnianPrescott, Claire1/23/20234/21/2023885675.00
25Sherman, ScottSherman, Matt1/23/20234/28/20239528875.00
26Brockman, RhondaBrockman, Jack1/23/20235/5/202310223200.00
27Darien P.S.Herne, Anabel "Tobi"1/23/20233/10/2023465900.00
28Mottola, ThomasMottola, Sakae1/26/20233/24/20235715250.00
29Proctor, DianeProctor, Kelly1/31/20236/9/202312937300.00
30Darien P.S.Hagander, Ashley1/20/20235/12/202311211350.00
31Stamford P.S.Academic Support for ESS/BGC1/23/20233/17/20235326735.00
32Lamendola, KristinFallon, Kyle1/30/20234/28/20238817525.00
33Norwalk P.S.Sullivan, Jennifer1/23/20233/17/2023535900.00
34Merrill, JohnMerrill, Poppy1/23/20235/12/202310914980.00
35Stamford P.S.Vita, Sophia2/3/20234/14/2023702025.00
36Kliatchko, VladimirKilatchko, Robin1/27/20236/9/202313311525.00
37PrivateLovett, Chloe2/6/20235/5/20238828375.00
38Bradley, AshleyBradley, Oliver2/13/20233/31/20234612300.00
39Blanchard, RobertBlanchard, Henry1/30/20233/4/2023336720.00
40Nemec, SuzanneNemec Phillip2/22/20233/15/2023212450.00
41Kornstein, AndrewKornstein, Lleyton3/6/20235/12/20236719200.00
42Greenwich P.S.Casey, Thalia2/27/20236/16/202310929500.00
43Town of DarienVasquez, Enrique3/2/20235/15/20237436775.00
44Nudelman, NikkiNudelman, Sammy3/2/20235/12/20237115860.00
45Norwalk P.S.Botens Daniel3/13/20236/16/20239511350.00
46Norwalk P.S.Sullivan, Jenniver3/20/20235/3/2023445900.00
47PrivateSciglimpaglia, Eden3/13/20236/16/20239515000.00
48Sherman, ScottSherman, Matt3/6/20237/14/202313011525.00
49Town of DarienTucker, Thier3/20/20236/9/20238111850.00
50Stamford P.S.Academic Support for ESS/BGC3/20/20235/5/20234626735.00
51PrivateNusseibeh, Anisa3/27/20235/19/20235315360.00
52Lamendola, KristinFallon, Kyle3/27/20236/9/2023745675.00
53Stamford P.S.Condlin, Emma3/29/20236/16/20237911850.00
54Mottola, ThomasMottola, Sakae3/27/20235/12/20234614750.00
55Greenwich P.S.Ginste, Elsa4/3/20236/16/20237414300.00
56Stamford P.S.Sikiotis, Tristen4/11/20236/23/20237323200.00
57PrivateHowe, Alexandar4/5/20235/12/20233715250.00
58PrivateNusseibeh, Sarimir3/30/20235/19/20235015360.00
59Darien P.S.Gracia, Jeraldine4/17/20236/30/20237429100.00
60Goodwin, SethGoodwin, Sage4/19/20236/9/2023513840.00
61Stamford P.S.Talbot, Jason5/15/20236/30/2023469350.00
62Josh, GershonGershon, Josh4/27/20236/9/2023432950.00
63Darien P.S.Piersol, Gavin5/4/20236/30/20235720870.00
64Darien P.S.Hagen, Duke4/26/20236/30/20236523425.00
65Stamford P.S.Academic Support for ESS/BGC5/8/20236/15/20233826735.00
66Greenwich P.S.Blackwell, Ru5/9/20236/16/20233815250.00
67Stamford P.S.Franco, Zaire5/15/20236/23/20233915750.00
68Darien P.S.Johnson, William4/27/20235/10/2023132460.00
69Northfield Mount HermanThomas, Gabby5/9/20236/23/2023455900.00
Sheet1
Cell Formulas
RangeFormula
E6:E69E6=DAYS(D6,C6)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
MrExcelPlayground17.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
120222023
2District/ParentStudentDurationAmountMay 2022Jun 2022Jul 2022Aug 2022Sep 2022Oct 2022Nov 2022Dec 2022Jan 2023Feb 2023Mar 2023Apr 2023May 2023Jun 2023Jul 2023Aug 2023Sep 2023Oct 2023
3Expected932.431070.57552.55
4
5Murray, LisaMurray, JakeStartEnd187.5
6StamfordCharly, Peter7/5/20229/17/2022742555.55  $920.00$1,056.29$579.26             
7Darien P.S.Johns, Judah6/29/20229/23/20228610591.27 $243.48$3,773.90$3,773.90$2,799.99             
8Norwalk P.S.Botens Daniel12/1/20223/10/20239922700       $7,037.00$7,037.00$6,356.00$2,270.00       
9Leonard SantorelliBates, Sienna11/21/20221/13/20235314750      $2,731.48$8,467.59$3,550.93         
10Darien P.S.Herne, Anabel "Tobi"12/5/20221/20/20234615250       $8,760.64$6,489.36         
11Merrill, JohnMerrill, Poppy12/5/20221/6/2023328000       $6,545.45$1,454.55         
12Stamford P.S.Academic Support for ESS/BGC12/5/20221/20/20234626735       $15,358.40$11,376.60         
13Norwalk P.S.Sullivan Jennifer12/5/20221/20/2023465900       $3,389.36$2,510.64         
14Darien P.S.Steinkraus Griffin1/3/20234/7/20239428375        $8,661.84$8,363.16$9,259.21$2,090.79      
15Stamford P.S.Reicin, Tina12/16/202212/22/20233711260       $54.19$105.00$94.84$105.00$101.61$105.00$101.61$105.00$105.00$101.61$105.00
16Greenwich P.S.Ginste, Elsa1/3/20233/31/20238722700        $7,480.68$7,222.73$7,996.59       
17VanWyk, MarkVanWyk, Beckett1/3/20234/7/20239428375        $8,661.84$8,363.16$9,259.21$2,090.79      
18Lamendola, KristinFallon, Kyle12/27/202212/30/20223910       $910.00          
19Kornstein, AndrewKornstein, Lleyton1/1/20233/4/20236214750        $7,257.94$6,555.56$936.51       
20Blanchard, RobertBlanchard, Henry1/9/20231/24/2023152800        $2,800.00         
21Greenwich P.S.Casey, Thalia1/9/20232/24/20234611800        $5,774.47$6,025.53        
22Edwards, JohnEdwards, Hayley1/6/20232/24/20234911800        $6,136.00$5,664.00        
23Yahong, ChenLi, Leon2/1/20234/21/20237923050         $8,067.50$8,931.88$6,050.63      
24Prescott, FinnianPrescott, Claire1/23/20234/21/2023885675        $573.88$1,785.39$1,976.69$1,339.04      
25Sherman, ScottSherman, Matt1/23/20234/28/20239528875        $2,707.03$8,421.88$9,324.22$8,421.88      
26Brockman, RhondaBrockman, Jack1/23/20235/5/202310223200        $2,027.18$6,306.80$6,982.52$6,757.28$1,126.21     
Sheet9
Cell Formulas
RangeFormula
I2:Y2I2=EDATE(H2,1)
H6:Y26H6=LET(d,SEQUENCE($D6-$C6+1,1,$C6),r,ROWS(d),a,$F6,p,a/r,m,SEQUENCE(r,1,p,0),f,FILTER(m,EOMONTH(d,-1)+1=H$2),IFERROR(SUM(f),""))
E6:E26E6=DAYS(D6,C6)
 
Upvote 0
Solution
Thank you very much James.
This is perfect.

Do you have any tip for me to practice on a formula like this?
 
Upvote 0
When using a LET formula, the last term is what is going to appear. I'd change the last term to see what each element is doing.

The best way to learn, is to come here and solve peoples problems - but be slow to show your solution. See how other people solve them.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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