Voci con l'etichetta :
Chiudi
Changelog
Chiudi
Provalo nel cloud

Calculating the number of days between dates

Introduction

When processing your spreadsheet data, you might need to count the number of days, months or years between two dates by subtracting the earlier date from the later one.

Using the Spreadsheet Editor you can easily perform all the necessary calculations, as the program offers you over 440 built-in functions. All you need to find out the date difference is to apply the DATEDIF function. This article explains how to do that.

The DATEDIF function is one of the date and time functions that is used to calculate the amount of time between two dates based on the interval specified.

The DATEDIF function has the following syntax:

DATEDIF (start-date, end-date, unit)

where

  • start-date is the earlier date.
  • end-date is the later date.
  • unit is the specified interval that can be one of the following:
    • Y - the number of complete years,
    • M - the number of complete months,
    • D - the number of complete days,
    • MD - the difference between the days (months and years are ignored, as if the dates were in the same year and the same month),
    • YM - the difference between the months (days and years are ignored, as if the dates were in the same year),
    • YD - the difference between the days (years are ignored, as if the dates were in the same year).

Step 1. Apply the DATEDIF function

To apply the DATEDIF function, follow these steps:

  1. select the cell where you wish to display the result,
  2. switch to the Formula tab,
  3. click the Date and time button,
  4. choose the DATEDIF function from the list,
  5. enter the required arguments separating them by commas.
days between dates Step 1 days between dates Step 1

Alternatively, you can add the DATEDIF function in one of the following ways:

  1. start adding the function:
    • switch to the Home tab,click the Insert Function Insert function icon icon situated at the top toolbar and select the Additional option,
    • or right-click within a selected cell and select the Insert Function option from the contextual menu,
    • or click the Function icon icon situated before the formula bar,
  2. in the Insert Function window, select the Date and time function group from the list,
  3. click the DATEDIF function and press the OK button,
  4. enter the required arguments separating them by commas.
The start-date and end-date arguments can be represented as:
  • nested functions that return dates, e.g. DATE(2015,3,23)
  • references to cells that contain dates, e.g. B2
  • text strings surrounded by quotation marks, e.g. "4/27/2017"
  • serial numbers of dates, e.g. 42852 that represents the date of April 27, 2017

The unit argument must be enclosed into the quotation marks.

In the examples below, we will consider, which of the unit arguments should be used depending on your tasks.

days between dates Step 1 days between dates Step 1

When the arguments are specified, press the Enter key. The result will be displayed in the selected cell.

if and excel Step 1 if and excel Step 1

In this example, we use the following function:

=DATEDIF(DATE(2015,3,23),"4/27/2017","Y") It returns the number of completed years in the entire period between March 23, 2015 and April 27, 2017. The result is 2 years.

Let's change the unit argument to calculate the difference between the same dates based on other intervals:

=DATEDIF(DATE(2015,3,23),"4/27/2017","M") - the function calculates the number of completed months in the entire period from March 23, 2015 to April 27, 2017. The result is 25 months.

=DATEDIF(DATE(2015,3,23),"4/27/2017","D") - the function calculates the total number of completed days in the entire period from March 23, 2015 to April 27. The result is 766 days.

=DATEDIF(DATE(2015,3,23),"4/27/2017","MD") - the function calculates the number of days between March 23, 2015 and April 27, 2017 ignoring months and years, i.e. it returns the difference in days between the 23-th and 27-th day. The result is 4 days.

=DATEDIF(DATE(2015,3,23),"4/27/2017","YM") - the function calculates the number of months between March 23, 2015 and April 27, 2017 ignoring days and years, i.e. it returns the difference in months between March and April. The result is 1 month.

=DATEDIF(DATE(2015,3,23),"4/27/2017","YD") - the function calculates the number of days between March 23, 2015 and April 27, 2017 ignoring years, i.e. it returns the difference in days between March 23 and April 27. The result is 35 days.

days between dates Step 1 days between dates Step 1

That's all. Now you can easily calculate the difference between two dates using the DATEDIF function.

How to create a new document?
Chiudi
Try now for free Try and make your decision No need to install anything
to see all the features in action
Ti potrebbe essere d'aiuto anche...
Chiudi