Related topics
×
Common uses of functions
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Common uses of functions

Calculate the difference between two dates

Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. Then type a formula like one of the following.

Warning: If the Start_date is greater than the End_date, the result will be #NUM!.

Difference in days

=DATEDIF(D9,E9,"d") with result of 856

In this example, the start date is in cell D9, and the end date is in E9. The formula is in F9. The “d” returns the number of full days between the two dates.

Difference in weeks

=(DATEDIF(D13,E13,"d")/7) and result: 122.29

In this example, the start date is in cell D13, and the end date is in E13. The “d” returns the number of days. But notice the /7 at the end. That divides the number of days by 7, since there are 7 days in a week. Note that this result also needs to be formatted as a number. Press CTRL + 1. Then click Number > Decimal places: 2.

Difference in months

=DATEDIF(D5,E5,"m") and result: 28

In this example, the start date is in cell D5, and the end date is in E5. In the formula, the “m” returns the number of full months between the two days.

Difference in years

=DATEDIF(D2,E2,"y") and result: 2

In this example, the start date is in cell D2, and the end date is in E2. The “y” returns the number of full years between the two days.

Calculate age in accumulated years, months, and days

You can also calculate age or someone’s time of service. The result can be something like “2 years, 4 months, 5 days.”

1. Use DATEDIF to find the total years.

=DATEDIF(D17,E17,"y") and result: 2

In this example, the start date is in cell D17, and the end date is in E17. In the formula, the “y” returns the number of full years between the two days.

2. Use DATEDIF again with “ym” to find months.

=DATEDIF(D17,E17,"ym") and result: 4

In another cell, use the DATEDIF formula with the “ym” parameter. The “ym” returns the number of remaining months past the last full year.

3. Use a different formula to find days.

=DATEDIF(D17,E17,"md") and result: 5

Now we need to find the number of remaining days. We'll do this by writing a different kind of formula, shown above. This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Here's how it does this: First the DATE function creates the date, 5/1/2016. It creates it using the year in cell E17, and the month in cell E17. Then the 1 represents the first day of that month. The result for the DATE function is 5/1/2016. Then, we subtract that from the original end date in cell E17, which is 5/6/2016. 5/6/2016 minus 5/1/2016 is 5 days.

Warning: We don't recommend using the DATEDIF "md" argument because it may calculate inaccurate results.

4. Optional: Combine three formulas in one.

=DATEDIF(D17,E17,"y")&" years, "&DATEDIF(D17,E17,"ym")&" months, "&DATEDIF(D17,E17,"md")&" days" and result: 2 years, 4 months, 5 days

You can put all three calculations in one cell like this example. Use ampersands, quotes, and text. It’s a longer formula to type, but at least it’s all in one. Tip: Press ALT+ENTER to put line breaks in your formula. This makes it easier to read. Also, press CTRL+SHIFT+U if you can’t see the whole formula.

Download our examples

You can download an example workbook with all of the examples in this article. You can follow along, or create your own formulas.

Download date calculation examples

Other date and time calculations

As you saw above, the DATEDIF function calculates the difference between a start date and an end date. However, instead of typing specific dates, you can also use the TODAY() function inside the formula. When you use the TODAY() function, Excel uses your computer's current date for the date. Keep in mind this will change when the file is opened again on a future day.

=DATEDIF(TODAY(),D28,"y") and result: 984

Please note that at the time of this writing, the day was October 6, 2016.

Use the NETWORKDAYS.INTL function when you want to calculate the number of workdays between two dates. You can also have it exclude weekends and holidays too.

Before you begin: Decide if you want to exclude holiday dates. If you do, type a list of holiday dates in a separate area or sheet. Put each holiday date in its own cell. Then select those cells, select Formulas > Define Name. Name the range MyHolidays, and click OK. Then create the formula using the steps below.

1. Type a start date and an end date.

Start date in cell D53 is 1/1/2016, end date is in cell E53 is 12/31/2016

In this example, the start date is in cell D53 and the end date is in cell E53.

2. In another cell, type a formula like this:

