# 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.
`=DAYS(D5,C5)`

• 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.
`=DAYS(D6,C6)/7`
• 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.
`=DAYS(D7,C7)/30`
• 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.
`=DAYS(D8,C8)/365`

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

## 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.
`=(D5-C5)*24`
• 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.
`=(D6-C6)*1440`
• 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.
`=(D7-C7)*86400`

## 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.
`=HOUR(D5-C5)`

• In cell F5, use the formula below and hit the Enter key.
`=MINUTE(D5-C5)`

• In cell G5, insert the following formula and press the Enter key.
`=SECOND(D5-C5)`

## 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.
`=INT(D5-C5) & " Days, " & HOUR(D5-C5) & " Hours, " & MINUTE(D5-C5) & " Minutes, " & SECOND(D5-C5) & " Seconds "`
• Hit the Enter key.

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

## 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.
`=NOW()-C5`
• 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.

## 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF