Subtracting Dates in Excel: DATEDIF Function vs Arithmetic

One of the most commonly performed date-related tasks in the business world is calculating the number of days between two dates. In Excel, you can accomplish this either through arithmetic subtraction or using the DATEDIF function.



How to Calculate Days Remaining Until a Date?

Difference between dates as per condition

Project managers perform such calculations to determine the percentage completion of project implementation based on milestone dates. In personal activities, these operations serve for filling out employee declarations, and in finance departments, for depreciation calculations.

To solve this task in Excel, you can proceed in two ways:

  1. Use the DATEDIF function.
  2. Perform straightforward subtraction of the older date from the newer one.

Below is a schematic representation of a report using the DATEDIF function and the second method with regular date subtraction. The report calculates the days remaining from today to invoice payment deadlines:

DATEDIF and date subtraction

As shown in the image, the formula in cell D4 is:

=DATEDIF(TODAY(), A2, "d")

The first formula uses the DATEDIF function with a time unit code "d" (days) as its third argument. As a result of the formula calculation, the DATEDIF function returns the number of days in that period. The cell values show the number of days from today's date (returned by the TODAY function) to the payment deadlines initially indicated in column A (Payment Date).

The second formula variant:

=A2-TODAY()

In the second formula, we perform a regular date subtraction. We subtract the older (from column A) from the newer (current) date. The result remains the same. Why does this work? In Excel, like all other MS Office suite programs, the "1900 system" date numbering system is used. Each date in Excel is the number of days since January 1, 1900. If you change the cell format to "General" or "Number," you'll see a numerical code instead of a date. This number represents the day's sequential number as of that date. Therefore, when subtracting dates, we merely subtract the lesser number of days from the greater one, yielding the same result as the function.

Practical Example of Using the DATEDIF Formula

To display the difference between two dates based on several conditions:

  1. Difference in years, months, and days.
  2. Only in days.
  3. Only in months.
  4. Only in years.
  5. In months and the remaining days of the last incomplete month.
  6. Difference in years and remaining months.
  7. In years and the number of days of the last incomplete year.

All these 7 condition combinations are controlled using three buttons, as shown below:

Difference between dates as per condition

download file Download

By holding down the CTRL key, you can simultaneously toggle the buttons in pairs. When activating a specific button combination on the Slicer control panel, the formula will automatically display the date difference in a label style, aligning with the user's conditions.