Question

I need help writing a VBA code for aging accounting receivables for 30, 60, 90 days in Excel.

Answer 1

You can use the following code

=LOOKUP(TODAY()-F2,{0,31,61,91},{"0-30 Days","31-60 Days","61-90 Days","Over 90 Days"})

Step 1:

Label the following cells:
A1: Customer
B1: Order #
C1: Date
D1: Amount Due.
Enter in the corresponding information for your customers and their orders underneath the headlines.

A D G H Order # Date 1 Customer Amount Due 1/1/2017 $1,005.23 2 Myers Supply Takei Corporation 4 Vandelay Enterprises 110023 110056 12/25/2016| $5,869.23 8/5/2017 $657.51 110089 3/6/2017 $968.78 5 Home Renovation LLC 110074 110096 5/25/2017 $1,658.98 6 Records Industries 110078 6/30/2017] $3,669.45 7 Rubber Made Goods 8 Produce Suppliers 110012 7/28/2017 $10,052.36 110011 2/15/2017 $8,759.36 9 ABC LLC 110028 8/30/2017 $25,469.56 10 Salon and Spa Supplies 11 Pet Store Supplier 110091 7/31/2017| $3,542.98 6/5/2017 $1,654.87 12 Exacto Goods 110063 13 Firehouse Enterprises 14 Steamship Services 15 e2b teknologies 110057 4/16/2017 $569.42 3/9/2017 $987.65 110019 110073 5/25/2017 $2,456.98

Step 2:

Add additional headers for each column as:
E1: Days Outstanding
F1: Not Due
G1: 0-30 Days
H1: 31-60 days
I1: 61-90 days
J1: >90 days

A C E H 1 Customer Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days >90 Days $1,005.23 1/1/2017 2 Myers Supply 3Takei Corporation 4 Vandelay Enterprises 5 Home Renovation LLC 110023 110056 12/25/2016 $5,869.23 8/5/2017 $657.51 110089 3/6/2017 $968.78 110074 110096 5/25/2017 | $1,658.98 6 Records Industries 7 Rubber Made Goods 3Produce Suppliers 110078 6/30/2017| $3,669.45 110012 7/28/2017 $10,052.36 $8,759.36 110028 8/30/2017 $25,469.56 ABC LLC 110011 2/15/2017| 0 Salon and Spa Supplies 1 Pet Store Supplier 110091 7/31/2017 $3,542.98 6/5/2017 $1,654.87 2 Exacto Goods 110063 3 Firehouse Enterprises 4 Steamship Services 15 e2b teknologies 110057 4/16/2017 $569.42 3/9/2017 $987.65 110019 110073 5/25/2017 $2,456.98 6 LL

Step 3:

Next, we will input a formula for the “Days Outstanding” column that will let us know how many days that invoice has gone unpaid since the due date.
In cell E2, enter in the following formula: =IF(TODAY()>C2,TODAY()-C2,0)

X f =IF(TODAY()>C2,TODAY()-C2,0) E2 E A P D F Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days 90 Days Customer $1,005.23 2 Myers Supply 3 Takei Corporation 1/1/2017 110023 177 110056 12/25/2016 $5,869.23 8/5/2017 $657.51 Vandelay Enterprises 110089 4 3/6/2017 $968.78 5 Home Renovation LLC 110074 110096 5/25/2017 $1,658.98 $3,669.45 6 Records Industries 7 Rubber Made Goods 110078 6/30/2017 8 Produce Suppliers 110012 7/28/2017 $10,052.36 110011 2/15/2017 10 Salon and Spa Supplies 110028 8/30/2017 110091 7/31/2017 9 ABC LLC $8,759.36 $25,469.56 $3,542.98 11 Pet Store Supplier 12 Exacto Goods 6/5/2017 $1,654.87 110063 13 Firehouse Enterprises 14 Steamship Services 110057 4/16/2017 $569.42 $987.65 3/9/2017 110019 110073 5/25/2017 $2,456.98 15 e2b teknologies 16

Step 4:

Drag the fill handler from cell E2 all the way to the last customer. This will populate the formula down the whole column so you do not have to enter it in over again.

