Calculate the difference between two times
Let’s say that you want find out how long it takes for an employee to complete an assembly line operation or a fast food order to be processed at peak hours. There are several ways to calculate the difference between two times.
Present the result in the standard time format
There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). You use the subtraction operator ( -) to find the difference between times, and then do either of the following:
Apply a custom format code to the cell by doing the following:
Select the cell.
On the Home tab, in the Number group, click the arrow next to the General box, and then click More Number Formats.
In the Format Cells dialog box, click Custom in the Category list, and then select a custom format in the Type box.
Use the TEXT function to format the times: When you use the time format codes, hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.
Example Table 1 — Present the result in the standard time format
Copy the following table to a blank worksheet, and then modify if necessary.
Calculate the difference between two dates
In this course:
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
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
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
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
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
In another cell, subtract the start time cell from the end time cell.
4. Set the h:mm format.
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.
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.
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.
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.
Press CTRL + 1 (or+ 1 on the Mac). Select Custom. In the Type box, type [h]:mm.
Excel how to calculate time duration
- Excel Tips
- Excel Functions
- Excel Formulas
- Word Tips
- Outlook Tips
How to calculate end date from start date and duration in Excel?
If you have a list of start dates and durations, now, you want to calculate the end date based on the start date and duration. How could you solve this job in Excel?
Kutools for Excel’sFormulasutility contains some commonly-used formulas, such as add years to date, calculate age based on birthday and so on. With these usual formulas, you can quickly get the result that you want without remembering any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.Download and free trial Now!
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
The following formulas can help you to calculate the end date from the given start date and duration, please do as this:
Calculate end date from start date and duration(weeks):
1. To calculate the end date from start date and number of weeks, please enter this formula:
=IF(A2,A2+(B2*7),"")into cell C2, and then drag the fill handle down to the cells you want to apply this formula, and a list of numbers are displayed as following screenshot shown:
Note: in this formula:A2is the start date andB2is the duration.
2. Then you should format the numbers as date format, clickHometab, chooseShort Datefrom theGeneraldrop down list, and the numbers have been formatted as date format, see screenshot:
Calculate end date from start date and duration(months):
If you want to calculate the end date from start date and number of months, the following formula may do you a favor:
Enter this formula:=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2))into cell C2, and then drag the fill handle down to the cells you want to fill this formula, and the end date has been calculated at once, see screenshot:
Note: In this formula:A2is the start date andB2is the duration.
Calculate end date from start date and duration(years):
To calculate end date based on given start date and number of years, please apply this formula:
Enter this formula:=DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2))into cell C2, and drag the fill handle down the cells that you want to apply this formula, and all end dates have been calculated. See screenshot:
Note: In this formula:A2is the start date andB2is the duration.
Perhaps, you are bored with the above formulas, here, I will talk about a useful tool-Kutools for Excel, with itsAdd years / months / weeks / daysto date features, you can quickly deal with this job as easily as possible.
|Kutools for Excel:with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installingKutools for Excel, please do as follows:
1. Click a cell where you want to output the calculated result, and then clickKutools>Formula Helper>Add days to date / Add years to date / Add months to date / Add weeks to date(here, I will take an example withAdd weeks to date), see screenshot:
2. In theFormula Helperdialog box, clickbutton to select the start date beside theDate Timetext box, and then clickbutton beside theNumbertext box to choose the duration cell, see screenshot:
3. Then clickOkbutton, and the end date has been calculated, and then drag the fill handle down to the cells to fill the calculated results, see screenshot:
Note: You can calculate end date from start date and number of days or months or years as same as the above steps.
Calculate Time Difference in Excel
How to Calculate a Time Difference in Excel
The easiest way to calculate a time difference in Excel is to simply subtract one time from the other.
This works because Excel stores dates as integers and times as decimal values (see the page on Excel dates for more details). It is only the formatting of an Excel cell that causes the cell’s contents to be displayed as a date or time, rather than as a simple number.
Therefore, when you want to calculate the difference between two times in Excel, you can simply subtract the time values in the same way as you would subtract any other numeric values.
Date and Time Difference Examples
Cell B3 of the following spreadsheet shows a simple example of how to calculate a time difference by subtracting the time in cell B1 from the time in cell B2.
In the example above, the time in cell B1 is internally represented by the number, 0.156597222 and the time in cell B2 is internally represented by the number 0.428993056. Subtracting these two numbers gives the result 0.272395833, which, when formatted as a time, is06:32:15(i.e. 6 hours 32 minutes and 15 seconds).
Formatting Cells as Times
When you calculate a time difference in Excel, you may want the result to be displayed as a time.
If it is not already displayed as a time, you can format the cell to have a ‘Time’ format, using any of the following methods:
Method 1 – Format Cells Using the Excel Ribbon Options
The easiest way to apply a simple ‘Time’ format is to select the cell(s) to be formatted and then select theTimeoption from the drop-down menu in the ribbon. This is found in the ‘Number’ group on theHometab of the ribbon (see below):
Method 2 – Format Cells Using the ‘Format Cells’ Dialog Box
If you require a greater selection of time formats, you may prefer to apply cell formatting using the ‘Format Cells’ dialog box as follows:
- Select the cell(s) to be formatted.
Open up the ‘Format Cells’ dialog box by either:
- Clicking on the dialog box launcher in the ‘Number’ grouping, on the Home tab of the Excel ribbon (see right above ).
- Using the keyboard shortcut Ctrl + 1 (i.e. press the Ctrl key and while holding this down press 1).
From theCategory:list, select the optionTime.
(If you want a time other than the default, you can then select one of the options from the list on the right hand side of the ‘Format Cells’ dialog box.)
The following spreadsheet shows a simple subtraction of two dates and times.
|1||Start Date & Time:||19-Jan-2011 21:00|
|2||End Date & Time:||21-Jan-2011 15:00|
In this example, the date and time in cell B1 is internally represented by the number, 40562.875 and the date and time in cell B2 is internally represented by the number 40564.625. Subtracting these two numbers gives the result 1.75 which is Excel’s internal value for the time42 hours 0 minutes.
Formatting Cells to Display Times With More Than 24 Hours:
It is likely that the standard time format (applied when you use the ribbon option to format a time) will only display times up to 24 hours.
However, further time formatting options are available in the ‘Format Cells’ dialog box. To apply a time format that displays more than 24 hours:
- Open up the ‘Format Cells’ dialog box (as described above).
- Ensure theNumbertab of the dialog box is selected.
- From theCategory:list, select the optionTime.
- From the ‘Type’ list that appears on the right hand side of the ‘Format Cells’ dialog box, select a time format that displays an hour value that is greater than 24.
- ClickOKto apply the selected format and close the dialog box.
Common Time Difference Errors
If Excel has a problem displaying the contents of a cell in a time or a date format, it will, instead display a row of hashes (see below).
Possible reasons for this are:
Possible Reason 1
The cell may not be wide enough to display the time or date in the specified format.
You can alter the width of an Excel column by dragging the bar (shown in the above image on the right ) until the cell is wide enough for the contents.
Alternatively, if you double-click on this bar, the cell should automatically re-size to fit the contents.
Possible Reason 2
Excel cannot display negative times. Therefore if the result of your subtraction formula is a negative value, and the cell containing this formula has date or time formatting, the result will be displayed as a row of hashes.
You can display the underlying numeric value by formatting the cell with the ‘General’ formatting type.
Calculate number of hours between two times
To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (midnight), things can get tricky. Read below to see several ways to manage this challenge.
How Excel tracks time
In Excel, one day equals 1, which represents 24 hours. This means times and hours are fractional values of 1, as shown in the table below:
Simple duration calculation
When start time and end time are in the same day, calculating duration in hours is straightforward. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:
When times cross midnight
Calculating elapsed time is more tricky if the times cross a day boundary (midnight). For example, if thestart timeis 10:00 PM one day, and theend timeis 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value, and Excel twill display a string of hash characters (########).
To correct this problem, you can use this formula for times that cross a day boundary:
By subtracting thestart timefrom 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as theend time.
This formula won’t work for times in the same day, so we can generalize and combine both formulas inside an IF statement like so:
Now when both times are in the same day,endis greater thanstart time, so the simple formula is used. But when the times across a day boundary the second formula is used.
MOD function alternative
By using the MOD function with a divisor of 1, we can simplify the formula above to this:
Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value. This version of the formula will handle both cases, so we can eliminate the conditional IF statement.
Note: neither formula above will handle durations greater than 24 hours. If you need this, see the date + time option below.
For more on modulo, here’s a good link on Khan Academy.
Formatting time durations
By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM. To remove the AM/PM, apply a custom number format like h:mm.
In cases where calculated time exceeds 24 hours, you may want to use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations of greater than 24 hours. If you don’t use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).
Simplifying the problem with date + time
You can simply the problem of calculating elapsed time by working with values that contain both date and time. To enter a date and time together, use a single space between time and date: 9/1/2016 10:00 AM
Then you can use a basic formula to calculate elapsed time:
In the example below start and end values contain both dates and times:
Formatted with the custom number format [h]:mm, to display elapsed hours.