Hi,
I'm trying to come up with a formula that will calculate the expense for the period, based on a start and end date for a service, a rate per day, and the various month end dates.
In this case, the rate per day would be £55.56 which would need to be expensed over the 02/05/2022 - 30/07/2022 with the first month end date being 23/04/2022, the second month end date being 28/05/2022 etc.
I have manually keyed in what the values should be each month, i'm just looking for a formula i can use to more efficiently populate the table?
The days at the bottom of the table are just for reference.
Thanks
I'm trying to come up with a formula that will calculate the expense for the period, based on a start and end date for a service, a rate per day, and the various month end dates.
In this case, the rate per day would be £55.56 which would need to be expensed over the 02/05/2022 - 30/07/2022 with the first month end date being 23/04/2022, the second month end date being 28/05/2022 etc.
I have manually keyed in what the values should be each month, i'm just looking for a formula i can use to more efficiently populate the table?
The days at the bottom of the table are just for reference.
Amount | Start Date | End Date | Days | Rate per Day £ | 23/04/2022 | 28/05/2022 | 25/06/2022 | 23/07/2022 | 27/08/2022 | 24/09/2022 | 22/10/2022 | Total |
5,000 | 02/05/2022 | 30/07/2022 | 90 | 55.56 | 0 | 1,500 | 1,556 | 1,556 | 389 | 5,000 | ||
Days | 27 | 28 | 28 | 7 | 90 |
Thanks