Need Help with setting up something for Accounts Receivable

[erthqkckn]

Hello!

I need help setting up a spreadsheet to track our accounts receivable. The setup pretty much looks like this:

A B C D E F G H

Invoice #, Invoice Date, Invoice amount, 0-30 days, 31-60 days, 61-90 days, 91 - 120 days, and then 121 + days

Is there a way/formula that can automatically input the invoice amount into the appropriate column (either 0-30, 31-60, 61-90, etc.) and also make it bold and red if it's in a column that exceeds 60 days?

Your help will be much appreciated!!!!

[singerj6]

Hi erthqkckn, welcome.

I made this for you, check it out and see if it meets your needs. In the future, it's helpful to attach your own workbook.

singerj6_AR.xlsx

If this helps, please click the star icon in lower left corner.

[erthqkckn]

AR Tracking CL.xlsx

I have attached the spreadsheet of what we have. I receive a report every week and input whenever these have been paid but it's a hassle to go through every time one of these invoices goes into another month. It would be so much easier if every time I opened the document it would automatically switch it from 0-30 to 31-60 or whatever.

Thanks!

[singerj6]

AR Tracking CL_singerj6_multi.xlsx

Wasn't sure what date was the date of reference for you example so I made 2 versions for you to try...

2nd tab is based on today's date, this dynamically changes based on the actual date.
3rd tab is based on an inputted date (cell J1) which lets you keep the date as a static variable.

Wasn't sure which amount you wanted to report so I chose Invoice Amount. To switch to Balance Due Amount highlight columns, find H and replace with I for all (Ctrl + H)

Let me know if you have any questions.

[singerj6]

Took another look, I believe this is what you are looking for...

AR Tracking singerj6 final.xlsx