Calculating old debt?

[lky2k23]

Hi first post so be kind....


Have a statement i am working on and i want to be able to calculate the debt over 120 days. I want this to be able to update daily also. the setup at present is;

Date -Number -Due Date- Debit -Credit

So I want the 'date' to only calculate from today -120 and show the balance after credits and debits are calculated?

Thanks

Liam

[lky2k23]

is there anything i need to add to this to help?

[darkyam]

Assuming credits increase the amount owed, try something like:

=SUMPRODUCT(--(A1:A10<=TODAY()-120),E1:E10)-SUMPRODUCT(--(A1:A10<=TODAY()-120),D1:D10)

This assumes you're calculating based off the date of the debt rather than the date the debt was due. If it is the latter, change A1:A10 to C1:C10. It also counts items 120 days or older. If you want just older, replace <= with <.