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.


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


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:


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:


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 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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.