fx IF(TODAY()>2,TODAY()- C2,0) F2 F A B C C F G H 1 Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days >90 Days Customer 2 Myers Supply 3 Takei Corporation 4 Vandelay Enterprises 5 Home Renovation LLC 6 Records Industries $1,005.23 $5,869.23 $657.51 $968.78 $1,658.98 $3,669.45 110012 7/28/2017 $10,052.36 $8,759.36 110028 8/30/2017 $25,469.56 $3,542.98 $1,654.87 $569.42 $987.65 $2,456.98 1/1/2017 110023 177 110056 12/25/2016 184 8/5/2017 110089 0 3/6/2017 110096 5/25/2017 | 110074 113 33 7 Rubber Made Goods 110078 6/30/2017 8 Produce Suppliers 0 9 ABC LLC 110011 2/15/2017 132 10 Salon and Spa Supplies 11 Pet Store Supplier 12 Exacto Goods 13 Firehouse Enterprises 14 Steamship Services 15 e2b teknologies 0 110091 7/31/2017 0 6/5/2017 110063 22 110057 4/16/2017 | 72 3/9/2017 110019 110 110073 5/25/2017 33 16

Step 5:

Now we want to give our aging report some color, so that we can easily see who is the most overdue versus who is still in the clear. Highlight all the rows in the E column then click Conditional Formatting on the Home tab and New Rule.

Cut -11 A A Wrap Text Normal Calibri General E Copy Conditional Format as Paste Check Ce Merge & Center 00 A BIU Format Painter Formatting Table Clipboard Alignment Number Font Highlight Cells Rules IF(TODAY()>C2,TODAY()-C2,0) E2 ET Top/Bottom Rules 10 D P G H 1 Customer 2 Myers Supply 3 Takei Corporation 4 Vandelay Enterprises 5 Home Renovation LLC 6 Records Industries 7 Rubber Made Goods 8 Produce Suppliers 9 ABC LLC Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days $1,005.23 $5,869.23 $657.51 $968.78 $1,658.98 $3,669.45 $10,052.36 $8,759.36 $25,469.56 $3,542.98 $1,654.87 $569.42 $987.65 $2,456.98 Data Bars 1/1/2017 177 110023 110056 12/25/2016 184 Color Scales 8/5/2017 110089 0 3/6/2017 110096 5/25/2017 110078 6/30/2017 110074 113 33 Icon Sets New Rule... 110012 7/28/2017 110011 2/15/2017 0 132 Clear Rules 10 Salon and Spa Supplies 11 Pet Store Supplier 12 Exacto Goods 13 Firehouse Enterprises 14 Steamship Services 15 e2b teknologies 110028 8/30/2017 Manage Rules... 110091 7/31/2017 0 22 6/5/2017 110063 110057 4/16/2017 72 3/9/2017 110019 110 110073 5/25/2017 33 16

Step 6:

A separate window will open named “New Formatting Rule”.
Click the “Format Style” drop down and select 3-color scale.
Click the “Type” drop down and select Number
Under “Values”, enter 0 for minimum, 60 for midpoint and 90 for maximum.
Finally, select the colors that make the most sense for you, usually three colors that are very far apart on the color scale.

Alignment Font New Formatting Rule ? X foix lF(TODAY()>C2,TODAY()-C2,0) Select a Rule Type Format all cells based on their values F D Format only cells that contain Amount Due Days Outstanding N $1,005.23 $5,869.23 $657.51 $968.78 $1,658.98 r# Date Format only top or bottom ranked values Format only values that are above or below average Format only unique or duplicate values 1/1/2017 23 177 56 12/25/2016 184 8/5/2017 89 0 Use a formula to determine which cells to format 3/6/2017 74 113 96 5/25/2017| 33 Edit the Rule Description: 78 6/30/2017] $3,669.45 0 Format all cells based on their values: 12 7/28/2017 11 2/15/2017] $10,052.36 $8,759.36 $25,469.56 $3,542.98 0 Format Style: 3-Color Scale 132 Minimum Midpoint Maximum 28 8/30/2017 0 Number Number Number Iype: 91 7/31/2017 0 Value: 0 60 90 63 6/5/2017 $1,654.87 22 Color: 57 4/16/2017 $569.42 72 $987.65 $2,456.98 19 3/9/2017 110 Preview: 73 5/25/2017 33 OK Cancel

Your aging report Excel sheet should now look like this:

