Share via

how to calculate accounts payable aging current and past due days

Anonymous
2012-08-17T23:46:58+00:00

Please see below, I have type the data but I would like to place the formula which can automatically calculate total number of day as per column title and place the invoice amount in appropriate column, can some help me with the formula please, thanks

Invoice Date Invoice Description Invoice Amount Terms Payment Due Date Current 1-30 Days 30-60 Days 60-90 Days Over 90 Days
5/15/2012 200 200
8/15/2012 200 200
7/15/2012 200 200
6/15/2012 200 200
5/15/2012 200 200
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-08-18T01:37:44+00:00

With your sample data in A1:J6

This regular formula begins the aging of 1-30 days

G2: =IF(MATCH(TODAY()-$A2,{0,30,60,90},1)=COLUMNS($G:G),$C2,"")

Copy that formula across and down through J6 for the rest of the aging amounts.

Is that something you can work with?

Was this answer helpful?

0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-08-23T12:42:20+00:00

    OK...but where ARE the payments entered? How do you want the aging impacted?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-08-23T11:46:25+00:00

    Hi,

    Thanks, the formula works but I need to take one more step in this, when the payment is made partial or in full that the aging amount should adjust to show correct amount as an open balance. Please help, because in this current formula the payment is not being adjusted, Thanks, nk

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-08-18T23:20:03+00:00

    Using your posted example in A1:J6

    This regular formula adjusts the aging for any rows that are "Net 90 Days" in Col_D

    G2: =IF(MATCH(MAX(TODAY()-$A2-($D2="Net 90 Days")*90,0),{0,30,60,90},1)=COLUMNS($G:G),$C2,"")

    Copy that cell and paste it down and across through J6

    Does that help?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-08-18T16:35:45+00:00

    Hi, Ron:

    Thank you for the reply, the formula does work, but I forgot the put one more major point.  there is column for Terms  and if the term column reads "Net 90 Days" than the bill which will be still current and will not show in past due column.

    at the end of the column "over 90 Days" there should be one more column which will show the comment for that raw record to shoe the invoice as "Current" or "Past Due"

    Invoice Date Invoice Description Invoice Amount Terms Payment Due Date Current 1-30 Days 30-60 Days 60-90 Days Over 90 Days
    5/15/2012 200 200
    8/15/2012 200 200
    7/15/2012 200 200
    6/15/2012 200 200
    5/15/2012 200 200

    actually I really got stuck over there, if you can help on this issue or any one else can help me in this issue, thanks, please I need the formula, I know it is some kind of conditional or logical formula but I am not getting it correct., please help, thanks

    Was this answer helpful?

    0 comments