Good morning all!
I need your help/ assistance. I am working on automated prepaid expenses allocation among of all month the service run through.
To be specific, it is related to software products we manually expense at the end of the month.
Ideally services run from the beginning of the month to the end of the moth, but there are cases when service starts and ends in the middle of the month or just random day in the month, depending when it was aquired.
So far I have the following:
The formulas used are as follow:
D5 =E5/(C5-B5) This calculates charge per day by taking total cost and dividing it by total days the prepaid runs through
F5:Y5 =MAX(MIN($C$5,G3)-MAX($B$5,G2)+1,0)*$D5 This calculates how many days the prepaid runs through given month and multiplies it by the amount in the D5 which is per day charge
THE CHALLANGE:
I like to have in the formula a date paid in the consideration. in the example above the date paid falls in October, therefore my prorating should start there and the amount in the October should be total of the all periods from before October and October including ($40,837.81), and after October run as it is.
Is there any kind soul to help me tweak the formula a little to solve the challenge?
Thank you,
I need your help/ assistance. I am working on automated prepaid expenses allocation among of all month the service run through.
To be specific, it is related to software products we manually expense at the end of the month.
Ideally services run from the beginning of the month to the end of the moth, but there are cases when service starts and ends in the middle of the month or just random day in the month, depending when it was aquired.
So far I have the following:
| A1 | D1 | ||||||||||||||||||||||
1/1/2022 | 2/1/2022 | 3/1/2022 | 4/1/2022 | 5/1/2022 | 6/1/2022 | 7/1/2022 | 8/1/2022 | 9/1/2022 | 10/1/2022 | 11/1/2022 | 12/1/2022 | 1/1/2023 | 2/1/2023 | 3/1/2023 | 4/1/2023 | 5/1/2023 | 6/1/2023 | 7/1/2023 | |||||
1/31/2022 | 2/28/2022 | 3/31/2022 | 4/30/2022 | 5/31/2022 | 6/30/2022 | 7/31/2022 | 8/31/2022 | 9/30/2022 | 10/31/2022 | 11/30/2022 | 12/31/2022 | 1/31/2023 | 2/28/2023 | 3/31/2023 | 4/30/2023 | 5/31/2023 | 6/30/2023 | 7/31/2023 | |||||
| Date Paid | START | END | Per Day Charge | Cost | January 2022 | February 2022 | March 2022 | April 2022 | May 2022 | June 2022 | July 2022 | August 2022 | September 2022 | October 2022 | November 2022 | December 2022 | January 2023 | February 2023 | March 2023 | April 2023 | May 2023 | June 2023 | July 2023 |
10/17/2022 | 02/14/22 | 02/14/23 | 157.07 | 57,330.00 | - | 2,356.03 | 4,869.12 | 4,712.05 | 4,869.12 | 4,712.05 | 4,869.12 | 4,869.12 | 4,712.05 | 4,869.12 | 4,712.05 | 4,869.12 | 4,869.12 | 2,198.96 | - | - | - | - | - |
The formulas used are as follow:
D5 =E5/(C5-B5) This calculates charge per day by taking total cost and dividing it by total days the prepaid runs through
F5:Y5 =MAX(MIN($C$5,G3)-MAX($B$5,G2)+1,0)*$D5 This calculates how many days the prepaid runs through given month and multiplies it by the amount in the D5 which is per day charge
THE CHALLANGE:
I like to have in the formula a date paid in the consideration. in the example above the date paid falls in October, therefore my prorating should start there and the amount in the October should be total of the all periods from before October and October including ($40,837.81), and after October run as it is.
Is there any kind soul to help me tweak the formula a little to solve the challenge?
Thank you,
