Create month columns and calculate values for each month based on total value

[KittieJames]

Hello,

I have the below data and am trying to create columns based on the 'billing from' month and the 'billing to' month.

'TOTAL UNIT SELLING COST' is what needs to be recognised in each FULL month and the difference between SELL price goes to the 'pro-rata' month.

For example below we have billed 110.56 relating to 10/12/2024 - 28/02/2025. 40.80 needs to be recognised in each full month, i.e Jan and Feb and the difference of 28.96 needs to show in the pro-rata month, in this case December. The difference being  the sell price of 110.56 -(2 * 40.80).

On the second row we have billed 71.49 and 64.98 is what needs to be recognised in a FULL month, in this case Feb. The remaining £6.51 needs to be split between Jan and March based on the amount of days being billed.

So ideally I am trying to get what is shown in yellow.

[OTrieger]

@KittieJames 
As you will need to day count, for the Billing FROM find the last day of the month and then get the number of days. If equal to number of days in a month, then you have a full month if not, then you will need the # of days for the calculation later.

Very similar do with Billing TO only in this case find the First day of the month and get the total days, if equal to day in month then you have a full month if not then you have the number of days. From these 2 dates you could figure if there is a full month in between. There are  several ways how to do it, by creating a full calendar or by getting the month name, etc.
For any full month just assign the monthly full amounts. You can Transpose the data and Sum up the value for all full months. Append that value and with formula tool deduct it from the total. For the first month and last month assign the following value (# of Count days/total days of partial months), Multiple the value that you got after the deduction with the value that you have in the partial months. Now you have the amount that need to be paid in each month.

[OTrieger]

1. Count the days in first month

2. Count the days in last month

2A. Get the total days of the partial months

3. get the full months

4. For full months assign the full rate

5. Transpose and sum the total

6. Append that total and get what left to be paid

7. Divide the number of days per month by total days, now you have a ratio

8. Multiple that ratio by what left to be paid

[davidskaife · Accepted solution]

Hi @KittieJames 

Cross posting here, try the attached (it even accounts for Leap Years!)

[KittieJames]

Thank you this worked perfectly