Aging AR highlighting cell when past a date multiple people.

Jamie12

New Member
Ok, trying to figure out how to word this. I have a buy her pay her place and have people that owe me in a spreadsheet.
Everyone has different days of the month there payment is due. i want to have the cell highlight the day they are due and highlight a different color when 10 days late.
Here is how it is set up. when i enter there payment under each month it deducts what they owe.
Thanks

<colgroup><col span="4"><col><col span="6"></colgroup><tbody></tbody>

Puchase Date First Last 2019 Payments Due Remaining Balance January February March April May June
1/5/2019 John Doe \$2,000.00 5 \$2,000.00
1/10/2019 John Doe 2 \$3,000.00 10 \$3,000.00
1/15/2019 John Doe 3 \$4,000.00 15 \$4,000.00

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

handysmurf

Board Regular
do you have a cell that displays the days late?

Jamie12

New Member
Not right now, but i can add it. right now i go through the accounts and text the people and highlight the cell if they are late. but i have to many and it is taking to much time to go through all of them since they have so many different dates.

handysmurf

Board Regular
I would add one, that way you can later remark when/if the payment is made. remark = '10 instead of the sum formula that results in 10. That way it no longer highlights it. Otherwise once highlighted they will stay highlighted forever.

So then your sum column is simply "=today()-[payment date]

then you can use highlighting rules. for 1:9 days late = "=AND(\$B3>=0,\$B3<10)"

for 10+ days late = "=\$B3>=10" Both applied of course to your new sum column

Last edited:

handysmurf

Board Regular

Suggestion since it seems your spreadsheet is so far pretty simple and can be improved to make it more efficient for you.

I assume there is a different sheet for each customer.

I would suggest a master sheet listing all customers, by account number or name however you want to do it. Then you can find the max days late for each customer on their individual sheet, and then display that on the master list and also apply the highlighting rules to those. So you can see who needs attention at a glance instead of going through each customers sheet.

I would take the extra few seconds on setting up an account to put a link on the master list to the customers personal sheet as well.

handysmurf

Board Regular
Oh ... and add a link to each customers Sheet to jump back to the master list

Jamie12

New Member
i am trying to figure out how to past a picture of what my sheet looks like. i have all 175 on one sheet. I am not sure how to do what you are saying. anyway you can email me or i can email you so i can send you a example of my spreadsheet. thanks for your help. i have been wanting to do this for 3 years now since i started this business and now that i have so many it would be alot easier.

Replies
7
Views
563
Replies
3
Views
246
Replies
2
Views
415
Replies
1
Views
259
Replies
5
Views
844

1,127,159
Messages
5,623,092
Members
415,951
Latest member
Shen1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back