Teach Excel users to calculate elapsed time

A TechRepublic member we’ll call Zee wrote asking for help with date math. Zee, who works for a small correctional institute, had an Excel spreadsheet with the following information:

• Date of entry
• Date of scheduled departure

Using that data, Zee wanted to calculate two new columns, “length of stay in years, months, and days,” and “percent of total stay.”

Fortunately, Excel offers a built-in function, DATEDIF, which makes it easy to calculate elapsed calendar time. I sent Zee a sample solution, and she wrote back that it was exactly what she had in mind.

In this brief date-math tutorial, I’ll explain how I generated the output Zee requested. The screen shots were captured using Excel 2000. However, the process is identical in Excel 97, 2000, and 2002.

The easy part: Age in days
Zee’s need is similar to that of business spreadsheet users who need to calculate the age in days of unpaid invoices. Zee’s date of entry is like the invoice date. Suppose the entry date or invoice date is stored in cell A2, and you want to display in cell B2 the number of days that have elapsed between that date and the current date. To do so, you’d enter the following code in cell B2:
=NOW()-A2

Then, Excel would calculate the age in days by subtracting the date value entered in cell A2 from NOW(), which returns the current system date. Sort your unpaid invoices in descending order by that age in days column, and you have an aged receivables report.

Zee’s needs: “Length of stay so far”
The problem for Zee is that calculating the number of elapsed days isn’t enough. Zee wants to express that interval in the format X years, Y months, Z days.

So how do you convert an integer like 800 days into the appropriate number of years, months, and days? To get such a result in the early spreadsheet days, you had to parse out the month, day, and year components of the beginning and ending dates, and then jump through several If-test hoops to display correct results.

Now there’s the DATEDIF date difference function that takes this form:
=DATEDIF(begin_date,end_date,calendar_ unit_option)

The begin_date and end_date parameters must be expressions that return dates or references to cells containing dates. It’s the number of options for the calendar_unit_option parameter that makes some Excel users shy away from trying the function. Let’s look at the functions behind Zee’s solution, shown in Figure A.

Figure A

We used the DATEDIF function to generate the results of this date math.

Listing A shows what the formula in Figure A’s C2 looks like.

This formula concatenates (combines) into a single string the results of several functions. The DATEDIF functions provide the numbers, and the IF tests provide the correct text labels. (Note the embedded spaces following the year/years and month/months labels.)

Zee wants to show all three labels, even when the number of years, months, or days is zero. So, to make sure the result is grammatically correct, we had to use the IF function to check each value. If the value equals 1, the IF test returns one of the singular terms year, month, or day. In all other cases, where the DATEDIF function returns 0 or any number greater than 1, the IF test returns the plurals.

Digging DATEDIF
Here’s what to do if you want to calculate elapsed time in years, months, and days, but you don’t need text labels like Zee’s. Assuming the begin and end dates are in A2 and B2, just enter the three core function calls in separate columns. Here’s how the expressions work:

• Years. In the expression DATEDIF(A2,B2,”Y”), the “Y” parameter tells the function to return the number of whole years between the two dates.
• Months. The expression DATEDIF(A2,B2,”YM”) returns the number of months between the two dates, without regard to the years.
• Days. Finally, the expression DATEDIF(A2,B2,”MD”) returns the number of days in the interval without regard to month and year.

Table A shows a short summary of all the unit-of-measure options available when you use DATEDIF.

Zee’s length of stay so far
The other thing Zee wanted to determine was what percentage of each person’s stay had elapsed. In other words, she wanted to know the result of this calculation:
(Number of Days Stayed So Far) divided by (Total Number of Days in Planned Stay)

Figure B shows the results of sample calculations. Since this tutorial is primarily about the DATEDIF function, we used the expression
=DATEDIF(A2,C2,”D”)/DATEDIF(A2,B2,”D”)

In this formula, DATEDIF(A2,C2)”D”) returns the number of days between the entry date and our given date. The expression DATEDIF(A2,B2,”D”) yields the number of days between entry date and expected departure date.

Of course, in this case, you could have achieved Zee’s results without the DATEDIF function. Since subtracting two dates also yields the number of days between, the formula =(C2-A2)/(B2-A2) would result in the same percentage.

Figure B

The DATEDIF function makes it easy to calculate percentage of time elapsed.