How to spread costs evenly across months, if they fall within those dates

AshwiniBiotech2023

This certainly gets me way closer -- it works great! The only thing interestingly is if the dates are say 1/1/23 to 12/31/23 (basically the entire year), it is not counting 1/1 and 12/31 itself, so it is calculating for 363 days instead of 365 days. So for that, I need to list 12/31/22 to 1/1/24 instead, if I want to include all 365 days in 2023. Other than that, it works perfectly!

Paul Newcome

Does it do the same thing if you reference the start and end of different months such as 1 Feb 23 through 30 June 23?

AshwiniBiotech2023

It appears to.... see below for what I mean. I put 1/1/23 to 12/31/23. It is 12 months and 365 days, but still the total per month is different for Jan and Dec - hence the total doesnt match up with the $65k...

I can still use your suggested formula though - it is better than what I was getting before!!! THank you SO much for your help!!!

Paul Newcome

I understand what is happening and get that it is on the full year, but I am trying to find out if it is also happening for you when you have a shorter span but one that still starts at the beginning of the month and ends at the end of the month.

If it is doing that every time, I have an idea of how to fix it.

AshwiniBiotech2023

Hi Paul,

Yes it's happening every time, for every month, regardless of the time span....

Paul Newcome

Ok. See fi this corrects it:

=IF(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) = 202304, ((DATE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, 1, 0), MONTH([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, -11, 1), 1)) - [Start Date]@row) * [$ per day]@row, IF(VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) = 202304, (DAY([End Date]@row) + 1) * [$ per day]@row, IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) <= 202304, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) >= 202304), [$ per Month]@row)))

AshwiniBiotech2023

Hi Paul,

Thank you so much for trying.... it is still not right. The initial and end months are higher, and hence the total doesnt add up to the actual annual total. Please see below. Now the initial and end months are even higher than before, I think. The same thing applies to other months, such as mid-July to mid-July - the start and end months are off, such that the total is higher than the actual total for that time frame....

Paul Newcome

Looks like I went the wrong direction then. How's this one?

=IF(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) = 202304, ((DATE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, 1, 0), MONTH([Start Date]@row) + IF(MONTH([Start Date]@row) = 12, -11, 1), 1) - 2) - [Start Date]@row) * [$ per day]@row, IF(VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) = 202304, (DAY([End Date]@row) - 1) * [$ per day]@row, IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH([Start Date]@row) < 10, "0", "") + MONTH([Start Date]@row)) <= 202304, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row) < 10, "0", "") + MONTH([End Date]@row)) >= 202304), [$ per Month]@row)))

AshwiniBiotech2023

Now it's going under the required total..... Basically the start and end month are not working right, for any time frame. If its the entire year, the start and end months are different than the other months (and they shouldn't be), and if its a mid-month to another mid-month, again the start and end months arent right... such that the total line and the monthly additions dont line up. Paul --- given how much you've tried to help me, would it be simpler if I just scrubbed any confidential data off my sheet and shared it directly with you to see what is wrong?

Paul Newcome

I'm honestly not sure exactly what is going on. Here is what we have tried (as a basic principal).

Start Date and End Date = Short by a day on each side.

Start Date - 1 and End Date + 1 = Over on each side.

Start Date + 1 and End Date - 1 = Short on each side.

So at this point I am not exactly sure how to fix it. I may just be staring at it too much and missing the obvious, but I can't see anything outside of adding/subtracting a single day somewhere for the start and end months which just doesn't seem to be working.

AshwiniBiotech2023

Hi Paul,

I tried something different and a lot simpler and this may be working now. This is for Mar 2023, for example. I don't use the cost per day or month now. Just calculate the cost over that time frame using the total cost and the number of days...

=(Cost@row / [Duration (days)]@row) * ((MAX(DATE(2023, 3, 31) - [Start Date]@row, 0) - MAX(DATE(2023, 2, 28) - [Start Date]@row, 0)) - (MAX(DATE(2023, 3, 31) - [End Date]@row, 0) - MAX(DATE(2023, 2, 28) - [End Date]@row, 0)) + IF(MONTH(DATE(2023, 3, 31)) = MONTH([Start Date]@row), 1))

AshwiniBiotech2023

It works, but there are still places it doesn't work. For example, for a line item that goes from 1/1/23 to 12/31/23, I used this formula below for the Jan 2024 cell. It should be $0 since the cost only spans 2023, but it is still returning a value.... any idea why???

=(Cost@row / [Duration (days)]@row) * ((MAX(DATE(2024, 1, 31) - [Start Date]@row, 0) - MAX(DATE(2023, 12, 31) - [Start Date]@row, 0)) - (MAX(DATE(2024, 1, 31) - [End Date]@row, 0) - MAX(DATE(2023, 12, 31) - [End Date]@row, 0)) + IF(MONTH(DATE(2024, 1, 31)) = MONTH([Start Date]@row), 1))

AshwiniBiotech2023

I think I need to add an IF clause for the Year as well, after the last one for the month. I've tried a few different versions and it gives me an error. Can you provide advice on how to add an IF clause for the year, and then it returns a value of '1'?

AshwiniBiotech2023

This worked:

=(Cost@row / [Duration (days)]@row) * ((MAX(DATE(2023, 1, 31) - [Start Date]@row, 0) - MAX(DATE(2022, 12, 31) - [Start Date]@row, 0)) - (MAX(DATE(2023, 1, 31) - [End Date]@row, 0) - MAX(DATE(2022, 12, 31) - [End Date]@row, 0)) + IF(AND(MONTH(DATE(2023, 1, 31)) = MONTH([Start Date]@row), YEAR(DATE(2023, 1, 31)) = YEAR([Start Date]@row)), 1))

And that is for January - in case its helpful to you or anyone! THANK YOU so much for your help...........