If you use Excel to store accounts receivable data, it’s a good idea to set up an aging worksheet that shows past-due invoices, calculates the number of days past due, and groups the invoices into past-due categories (1–30 days, 31–60 days, and so on).

Figure 5.9 shows a simple implementation of an accounts receivable database. For each invoice, the due date (column D) is calculated by adding 30 to the invoice date (column C). Column E subtracts the due date (column D) from the current date (in cell B1) to calculate the number of days each invoice is past due.

You might have noticed a problem with the due dates in Figure 5.9. The date in cell D11 falls on a weekend. The problem here is that the due date calculation just adds 30 to the invoice date. To avoid weekend due dates, you need to test whether the invoice date plus 30 falls on a Saturday or Sunday. The WEEKDAY() function helps because it returns 7 if the date is a Saturday, and 1 if the date is a Sunday:

WEEKDAY(serial_number[,return_type])
serial_number The date for which you want to calculate the day of the week.
return_type An integer value that determines how Excel calculates the days of the week:

return_type Days of the Week
1 1 (Sunday) through 7 (Saturday); this is the default value.
2 1 (Monday) through 7 (Sunday)
3 0 (Monday) through 6 (Sunday)

So, to check for a Saturday, you use the following formula:
=IF(WEEKDAY(C4 + 30) = 7, C4 + 32, C4 + 30)

Here, I’m assuming that the invoice date resides in cell C4. If WEEKDAY(C4 + 30) returns 7, the date is a Saturday, so you add 32 to C4 instead (this makes the due date the following Monday). Otherwise, you just add 30 days as usual. Checking for a Sunday is similar:

=IF(WEEKDAY(C4 + 30) = 1, C4 + 31, C4 + 30)

The problem, though, is that you need to combine these two tests into a single formula. To do that, you can nest one IF() function inside another. Here’s how it works:

=IF(WEEKDAY(C4+30) = 7, C4+32, IF(WEEKDAY(C4+30) = 1, C4+31, C4+30))

The main IF() checks to see if the date is a Saturday. If it is, you add 32 days to C4; otherwise, the formula runs the second IF(), which checks for Sunday.
For cash-flow purposes, you also need to correlate the invoice amounts with the number of days past due. Ideally, you’d like to see a list of invoice amounts that are between and 1 and 30 days past due, between 31 and 60 days past due, and so on. Figure 5.10 shows one way to set up accounts receivable aging.

For the invoice amounts shown in column G (1–30 days), the sheet uses the following formula (this is the formula that displays in G4):

=IF(E4 <= 30, F4, “”)

If the number of days the invoice is past due (cell E4) is less than or equal to 30, the formula displays the amount (cell F4); otherwise, it displays a blank.

The amounts in column H (31–60 days) are a little trickier. Here, you need to check if the number of days past due is greater than or equal to 31 days and less than or equal to 60 days. To accomplish this, you can press the AND() function into service:

=IF(AND(E4 >= 31, E4 <= 60), F4, “”)

The AND() function checks two logical expressions: E4> = 31 and E4 <= 60. If both are true, AND() returns TRUE, and the IF() function displays the invoice amount.

If one of the logical expressions isn’t true (or if they both are not true), AND() returns FALSE, and the IF() function displays a blank. Similar formulas display in column I (61–90 days) and column J (91–120 days). Column K (over 120) looks for past-due values that are greater than 120.

• The Library Card: Your Other "Rewards Credit Card" I have been a huge fan of libraries since I was in grade school. Being an avid reader, I would...
• Player Profile for Patty Sheehan Patty Sheehan was born on October 27 in 1956 in Middlebury, Vermont, and is an American professional golfer who became...
• Table Mountain Golf Course, Oroville, CA Table Mountain Golf Course is located in Oroville, CA Phone: 530 533-3922 Website: http://www.tablemountaingolf.com/ Course History: This is a terrific...
• Tip Tricks: Three Methods When You Don't Have a Tip Calculator Chances are, you've gone out to eat in a nice restaurant before, and intend to do so again.  Hopefully, when...
• Apple Announces iPad (Part 4 of 10) Apple announces the iPad. Here some infos about the device: Height: 9.56 inches (242.8 mm) Width: 7.47 inches (189.7 mm)...