Formula to calculate money due

**1.zer0** · 01-19-2011, 11:52 AM

I have a spreadsheet with a column that I want to show how much money is still due from todays date to the completion of the job.

Each job has it's own sheet with the future weeks payments and completion date.

So if job 1 started on 03/01/2011 and finished on 28/01/2011 I would need the amount from today (19/01/2011) to the end. Maybe it would be done as the total minus any money received from the start (03/01/2011 - today)? Or just add the amounts from today to the end?

I've attached the workbook to help make sense.

Thanks

**scottylad2** · 01-19-2011, 01:07 PM

Maybe I'm not understanding, what you appear to be showing in your example is for instance GW Turner £6000 on 21/01/2011 is this part of the payment or the toal to be paid for that job? will you be paid that amount each payment? Or is it the case the £6000 is to be divided by the amount of weeks specified, in this case 9 weeks? If thats the case are you going to enter each week you recieve money or are the payments received daily and the 6000 should be split between the days between the start date and end date?

**1.zer0** · 01-20-2011, 11:05 AM

Hi scottylad,

Sorry I should have filled in a bit more data to help. I've uploaded an updated version to help clarify.

In addition to calculating money still due on a job I would also like the column titled completion date to be the last date on the specific job sheet with a value. So with the GW Turner job the date column goes to 25/3/11 but the last payment is 4/3/11 so that would be the completion date.

The reason I have done it like this is so once a job has finished the sheet can be renamed and updated with a new jobs info.

Hope that helps

Thanks

**scottylad2** · 01-20-2011, 04:27 PM

in F2 on Totals sheet

=SUMPRODUCT(--('GW Turner'!A2:A11<=TODAY()),'GW Turner'!B2:B11)
as you arrive at each payment date this will update with the amount due with that weeks money. I'm assuming you will always be paid on the date due

To get the date in G12 this will do

LOOKUP(9.9999999999E+307,'GW Turner'!B2:B11,'GW Turner'!A2:A11) If theres no dates in the column of payment due dates you will get an error, counter that by wrapping in an iferror or similar

In H2

G2-TODAY() will give you a countdown of the number of days till completion. You can do the same for each project. I'm sure you can automate some of this by using the indirect function to reference the sheet names, but i'll leave that for someone else to input

Been a long day