AR Aging based on a specific date

[philipsfn]

How can i build an AR Aging in excel using if statements using a locked down date of say 3/31/2016 and then to put the aged amounts in buckets of 0-30, 31-60, 61-90 etc. Thanks

 

[Brian Smith]

How can i build an AR Aging in excel using if statements using a locked down date of say 3/31/2016 and then to put the aged amounts in buckets of 0-30, 31-60, 61-90 etc. Thanks

Ken posted about this here if power query floats your boat.

If power query does not float your boat I attached a version using just normal excel formulas and a pivot table to summarize.

 

[philipsfn]

Thanks Brian.

My report does not have an invoice due date field, only invoice date. How can i modify the aging you've shown and how do i get the amounts to appear in the aging buckets across columns.

 

[Pecoflyer]

Hi
by using abbreviations like "AR", which not every member is supposed to understand, you are limiting the help you can get on a forum. Remember not everyone has the same skills as you do.

 

[Brian Smith]

Thanks Brian.

My report does not have an invoice due date field, only invoice date [I think it would still work the same way right? Just use invoice whatever invoice date you are using as a column and compare it to a date you enter in a cell - in the case of my example B1] . How can i modify the aging you've shown [by changing the date in b1] and how do i get the amounts to appear in the aging buckets across columns [either by putting the schedule into a pivot table as in rows 14 - 21 or using formulas like in columns G - J].

Hope that's helpful.