Ms Office Tune Up

Calculating the difference between two dates is useful in many business scenarios, includ­ing receivables aging, interest calculations, benefits payments, and more. If all you need is the number of days between two dates, you can just subtract one date from another:

=Date1 -Date2

Here, replace Date1 and Date2 with date values (not date strings). Excel returns a positive number if Date1 is larger than Date2; it returns a negative number if Date1 is less than Date2.

Besides the basic date-difference formula, you can use the date functions from earlier in this chapter to perform date-difference calculations. Also, Excel boasts a number of work­sheet functions that enable you to perform more sophisticated operations to determine the difference between two dates. The rest of this section runs through a number of these date-difference formulas and functions.

In many business situations, you need to know the number of workdays between two dates. For example, when calculating the number of days an invoice is past due, it’s often best to exclude weekends and holidays. This is easily done using the NETWORKDAYS() function (read the name as “net workdays”), which returns the number of working days between two dates:

NETWORKDAYS(start_date, end_date[, holidays])

For example, here’s an expression that returns the number of workdays between December 1, 2007, and January 10, 2008, excluding December 25, 2007 and January 1, 2008:

=NETWORKDAYS(”12/1/2007″, “1/10/2008″, {”12/25/2007″,”1/1/2008″})

Figure 1 shows an update to the accounts receivable worksheet that uses NETWORKDAYS() to calculate the number of workdays that each invoice is past due.

Figure 1 This worksheet calcu­lates the number of workdays that each invoice is past due by using the NETWORK­DAYS() function.

Popularity: 4% [?]