How Long Between Two Dates

Use the DATEDIF function when you lot desire to calculate the departure betwixt two dates. First put a first date in a cell, and an end date in another. Then type a formula similar 1 of the post-obit.

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

Divergence in days

In this case, the offset engagement is in jail cell D9, and the end appointment is in E9. The formula is in F9. The "d" returns the number of full days betwixt the two dates.

Difference in weeks

In this example, the kickoff date is in prison cell D13, and the end date is in E13. The "d" returns the number of days. But notice the /7 at the cease. That divides the number of days by 7, since there are 7 days in a week. Note that this result likewise needs to be formatted equally a number. Press CTRL + one. Then click Number > Decimal places: ii.

Departure in months

In this example, the starting time engagement is in cell D5, and the end date is in E5. In the formula, the "yard" returns the number of full months betwixt the ii days.

Difference in years

In this example, the start date is in cell D2, and the end engagement 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 as well summate historic period or someone's time of service. The effect tin can be something like "2 years, 4 months, 5 days."

ane. Use DATEDIF to find the total years.

In this example, the offset 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 2 days.

2. Utilise DATEDIF again with "ym" to observe months.

In another jail cell, apply the DATEDIF formula with the "ym" parameter. The "ym" returns the number of remaining months past the concluding full twelvemonth.

three. Apply a dissimilar formula to observe days.

Now we need to detect the number of remaining days. Nosotros'll do this by writing a different kind of formula, shown above. This formula subtracts the start day of the ending calendar month (5/1/2016) from the original end date in cell E17 (v/half-dozen/2016). Here's how it does this: First the DATE role creates the date, 5/1/2016. It creates it using the year in jail cell E17, and the calendar month in cell E17. Then the one represents the first mean solar day of that month. The issue for the Appointment function is 5/1/2016. And so, nosotros decrease that from the original end date in cell E17, which is five/6/2016. five/vi/2016 minus 5/1/2016 is 5 days.

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

four. Optional: Combine three formulas in one.

Yous tin 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 to the lowest degree it's all in i. Tip: Press ALT+ENTER to put line breaks in your formula. This makes information technology easier to read. Also, press CTRL+SHIFT+U if you can't see the whole formula.

Download our examples

Other date and fourth dimension calculations

Equally you saw higher up, 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 utilize the TODAY() function, Excel uses your computer'due south current engagement for the date. Proceed in listen this will change when the file is opened once more on a futurity day.

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

Employ the NETWORKDAYS.INTL role when you want to summate the number of workdays between two dates. You can also have it exclude weekends and holidays as well.

Earlier you lot begin: Decide if y'all want to exclude holiday dates. If you practice, type a list of holiday dates in a dissever area or sail. Put each holiday date in its ain jail cell. So 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 stop engagement.

In this example, the start date is in prison cell D53 and the finish appointment is in cell E53.

2. In some other cell, blazon a formula like this:

Type a formula like the in a higher place example. The one 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 to a higher place 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 Lord's day.

3. If necessary, change the 1.

If Saturday and Sunday are not your weekend days, and then change the i 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 ever 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 lot begin" section above, then type it at the terminate like this. If you don't accept holidays, you tin leave the comma and MyHolidays out. If you are using Excel 2007, the above instance would exist this instead: =NETWORKDAYS(D53,E53,MyHolidays).

Tip: If you don't desire to reference a holiday range proper noun, you tin can too blazon a range instead, like D35:E:39. Or, you could type each holiday within the formula. For example if your holidays were on January ane and ii of 2016, you lot'd blazon them like this: =NETWORKDAYS.INTL(D53,E53,ane,{"one/one/2016","1/ii/2016"}). In Excel 2007, it would look like this: =NETWORKDAYS(D53,E53,{"ane/1/2016","1/2/2016"})

You can calculate elapsed time by subtracting ane fourth dimension from some other. First put a start fourth dimension in a prison cell, and an end time in another. Make certain to type a total time, including the hour, minutes, and a space before the AM or PM. Here's how:

1. Blazon a commencement fourth dimension and end fourth dimension.

In this example, the beginning time is in jail cell D80 and the end time is in E80. Brand sure to blazon the 60 minutes, minute, and a space before the AM or PM.

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

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

iii. Subtract the two times.

In some other cell, subtract the start time cell from the end fourth dimension cell.

4. Gear up the h:mm format.

Printing CTRL + i (or + one on the Mac). Choose Custom > h:mm then that the result excludes AM and PM.

To summate the fourth dimension between two dates and times, you can simply decrease one from the other. Even so, you lot must apply formatting to each cell to ensure that Excel returns the result you want.

one. Blazon two full dates and times.

In i cell, type a full start date/time. And in another cell, blazon a full end date/time. Each prison cell should have a calendar month, day, year, 60 minutes, minute, and a space before the AM or PM.

2. Prepare the iii/xiv/12 ane:30 PM format.

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

iii. Subtract the two.

In another cell, subtract the starting time date/time from the end date/time. The outcome will probably await like a number and decimal. You'll gear up that in the adjacent footstep.

four. Prepare the [h]:mm format.

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 departure betwixt 2 times

Source: https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38

Posted by: stephensbowd1949.blogspot.com

Share this post