12/6/2021: I'm doing some work on the site this morning. If you notice anything crazy or not working, email me: amicron@gmail.com   [dismiss]

 Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

Excel: Aged Accounts Receivable
By Richard Rost     13 years ago

Q: Can you tell me how/if it is possible to do the following? I've created a table which shows an invoice number, amount and date.  I put in a formula which is "today's date" minus the invoice date to show how many days the invoice is past due, i.e. 1-15, 16-30, 31+.  Is there a way to have the number move to a different column depending on what the number is, i.e. if the number is 35, move the number to the 16-30 column?  Thanks!

A: Just put an IF function in your different columns to display the value if it falls in your date range. For example, in your "Less Than 30 Days" column, assuming DAYS LATE is in cell D1 and AMOUNT DUE is in C1, you could say:

=IF(D1 < 30, C1, 0)

Then you can make another cell to the right of that to show orders that are between 30 and 60 days late:

=IF(D1 >= 30 AND D1 < 60, C1, 0)

And so on.

I cover the IF function in detail in my Excel 201 class:
http://www.599cd.com?GOXL201

Upload Images   Link
JAWAD KHAN
13 years ago
I want to have masters on excel to be the best employee who make all the analysis, report, graphs and make lenghty work in hour or minutes in quality.

Start a NEW Conversation
 Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

 You may want to read these articles from the 599CD News:
 11/30/2021 Import Multiline Cells 11/11/2021 Link to Excel 11/11/2021 Read Write Excel Data 11/10/2021 Excel Moving Average 9/11/2021 Circular References 8/25/2021 Stock History 8/23/2021 Concatenation 8/17/2021 Flash Fill 8/5/2021 Stock Portfolio 8/4/2021 Stock Portfolio

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Account Login Online Theater Downloads Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Email Richard Mailing Address Phone Number Fax Number Course Survey
Keywords: excel tips accounts receivable if function  PermaLink