A D H 1 Customer Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days >90 Days $1,005.23 $5,869.23 $657.51 $968.78 $1,658.98 $3,669.45 110012 7/28/2017 $10,052.36 $8,759.36 10 Salon and Spa Supplies 110028 8/30/2017 $25,469.56 $3,542.98 $1,654.87 $569.42 $987.65 $2,456.98 2 Myers Supply 3 Takei Corporation 4 Vandelay Enterprises 5 Home Renovation LLC 6 Records Industries 7 Rubber Made Goods 8 Produce Suppliers 9 ABC LLC 1/1/2017 110023 177 110056 12/25/2016 184 8/5/2017 110089 C 3/6/2017 113 110074 110096 5/25/2017 33 110078 6/30/2017 110011 2/15/2017| 132 11 Pet Store Supplier 12 Exacto Goods 13 Firehouse Enterprises 14 Steamship Services 110091 7/31/2017| 0 6/5/2017 110063 22 110057 4/16/2017 72 3/9/2017 110019 110 110073 5/25/2017| 33 15 e2b teknologies 16

Step 7:

In cell F2 we will find out who is not yet due on their invoices. The formula will check for anything in the “Days Outstanding” column that is equal to zero.
In cell F2, enter in the following formula: =IF(E2=0,D2,0)
Drag the fill handler down the column to populate.

Step 8:

The formula for 0-30 days basically says, “Check to see if the difference between today’s date and C2’s date are less than or equal to 30. If it is, input the data from D2. If it isn’t, leave as 0”.
Enter in cell G2 the following formula: =IF(C2<TODAY(),(IF(TODAY()-C2<=30,D2,0)),0)
Drag the fill handler down the column to populate.

Step 9:

The next formula will use an AND statement, which will basically say that if the difference between today’s date and that date in C2 is less than or equal to 60 days AND greater than 30 days, then input the data from D2. Otherwise, input 0.
In cell H2, enter in the following formula: =IF (AND(TODAY()-$C2<=60,TODAY()-$C2>30),$D2,0)
Drag the fill handler down the column to populate.

Step 10:

Under the 61-90 days column, the formula will be similar in concept to the one input in step 9.
In cell I2, enter in the following formula: =IF(AND(TODAY()-$C2<=90,TODAY()-$C2>60),$D2,0)
Drag the fill handler down the column to populate.

IF(AND(TODAY()-$c2< -90,TODAY()-$c2>60), $D2,0) 12 A E G Order # Date Amount Due Days Outstanding Not Due 0-30 Days 31-60 Days 61-90 Days >90 Days $1,005.23 $5,869.23 $657.51 1 Customer 2 Myers Supply 3 Takei Corporation 4 Vandelay Enterprises 5 Home Renovation LLC 6 Records Industries 7 Rubber Made Goods 8 Produce Suppliers 1/1/2017 110023 177 0 0 110056 12/25/2016 184 0 C 8/5/2017 110089 0 657.51 0 C 3/6/2017 $968.78 113 110074 C 0 $1,658.98 $3,669.45 $10,052.36 $8,759.36 110096 5/25/2017 1658.98 33 C C 110078 6/30/2017 03669.45 0 0 0 110012 7/28/2017| 0 10052.4 0 9 ABC LLC 110011 2/15/2017 132 0 C 0 0 10 Salon and Spa Supplies 11 Pet Store Supplier 12 Exacto Goods 13 Firehouse Enterprises 14 Steamship Services 15 e2b teknologies 8/30/2017 $25,469.56 110028 0 25469.6 C 0 0 110091 7/31/2017 $3,542.98 $1,654.87 $569.42 3542.98 0 C 0 0 6/5/2017 22 110063 1654,87 0 0 110057 4/16/2017 72 569.42 0 0 C 3/9/2017 $987.65 110019 110 C 0 110073 5/25/2017 $2,456.98 2456.98 33 0 16 OO O O O C O -

Step 11:

To find the unpaid invoices greater than 90 days, the formula is quite simple. It is simply stating that if the difference between today’s date and the due date is greater than 90 to input the data from cell D2. Otherwise, input 0.
In cell J2, enter in the following formula: =IF(TODAY()-$C2>90,D2,0)
Drag the fill handler down the column to populate.

Step 12:

To sum up the value of all of the invoices in each column to know how much cash you have floating among each simply click and drag from the first empty cell underneath the “Not Due” column to the “>90” column. Then press ALT+=