Calculate between two dates excel

Subtract dates

You can quickly find the difference between two dates, control the format of the result, and extend the calculation to a list of dates. For example, you can quickly subtract one date from another date, or calculate the duration of items on a project schedule.

Use the provided sample data and the following procedures to learn how to calculate dates in a project schedule.

Copy the sample data

Copy the following sample data to cell A1 on a blank sheet.

Create Project Plan

Press + C .

In the sheet, select cell A1, and then press + V .

Calculate the difference in days

Calculate the number of days between two dates

Select cell D2, which is the first blank cell in the Duration column.

Type = C2-B2, and then press RETURN .

Excel displays the result as the number of days between the two dates (104).

To copy the formula into the remaining rows, drag the fill handle .

Excel adjusts the cell references automatically to include the correct values for each row.

Calculate the number of workdays between two dates

Select cell D2, which is the first blank cell in the Duration column.

If the cell is not blank, on the Edit menu, point to Clear, and then click All.

Do one of the following:

In Excel for Mac 2011, on the Formulas tab, under Function, click Formula Builder.

In Excel 2016 for Mac, on the Formulas tab, click Insert Function.

In the Formula Builder search box, type NETWORKDAYS.

In the list that appears, double-click NETWORKDAYS.

Under Arguments, click the box next to start_date, and then on the sheet, select cell B2 (6/8/2011)

Under Arguments, click the box next to end_date, and then on the sheet, select cell C2 (9/20/2011)

Leave the box next to holidays blank.

The result is 75 working days, not accounting for holidays.

To copy the formula into the remaining rows, drag the fill handle .

Excel adjusts the cell references automatically to include the correct values for each row.

Tip: See NETWORKDAYS function and NETWORKDAYS.INTL function to learn more about these formulas, including how to account for holidays.

Calculate the number of months between two dates in the same year

Excel treats months as values (1 through 12), which makes it easy to subtract an earlier date from another date as long as both dates fall in the same year. Use the MONTH function to obtain the month value for each date, and then calculate the difference between the values.

Select cell D2, which is the first blank cell in the Duration column.

If the cell is not blank, on the Edit menu, point to Clear, and then click All.

In cell D2, type =MONTH(C2)-MONTH(B2), and then press RETURN .

The result is 3.

Calculate the number of months between two dates in different years

When you have to find the number of months between dates that fall in different years, you can use a formula that determines the number of years between the two dates, converts the difference in years to months, and then accounts for any additional months to obtain an accurate result. Use the MONTH and YEAR functions to make this calculation.

If the cell is not blank, on the Edit menu, point to Clear, and then click All.

In cell D3, type =(YEAR(C3)-YEAR(B3))*12+MONTH(C3)-MONTH(B3), and then press RETURN .

The result is 9.

Calculate the number of years between two dates

Excel treats years as values. Use the YEAR function to obtain the year value for each date, and then calculate the difference between the values.

If the cell is not blank, on the Edit menu, point to Clear, and then click All.

In cell D4, type =YEAR(C4)-YEAR(B4) and then press RETURN .

Источник

Calculate years between dates

Related functions

Summary

To calculate the number of years between two dates, you can use the YEARFRAC function, which will return a decimal number representing the fraction of a year between two dates. In the example shown, the formula in D6 is:

Generic formula

Explanation

The YEARFRAC function returns a decimal number representing the fractional years between two dates. For example:

Here are a few examples of the results that YEARFRAC calculates:

Start date	End date	YEARFRAC result
1/1/2015	1/1/2016	1
3/15/1970	9/15/1976	6.5
1/1/2000	7/15/2000	.5389
6/1/2000	6/25/1999	.9333

In the example shown, the formula in D6 is:

Rounding results

Once you have the decimal value, you can round the number if you like. For example, you could round to the nearest whole number with the ROUND function:

Whole years only

You might also want to keep only the integer portion of the result with no fractional value, so that you are only counting whole years. In that case, you can just wrap YEARFRAC in the INT function:

If you need to calculate years on an ongoing basis, for example to get age based on a birthday, see the example here.

Note: The YEARFRAC function has an optional 3rd argument that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The YEARFRAC page provides more information.

Источник

Calculate the Number of Months Between Two Dates in Excel (Easy Formulas)

If you manage multiple projects, you would have a need to know how many months have passed between two dates. Or, if you’re in the planning phase, you may need to know the same for the start and end date of a project.

There are multiple ways to calculate the number of months between two dates (all using different formulas).

