How to Calculate Difference Between Two Dates and Times in Excel

If you want to find out the difference between two dates or times in excel, you will find this article useful. Here you can find how to calculate the difference between two dates and times in numerous formats in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


6 Practical Examples to Calculate Difference Between Two Dates and Times in Excel

1. Difference Between Two Dates

Suppose you want to know the times taken to complete different projects of your company. For that, you need to find the difference between the project starting date and the project ending date. Let’s say in your dataset, you have the Starting Date of different projects in column C and the Ending Date in column D.

Sample Dataset to Calculate Time Difference Between Two Dates in Excel

You can use Excel functions to find out the difference between two dates in days, weeks, months, and years. Remember, the difference must be calculated in Number format.


1.1 Difference Between Two Dates in Days

To find out the difference between two dates in days, we can use the DAYS function. To accomplish this, follow the steps below.

πŸ“Œ Steps:

  • First, click on cell E5.
  • Subsequently, insert the following formula and hit the Enter key.
=DAYS(D5,C5)

DAYS function to Calculate Time Difference Between Two Dates in Excel

  • You can also find out the difference in days by subtracting the two cells.

Subtraction Feature Calculate Time Difference Between Two Dates in Excel


1.2 Difference Between Two Dates in Weeks

To find out the difference between two dates in weeks, follow the steps below.

πŸ“Œ Steps:

  • At the very beginning, click on cell E6 and insert the following formula.
=DAYS(D6,C6)/7
  • Subsequently, press the Enter key.

Formula to Calculate Time Difference Between Two Dates in Weeks in Excel

  • You can also find out the difference in weeks by subtracting the two cells and dividing by 7.


1.3 Difference Between Two Dates in Months

To find out the difference between two dates in months, go through the steps below.

πŸ“Œ Steps:

  • Initially, click on cell E7 and insert the formula below.
=DAYS(D7,C7)/30
  • Following, hit the Enter key.

Formula to Calculate Time Difference Between Two Dates in Months in Excel

  • You can also find out the difference in months by subtracting the two cells and dividing by 30.

Subtraction Feature to Calculate Time Difference Between Two Dates in Months in Excel


1.4 Difference Between two Dates in Years

To find out the difference between two dates in years, follow the steps below.

πŸ“Œ Steps:

  • First and foremost, click on your desired cell (cell E8 here).
  • Afterward, insert the formula below and hit the Enter key.
=DAYS(D8,C8)/365

Formula to Calculate Time Difference Between Two Dates in Years in Excel

  • You can also find out the difference in years by subtracting the two cells and dividing by 365.

Subtract to Find Year Difference Between Two Dates

Read More: Excel Calculate Hours between Two Times after Midnight (3 Methods)


2. Getting Difference Between Two Times

Now, suppose you want to find out the times your employees are working. For that, you need to know the difference between the entry time and exit time of an employee. In your dataset, you have the Entry Time of different employees in column C and Exit Time in column D.

Sample Dataset to Calculate Time Difference Between Two Times in Excel

Remember the difference must be calculated in Number format.


2.1 Difference Between Two Times in Hours

Now, to find out the difference between two times in hours, follow the steps below.

πŸ“Œ Steps:

  • First, click on cell E5 and insert the following formula.
=(D5-C5)*24
  • Subsequently, press the Enter key.

Formula to Calculate Time Difference Between Two Times in Hours in Excel


2.2 Difference Between Two Times in Minutes

To find out the difference between two times in minutes, go through the steps below.

πŸ“Œ Steps:

  • Initially, click on cell E6 and write the following formula in the formula bar.
=(D6-C6)*1440
  • Subsequently, hit the Enter key.

Formula to Calculate Time Difference Between Two Times in Minutes in Excel


2.3 Difference Between Two Times in Seconds

At this time, to find out the difference between two times in seconds, follow the steps below.

πŸ“Œ Steps:

  • Initially, click on your desired cell (cell E7 here).
  • Following, insert the formula below and hit the Enter key.
=(D7-C7)*86400

Formula to Calculate Time Difference Between Two Times in Seconds in Excel

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


3. Calculating Difference in Each Time Unit

Suppose you want to know the difference of hours or minutes or seconds of two times, you also can do that using Excel. Now we will determine the Hour unit, Minute unit, and Second unit difference of Exit and Entry times of different employees.

Sample Dataset to Calculate Time Difference Between Two Dates and Times in Excel

  • To find the difference in hour units, we can use the HOUR function.
  • To do this, in cell E5, type the formula below and press the Enter key.
=HOUR(D5-C5)

Formula to Calculate Time Difference Between Two Dates and Times in Hours in Excel

  • Moreover, to find the difference in minute units we can use the MINUTE function.
  • In order to do this, in cell F5, type the formula below and hit the Enter key.
=MINUTE(D5-C5)

Formula to Calculate Time Difference Between Two Dates and Times in Minutes in Excel

  • Furthermore, to find the difference in the second unit, we can use the SECOND function.
  • For doing this, In cell G5, insert the following formula and press the Enter key.
=SECOND(D5-C5)

Formula to Calculate Time Difference Between Two Dates and Times in Seconds in Excel

Read More: How to Calculate Time Difference Between AM and PM in Excel


Similar Readings


4. Calculate the Date and Time Difference Simultaneously in Excel

If Date and Time are given as a single entry, we can find the date and time together. Consider the following Dataset. Here, Starting and Ending date and time of each project are given.

Sample Dataset to Calculate Time Difference Between Two Dates and Times in Excel

Go through the steps below to calculate the difference between two dates and times in Excel simultaneously.

πŸ“Œ Steps:

  • First, click on cell E5.
  • Following, insert the formula below.
=INT(D5-C5) & " Days, " & HOUR(D5-C5) & " Hours, " & MINUTE(D5-C5) & " Minutes, " & SECOND(D5-C5) & " Seconds "
  • Subsequently, hit the Enter key.

Formula to Calculate Time Difference Between Two Dates and Times in Excel

  • Afterward, use the fill handle feature to copy the same formula below and get the result for that data as well.

Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends


5. Finding Time Difference From a Start Time to Now

Time difference between the past and present can also be determined by Excel. Suppose your employees have started working some hours ago. Now you want to find out how much time they have worked so far. To find that we will use NOW function. Follow the steps below to accomplish this.

Sample Dataset to Calculate Elapsed Time

πŸ“Œ Steps:

  • At the very beginning, click on cell D5 and insert the formula below.
=NOW()-C5
  • Subsequently, hit the Enter key.

Formula to Calculate Elapsed Time

  • Afterward, use the fill handle feature to copy the same formula for all the cells below to get same result for the other data.

Read More: How to Calculate the Duration of Time in Excel (7 Methods)


6. Calculating Negative Times

You can also calculate negative time in excel but you need to change a default setting first to do this. Follow the steps below to accomplish this.

πŸ“Œ Steps:

  • First and foremost, go to the File tab.

Access File Tab

  • Following, click on the Options option from the expanded File tab.

Choose Options Option

  • As a result, the Excel Options window will appear.
  • Now, go to the Advanced tab here and tick on the option Use 1904 date system.
  • Subsequently, click on the OK button.

Excel Options Window

  • Thus, you can calculate negative time in excel. For example, we will subtract 12:50 PM from 9:30 AM in cell D5.

Calculated Negative Time

Read More: How to Subtract and Display Negative Time in Excel (3 Methods)


Conclusion

So, finding the difference between two dates and times in Excel is not so hard. I hope, after going through this article, now you can easily find the difference. If you have any confusion please leave a comment, so I will be able to help you.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo