Re-calulating totals based on "days old"

[Vikki]

HI there

I currently have a financial sheet where we track the age of invoices - I
currently use DAYS360 - and then total the amounts for invoices 0-30 days,
31-60 days, 61-90 days etc. At the moment I have to cut and paste each entry
once it passes into the next group so that the totals are accurate, but is
there any way I can get it to recalculate automatically?

Hope I have explained sufficiently!

 

[Stefi]

If you want to keep totals automatically updated then use these formulae
(dates being in A2:A5, amounts in B2:B5, adjust ranges!!!):
for 0-30 days:
=SUMPRODUCT(B2:B5,--(DAYS360(A2:A5,TODAY())<=30))
for 31-60 days
=SUMPRODUCT(B2:B5,--(DAYS360(A2:A5,TODAY())>30),--(DAYS360(A2:A5,TODAY())<=60))
for 61-90 days :
=SUMPRODUCT(B2:B5,--(DAYS360(A2:A5,TODAY())>60))

You can use Conditional formatting to show the age groups of invoices in
different colors using criteria
DAYS360(A2:A5,TODAY())<=30
etc.

If you want to move invoices to another location when entering in another
age group, I'm afraid you'll need a macro to do that!

Regards,
Stefi

„Vikki†ezt írta: