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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.