=NETWORKDAYS.INTL(D53,E53,1) and result: 261

Type a formula like the above example. The 1 in the formula establishes Saturdays and Sundays as weekend days, and excludes them from the total.

Note: Excel 2007 doesn't have the NETWORKDAYS.INTL function. However, it does have NETWORKDAYS. The above example would be like this in Excel 2007: =NETWORKDAYS(D53,E53). You don't specify the 1 because NETWORKDAYS assumes the weekend is on Saturday and Sunday.

3. If necessary, change the 1.

Intellisense list showing 2 - Sunday, Monday; 3 - Monday, Tuesday, and so on

If Saturday and Sunday are not your weekend days, then change the 1 to another number from the IntelliSense list. For example, 2 establishes Sundays and Mondays as weekend days.

If you are using Excel 2007, skip this step. Excel 2007's NETWORKDAYS function always assumes the weekend is on Saturday and Sunday.

4. Type the holiday range name.

=NETWORKDAYS.INTL(D53,E53,1,MyHolidays) and result: 252

If you created a holiday range name in the “Before you begin” section above, then type it at the end like this. If you don't have holidays, you can leave the comma and MyHolidays out. If you are using Excel 2007, the above example would be this instead: =NETWORKDAYS(D53,E53,MyHolidays).

Tip: If you don’t want to reference a holiday range name, you can also type a range instead, like D35:E:39. Or, you could type each holiday inside the formula. For example if your holidays were on January 1 and 2 of 2016, you'd type them like this: =NETWORKDAYS.INTL(D53,E53,1,{"1/1/2016","1/2/2016"}). In Excel 2007, it would look like this: =NETWORKDAYS(D53,E53,{"1/1/2016","1/2/2016"})

You can calculate elapsed time by subtracting one time from another. First put a start time in a cell, and an end time in another. Make sure to type a full time, including the hour, minutes, and a space before the AM or PM. Here’s how:

1. Type a start time and end time.

Start date/time of 7:15 AM, End date/time of 4:30 PM

In this example, the start time is in cell D80 and the end time is in E80. Make sure to type the hour, minute, and a space before the AM or PM.

2. Set the h:mm AM/PM format.

Format cells dialog box, Custom command, h:mm AM/PM type

Select both dates and press CTRL + 1 (or + 1 on the Mac). Make sure to select Custom > h:mm AM/PM, if it isn’t already set.

3. Subtract the two times.

=E80-D80 and result: 9:15 AM

In another cell, subtract the start time cell from the end time cell.

4. Set the h:mm format.

Format Cells dialog, Custom command, h:mm type

Press CTRL + 1 (or + 1 on the Mac). Choose Custom > h:mm so that the result excludes AM and PM.

To calculate the time between two dates and times, you can simply subtract one from the other. However, you must apply formatting to each cell to ensure that Excel returns the result you want.

1. Type two full dates and times.

Start date of 1/1/16 1:00 PM; End date of 1/2/16 2:00 PM

In one cell, type a full start date/time. And in another cell, type a full end date/time. Each cell should have a month, day, year, hour, minute, and a space before the AM or PM.

2. Set the 3/14/12 1:30 PM format.

Format Cells dialog, Date command, 3/14/12 1:30 PM type

Select both cells, and then press CTRL + 1 (or + 1 on the Mac). Then select Date > 3/14/12 1:30 PM. This isn't the date you'll set, it's just a sample of how the format will look. Note that in versions prior to Excel 2016, this format might have a different sample date like 3/14/01 1:30 PM.

3. Subtract the two.

=E84-D84 and result of 1.041666667

In another cell, subtract the start date/time from the end date/time. The result will probably look like a number and decimal. You'll fix that in the next step.

4. Set the [h]:mm format.

Format Cells dialog, Custom command, [h]:mm type

Press CTRL + 1 (or + 1 on the Mac). Select Custom. In the Type box, type [h]:mm.

Related Topics

DATEDIF function
NETWORKDAYS.INTL function
NETWORKDAYS
More date and time functions
Calculate the difference between two times

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×