I would like to know what to change in my formula that would be able to handle splitting out the days in a date range to give the actual cost for that month, i.e., formula works when the date starts on the first of the month, however, if the service starts on the 15th of the month my formula will not calculate the 15 days expense for that month. My formula right now is straight-lining the expense over a certain amount of months but I need it to calculate based off the date range. I've attached the workbook. The items marked in yellow show my problem.
Not the most elegant solution but I believe formulae in the attached file work as intended.
I've used a few helper rows (hidden with a group button) that made it easier to capture and calculate the impact of starting dates other than the 1st of a month.
Thanks Riny, I did think helper rows would be needed to try and get it to work and a median formula but when I tested it on my own before asking for help, I wasn't getting it right so thank you for helping me with nailing down the formula it as it seems to be doing what I need it to do now.