In this tutorial, I will give you some formulas that you can use to get the number of months between two dates.

So let’s get started!

This Tutorial Covers:

Using DATEDIF Function (Get Number of Completed Months Between Two Dates)

It’s unlikely that you will get the dates that have a perfect number of months. It’s more likely to be some number of months and some days that are covered by the two dates.

For example, between 1 Jan 2020 and 15 March 2020, there are 2 months and 15 days.

If you only want to calculate the total number of months between two dates, you can use the DATEDIF function.

Suppose you have a dataset as shown below where you only want to get the total number of months (and not the days).

Below is the DATEDIF formula that will do that:

The above formula will give you only the total number of completed months between two dates.

In case you want to get the total number of months as well as days between two dates, you can use the below formula:

Note: DATEDIF function will exclude the start date when counting the month numbers. For example, if you start a project on 01 Jan and it ends on 31 Jan, the DATEDIF function will give the number of months as 0 (as it doesn’t count the start date and according to it only 30 days in January have been covered)

Using YEARFRAC Function (Get Total Months Between Two Dates)

Another method to get the number of months between two specified dates is by using the YEARFRAC function.

The YEARFRAC function will take a start date and end date as input arguments and it will give you the number of years that have passed during these two dates.

Unlike the DATEDIF function, the YEARFRAC function will give you the values in decimal in case a year has not elapsed between the two dates.

For example, if my start date is 01 Jan 2020 and end date is 31 Jan 2o20, the result of the YEARFRAC function will be 0.833. Once you have the year value, you can get the month value by multiplying this with 12.

Suppose you have the dataset as shown below and you want to get the number of months between the start and end date.

Below is the formula that will do this:

This will give you the months in decimals.

In case you only want to get the number of complete months, you can wrap the above formula in INT (as shown below):

Another major difference between the DATEDIF function and YEARFRAC function is that the YEARFRAC function will consider the start date as a part of the month. For example, if the start date is 01 Jan and end date is 31 Jan, the result from the above formula would be 1

Below is a comparison of the results you get from DATEDIF and YEARFRAC.

Using the YEAR and MONTH Formula (Count All Months when the Project was Active)

If you want to know the total months that are covered between the start and end date, then you can use this method.

Suppose you have the dataset as shown below:

Below is the formula that will give you the number of months between the two dates:

This formula uses the YEAR function (which gives you the year number using the date) and the MONTH function (which gives you the month number using the date).

The above formula also completely ignores the month of the start date.

For example, if your project starts on 01 Jan and ends on 20 Feb, the formula shown below will give you the result as 1, as it completely ignores the start date month.

In case you want it to count the month of the start date as well, you can use the below formula:

You may want to use the above formula when you want to know-how in how many months was this project active (which means that it could count the month even if the project was active for only 2 days in the month).

So these are three different ways to calculate months between two dates in Excel. The method you choose would be based on what you intend to calculate (below is a quick summary):

• Use the DATEDIF function method if you want to get the total number of completed months in between two dates (it ignores the start date)
• Use the YEARFRAC method when you want to get the actual value of months elapsed between tow dates. It also gives the result in decimal (where the integer value represents the number of full months and decimal part represents the number of days)
• Use the YEAR and MONTH method when you want to know how many months are covered in between two dates (even when the duration between the start and the end date is only a few days)

Below is how each formula covered in this tutorial will count the number of months between two dates:

Hope you found this Excel tutorial useful.

You may also like the following Excel tips and tutorials:

Источник

How to calculate days between dates in Excel

by Svetlana Cheusheva, updated on March 17, 2023

This tutorial will teach you a few quick and easy ways to find out how many days are between two dates in Excel.

Are you wondering how many days between two dates? Maybe, you need to know the number of days between today and some date in the past or future? Or, you just want to count working days between two dates? Whatever your problem is, one of the below examples will certainly provide a solution.

Days between dates calculator

If you are looking for a quick answer, just supply the two dates in the corresponding cells, and our online calculator will show you how many days there are from date to date:

Note. To view the embedded workbook, please allow marketing cookies.

Curious to know the formula that has calculated your dates? It’s as simple as =B3-B2 🙂

Below you will find the detailed explanation on how this formula works and learn a few other methods to calculate days between dates in Excel.

How many days between dates calculation

The easiest way to calculate days between dates in Excel is by subtracting one date from another:

For example, to find out how many days are between dates in cells A2 and B2, you use this formula:

Where A2 is an earlier date, and B2 is a later date.

The result is an integer that represents no. of days between two dates:

How this formula works

As you probably know, Microsoft Excel stores dates as serial numbers starting on 1-Jan-1900, which is represented by the number 1. In this system, 2-Jan-1900 is stored as the number 2, 3-Jan-1900 as 3, and so on. So, when subtracting one date from another, you actually subtract the integers representing those dates.

In our example, the formula in C3, subtracts 43226 (the numeric value of 6-May-18) from 43309 (the numeric value of 28-Jul-18) and returns a result of 83 days.

The beauty of this method is that it works perfectly in all cases, no matter which date is older and which is newer. If you are subtracting a later date from an earlier date, like in row 5 in the screenshot above, the formula returns a difference as a negative number.

Calculate number of days between dates in Excel with DATEDIF

Another way to count days between dates in Excel is by using the DATEDIF function, which is specially designed to work out the date difference in various units, including days, months and years.

To get the number of days between 2 dates, you supply the start date in the first argument, end date in the second argument, and «d» unit in the last argument:

In our example, the formula goes as follows:

Unlike the subtraction operation, a DATEDIF formula can only subtract an older date from a newer date, but not the other way round. If the start date is later than the end date, the formula throws a #NUM! error, like in row 5 in the screenshot below:

Note. DATEDIF is an undocumented function, meaning it is not present in the list of functions in Excel. To build a DATEDIF formula in your worksheet, you will have to type all the arguments manually.

Count days between dates with Excel DAYS function

The users of Excel 2013 and Excel 2016 have one more amazingly simple way to calculate days between two dates — the DAYS function.

Please pay attention that compared to DATEDIF, a DAYS formula requires the arguments in the reverse order:

So, our formula takes the following shape:

Like subtraction, it returns the difference as a positive or negative number, depending on whether the end date is greater or smaller than the start date:

How to calculate number of days between today and another date

In fact, calculating the number of days from or before a certain date is a particular case of «how many days between dates» math. For this, you can use any of the formulas discussed above and supply the TODAY function instead of one of the dates.

To calculate the number of days since date, i.e. between a past date and today:

To count the number of days until date, i.e. between a future date and today:

As an example, let’s calculate the difference between today and an earlier date in A4:

=TODAY() — A4

And now, let’s find out how many days are between today and a later date:

How to calculate working days between two dates in Excel

In situations when you need to get the number of days between two dates without weekends, use the NETWORKDAYS function:

The first two arguments should already look familiar to you, and the third (optional) argument allows excluding a custom list of holidays from the day count.

To find out how many working days are between two dates in columns A and B, use this formula:

=NETWORKDAYS(A2, B2)

Optionally, you can enter your holiday list in some cells and tell the formula to leave out those days:

=NETWORKDAYS(A2, B2, $A$9:$A$10)

As the result, only business days between two dates are counted:

Tip. In case you need to handle custom weekends (e.g. weekends are Sunday and Monday or Sunday only), use the NETWORKDAYS.INTL function, which allows you to specify what days of the week should be considered weekends.

Find number of days between two dates with Date & Time Wizard

As you see, Microsoft Excel provides a handful of different ways to count days between dates. If you are not sure which formula to use, let our Date & Time Wizard do the how-many-days-between-two-dates calculation for you. Here’s how:

1. Select the cell in which you want to insert the formula.
2. On the Ablebits Tools tab, in the Date & Time group, click Date & Time Wizard:
   
3. In the Date & Time Wizard dialog window, switch to the Difference tab and do the following:
   • In the Date 1 box, enter the first date (start date) or a reference to the cell containing it.
   • In the Date 2 box, enter the second date (end date).
   • In the Difference in box, select D.

The wizard immediately shows a formula preview in the cell and the result in the Difference in box.

Click the Insert formula button and have the formula inserted in the selected cell. Done!

A double-click on the fill handle, and the formula gets copied across the column:

To display the date difference in a slightly different way, you are free to choose any of the additional options:

• Show text labels — the word «days» will appear along with the number, like shown in the screenshot below.
• Do not show zero unitsВ­ — if the date difference is 0 days, an empty string (blank cell) will be returned.
• Negative result if Date 1 > Date 2 — the formula will return a negative number is the start date is later than the end date.

The screenshot below shows a couple of additional options in action:

This is how you calculate the number of days between dates in Excel. If you’d like to test our Date & Time Formula Wizard in your worksheets, you are welcome to download 14-day trial version of Ultimate Suite, which includes this as well as 70+ other time-saving tools for Excel.

Источник