Chapter 12: Using Formulas with Dates and Times
12
Using these time codes, you can easily calculate the number of years, months, and days
between two dates. If someone was born on May 16, 1972, you could find that person’s age
in years, months, and days using these respective formulas:
=DATEDIF("5/16/1972",TODAY(),"y")
=DATEDIF("5/16/1972",TODAY(),"m")
=DATEDIF("5/16/1972",TODAY(),"d")Calculating the number of days between two dates
One of the most common date calculations performed in the corporate world is figuring the
number of days between two dates. Project management teams use it to measure perfor-
mance against a milestone, HR departments use it to measure time to fill a requisition, and
finance departments use it to track receivables aging. Luckily, it’s one of the easiest calcu-
lations to perform thanks to the handy DATEDIF function.
Figure 12.1 demonstrates a sample report that uses the DATEDIF function to calculate the
number of days outstanding for a set of invoices.
FIGURE 12.1
Calculating the number of days between today and the invoice date
Looking at Figure 12.1, you’ll see the formula in cell D4 is as follows:
=DATEDIF(C4,TODAY(),"d")This formula uses the DATEDIF function with the time code d. This tells Excel to return
the number of days based on the start date (C4) and the end date (TODAY).
Calculating the number of workdays between two dates
Oftentimes when reporting on the elapsed number of days between a start date and end
date, it’s not appropriate to count the weekends in the final number of days. Operations are
typically shut down on the weekends, so you would want to avoid counting those days.
You can use Excel’s NETWORKDAYS function to calculate the number of days between a start
date and end date excluding weekends.
As you can see in Figure 12.2, the NETWORKDAYS function is used in cell E4 to calculate the
number of workdays between 1/1/2019 and 12/31/2019.