1. column
  2. TECHNOLOGY Q&A

Calculate The Number Of Days

Please note: This item is from our archives and was published in 2000. It is provided for historical reference. The content may be out of date and links may no longer function.

Related

No Results

TOPICS

    Uncategorized Article

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:

Advertisement

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