Aging AR highlighting cell when past a date multiple people.

Jamie12

New Member
Joined
Jan 14, 2019
Messages
3
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Oh ... and add a link to each customers Sheet to jump back to the master list
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top