Calculate The Number Of Days


Q. When I work on spreadsheet projects I frequently have to calculate the number of days between two dates and then use that figure in a later calculation. Can Excel calculate the number of days for me? It would save lots of time.

A. Yes, it can, and the formula is easy to use.

=end–start

For example, if you want to calculate the number of days between June 5 and June 1, the formula would be

=6/5/2000–6/1/2000

But be careful: There’s a not-so-obvious step here you need to take. The cells in which the start date and end date are entered must be formatted as dates (to set that format, right click on the cell; then click on Format Cells, Date ), and the cell in which the answer appears must be formatted as Number .

By the way, if you’re working on several date-difference formulas, you can make the work easier by using labels for the dates (such as “End” and “Start”) in the formulas rather than alphanumeric cell addresses. For example, if you label one column End and the next one Start (see exhibit 4), the above formula will work perfectly. Otherwise the formula would have to read:

=C3–B3

Exhibit 4

Now, if you try this trick and discover that the labels won’t work in the formula, your Excel is probably not defaulted to accept labels. To correct that, open the Tools menu in the toolbar and click on Options and Calculations . Under Workbook options, check the Accept labels in formulas box. You’ll probably have to close Excel and reopen it before the new default is evoked.

There are many ways to use the various Excel date functions. For example, you have a December 31, 2000 deadline for completing a project. You can use the =DATEVALUE and NOW() functions to tell you how many calendar days there are between the day you open the workbook and the future deadline (see exhibit 5). The formula would look like this:

=DATEVALUE(“12/31/2000”)–NOW()

Exhibit 5

Once again, there are some not-so-obvious steps you need to take for the formula to work correctly. You must format the answer cell to Accounting , remove the $ symbol and set the decimal places to 0.

Do you have a technology question for this column? Send them to Senior Editor Stanley Zarowin via e-mail at zarowin@mindspring.com or regular mail at the Journal of Accountancy, Harborside Financial Center, 201 Plaza Three, Jersey City, NJ 07311-3881. We regret that we cannot answer letters individually.

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: Economy and health care changes top CPAs’ list

CPA decision-makers’ economic outlook and the House Republicans’ proposed tax changes as part of replacing the Patient Protection and Affordable Care Act received attention recently. See how much you know with this short quiz.