How to Calculate Difference Between Two Dates and Times in Excel

Part 1 – Calculating the Difference Between Two Dates in Excel

We have the Starting Date of different projects in column C and the Ending Date in column D.

The difference must be in the Number format.

Case 1.1 – Difference Between Two Dates in Days

Steps:

• Click on cell E5.
• Insert the following formula and hit the Enter key.

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

Case 1.2 – Difference Between Two Dates in Weeks

Steps:

• Click on cell E6 and insert the following formula.

• Press the Enter key.

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

Case 1.3 – Difference Between Two Dates in Months

Steps:

• Click on cell E7 and insert the formula below.

• Hit the Enter key.

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

Case 1.4 – Difference Between Two Dates in Years

Steps:

• Click on your desired cell (cell E8 here).
• Insert the formula below and hit the Enter key.

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

Read More: Excel Calculate Hours Between Two Times After Midnight

Part 2 – Calculating the Difference Between Two Times in Excel

We have the Entry Time of different employees in column C and the Exit Time in column D.

The difference must be calculated in the Number format.

Case 2.1 – Difference Between Two Times in Hours

Steps:

• Click on cell E5 and insert the following formula.

• Press the Enter key.

Case 2.2 – Difference Between Two Times in Minutes

Steps:

• Click on cell E6 and insert the following formula in the formula bar.

• Hit the Enter key.

Case 2.3 – Difference Between Two Times in Seconds

Steps:

• Click on your desired cell (cell E7 here).
• Insert the formula below and hit the Enter key.

Part 3 – Determining the Difference in Each Time Unit in Excel

We will determine the difference between the Exit and Entry times of different employees in hours, minutes, and seconds.

• In cell E5, use the formula below and press the Enter key.

• In cell F5, use the formula below and hit the Enter key.

• In cell G5, insert the following formula and press the Enter key.

Part 4 – Calculating the Difference Between Two Dates and Times Simultaneously

If Date and Time are given as a single entry, we can find the difference between them.

Steps:

• Click on cell E5.
• Insert the formula below.

• Hit the Enter key.

• Use the fill handle to copy the same formula in the cells below.

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

Part 5 – Finding the Time Difference from a Start Time to Now

We have a list of employees and when they started working. We’ll calculate the difference between now and that start time.

Steps:

• Click on cell D5 and insert the formula below.

• Hit the Enter key.

• Use the fill handle to copy the same formula for all the cells below to get the same result for the other data.

Read More: How to Calculate Hours Between Two Times in Excel

Part 6 – Calculating Negative Times in Excel

Steps:

• Go to the File tab.

• Click on Options from the expanded File tab.

• The Excel Options window will appear.
• Go to the Advanced tab and tick on the option Use 1904 date system.
• Click on the OK button.

• You can calculate negative times in Excel. For example, we will subtract 12:50 PM from 9:30 AM in cell D5.

Download the Practice Workbook

Related Articles

• How to Calculate Hours and Minutes in Excel
• How to Calculate Hours from Date and Time in Excel
• Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
• How to Calculate Travel Time Between Two Cities in Excel

<< Go Back to Calculate Time Difference | Calculate Time | Date-Time in Excel | Learn Excel