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
 

Some videos you may like

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.

Jamie12

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

ADVERTISEMENT

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.
 

Jamie12

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

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top