Matching an invoice to a billing period using dates

Hi all

Here goes.. I have posted many times on here before but I think this is my biggest problem to date. I am hoping there is a sympathetic expert out there as what I am trying to achieve may be tricky to complete – if at all possible!

I am trying to build a model which builds an accrual based on utility charges received to date. The model has two tabs, one of the tabs (“Summary”) will list 20 odd branches along with a monthly charge and accrual calculation. The “Invoices” tab will be a direct dump from a database which will list invoices (actual Charges recieved to date).

The tricky part… What I need the model to do is match billing periods to calendar months on the "Summary Sheet" and then calculate any possible accrual if we have not been billed for the whole period.
e.g. Our water supplier bills us randomly throughout the year for random periods e.g. we could receive a bill in February for £2100 for the period 15/1/13 to 5/2/13. I would need (on my summary tab) for a formula to calculate the number of days billed for and the charge and assign it by month e.g. Billed £1600 for Jan and £500 for February – is this possible?

The accrual part is the easy bit I really need help with matching the billed period number of days and charges (£) to a calendar month.

One thing to note is that the formula will need some sort of lookup as although the “invoices” tab does have some structure currently in my example it will have a whole list of invoices for various branches all for different periods being posted at different times. One thing that is unique is the branch number e.g. 111 or 222.

I have uploaded an example file. In the cells I have highlighted I have manually worked out the number of days and cost – here is where I need the formulas.

Can any one help in any way – I am in need of some real direction with this.

Hi,

I have found a formula on the Chandoo site which possibly could be adapted - although it might just be wishful thinking in desperation!

On the attached sheet I have added the tab Range Lookup formula. Could this be adapted in any way to get my desired results? e.g. in the example I have the date range 18-Jan-13 to 11-Feb-13 and the formula has identified what period this relates to and how many days need to be assigned to each period - I did this manually, is there a way formula could be used?

Hi

Any takers? This is really bugging me tonight - I have searched online and couldn't find a solution.

On the attached, in the tab "range lookup formula" the formula:

Please Login or Register  to view this content.

Currently looks with a range and returns the ROW. Now I am thinking is there any way I can flip this range and have the formula return the column?

I am aware I have two posts running currently however they are for different problems (despite the same model being used).

I was helped with a formula which would match the dates and it would assign billed days to the correct month. I have applied this formula to the "invoices" tab.

I now simply (I Say lol) need to pull the data from the "Invoices" sheet to the "Summary" sheet. Can I use a formula which would match the summary sheet column A with that of the invoices tab column D and then sum the column appropriate to the month? What would be best for this - SUMIF or SUMPRODUCT?

Lol - I thought it would be simple but after posting and trying for myself it obviously wasnt - the concept was simple but that was about it!