Split periods between start and end dates and adjust a cost accordingly.

Hello,

I do not believe there is a solution for this so if anyone is a genius then let me know.

With the attached spread sheet, I am break down the start and end dates in order to get a budget cost per quarter for 2014. The quarter dates would be

01/01/14 - 31/03/2014
01/04/14 - 30/06/2014
01/07/14 - 30/09/2014
01/10/14 - 31/12/2014

The cost to split would only be the Total cost in Euros column.

Does anybody know if this is possible? Pleeeeeeeeeease help!

Many thanks in advance.

How about the attached file?

I've added a couple of rows at the top to specify the qtr start and end dates. The formulas then calculate the number of days in the qtr, divide that by the number of days between the start and end dates of the contract and multiply by the Euro value.

Got to be worth a click on the star below.

Chris

Chris,

You sir, are a genius. I cannot thank you enough. Is there a rating system on here where I can give you top marks?

Just one slight niggle though: If a contract does not start until, say, 31/07/2014, it places negative values in the quarters prior to this. See line 46 of your spread sheet fro an example. I think I may just have to delete these out, unless you know of a better way?

Either way, thank you very very much. I only wish I'd have found you a week ago so I could have saved a good number of headaches!

Thank you.

Hi Paul

Sorry about that, add *($M4<O$2) to the end of the formula in O4 and copy it down and across to the other formulas.

I didn't like row 37 till I noticed that the error was in H37.


The rating is by clicking on the * to add reputation at the bottom of each post.


Happy to help

Chris