Aging AR highlighting cell when past a date multiple people.

[Jamie12]

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
[TABLE="width: 710"]
<colgroup><col span="4"><col><col span="6"></colgroup><tbody></tbody>[/TABLE]

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

 

[handysmurf]

do you have a cell that displays the days late?

 

[Jamie12]

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]

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

 

[handysmurf]

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]

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

 

[Jamie12]

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.