Hi Guys,
I am hoping somebody can help with a formula to re-allocate contract costs over a given period. This will need to take the contract costs when they straddle two or more periods.
The data I have for each contract is set out like this:
And these are the expected results with my workings (I have colour coded relevant costs from the periods):
Hoping somebody can give me a steer on this.
Thanks in advance.
I am hoping somebody can help with a formula to re-allocate contract costs over a given period. This will need to take the contract costs when they straddle two or more periods.
The data I have for each contract is set out like this:
Start | End | Price |
01/03/2016 | 28/02/2017 | 40,316 |
01/03/2017 | 28/02/2018 | 42,395 |
01/03/2018 | 28/02/2019 | 43,988 |
01/03/2019 | 29/02/2020 | 31,223 |
01/03/2020 | 29/06/2020 | 13,199 |
30/06/2020 | 29/06/2021 | 39,809 |
30/06/2021 | 29/06/2022 | 49,371 |
And these are the expected results with my workings (I have colour coded relevant costs from the periods):
Results | Workings | ||||||||||
Start | End | Total Costs | Days | Costs | Days | Costs | Total Days | Total Costs | |||
06/04/2016 | 05/04/2017 | 40,521.09 | 329 | 36,339.63 | 36 | 4,181.47 | 365 | 40,521.09 | |||
06/04/2017 | 05/04/2018 | 42,552.51 | 329 | 38,213.96 | 36 | 4,338.54 | 365 | 42,552.51 | |||
06/04/2018 | 05/04/2019 | 42,720.57 | 329 | 39,649.46 | 36 | 3,071.11 | 365 | 42,720.57 | |||
06/04/2019 | 05/04/2020 | 32,078.79 | 330 | 28,151.89 | 36 | 3,926.91 | 366 | 32,078.79 | |||
06/04/2020 | 05/04/2021 | 39,810.29 | 85 | 9,271.87 | 280 | 30,538.42 | 365 | 39,810.29 |
Hoping somebody can give me a steer on this.
Thanks in advance.