Method 1 – Using DAYS Function to Calculate Time Difference in Excel Between Two Dates
- Insert the following formula in cell E5:
=DAYS(D5,C5)Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- Hit Enter to confirm.
- Drag the Fill Handle icon from cell E5 to E10.
- You will get the time difference in days between the two dates.
Read More: How to Calculate Time Difference Between Two Dates in Minutes in Excel
Method 2 – Combining INT and TEXT Functions to Calculate Time Difference Between Two Dates
- Insert the following formula in cell E5:
=INT(D5-C5)&" days "&TEXT(D5-C5,"h"" hrs ""m"" mins """)Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- INT(D5-C5)&” days “ returns the time difference in days.
- TEXT(D5-C5,”h”” hrs “”m”” mins “””) returns the time difference in hours, minutes, and seconds.
- Press Enter to apply the formula.
- Drag the Fill Handle icon from cell E5 to E10.
- This gets you the time difference between two dates in days, hours, and minutes.
Read More: Difference Between Two Dates in Months in Excel
Method 3 – Using NETWORKDAYS Function to Measure Time Difference Between Two Dates
- Copy this formula in cell E5:
=NETWORKDAYS(C5,D5)Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- Press Enter to apply it.
- Use the Fill Handle icon to fill the formula from cell E5 to E10.
- Excel will calculate the time difference in days excluding the holidays.
Read More: How to Minus Number of Days or a Date from Today in Excel
Method 4 – Applying Excel DATEDIF Function to Compute Time Difference Between Two Dates
- Paste the following formula in cell E5:
=DATEDIF(C5,D5,"d")Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- “d” specifies that the calculated time format is in days.
- Press the Enter key to confirm and apply the formula.
- Drag the Fill Handle icon from cell E5 to E10 to fill in the other cells.
- Here is the calculated result:
Method 5 – Using Excel TEXT Function to Calculate Time Difference Between Two Dates
- Use following formula for cell E5:
=TEXT(D5-C5,"yy"" years ""dd"" days """)Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- “yy”” years “”dd”” days “”” specifies the time format in years and days.
- Press the Enter key.
- Drag the Fill Handle icon from cell E5 to E10.
- So you will get the time difference between two dates in years and days.
Method 6 – Subtracting Two Dates to Calculate Time Difference Between Two Dates
- Insert the formula below into cell E5:
=D5-C5Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- Press the Enter button.
- Drag the Fill Handle icon from cell E5 to E10.
- You will get the time difference in the Duration column.
Read More: How to Subtract Dates in Excel to Get Years
Method 7 – Joining Excel TIME, HOUR, MINUTE, & SECOND Functions to Calculate Time Difference
The following formula can calculate the time difference only in hours, minutes, and seconds. It completely ignores the date part but focuses on the time part. Follow these steps:
- Use the following formula in cell E5:
=TIME(HOUR(D5),MINUTE(D5),SECOND(D5))-TIME(HOUR(C5), MINUTE(C5),SECOND(C5))Formula Explanation
- D5 is the finishing date.
- C5 is the beginning date.
- HOUR(D5),MINUTE(D5),SECOND(D5) extract the hour, minute, and second part from D5.
- HOUR(C5),MINUTE(C5),SECOND(C5) extract the hour, minute, and second part from C5.
- Hit Enter to apply it.
- Drag the Fill Handle icon from cell E5 to E10.
- You will get the time difference in hours, minutes, and seconds like this:
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
<< Go Back to Subtract Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
THANKS YOU ARE A LIFE SAVER
Hello Esraa Yaseen,
You are most welcome. We are glad to help you.
Regards
ExcelDemy
Vehicle In Date Vehicle In Time Gate Out Date Gate Out Time
15-09-2024 9:25 AM 17-09-2024 1:25 PM
15-09-2024 9:33 AM 15-09-2024 8:51 PM
15-09-2024 9:37 AM 16-09-2024 6:21 PM
Can you please help. Need answer in Hours and Minutes only.
Hello Sanjay Dutt Dobhal,
To calculate the time difference you need to insert the date and time based on your regional settings of Excel.
Format the date and time based on your settings then use the CONCATENATE formula to calculate the time difference from date and time.
=CONCATENATE(C2,””,D2)-CONCATENATE(A2,””,B2)
Right-click the time difference cell >> choose “Format Cells” >> select “Custom” and enter the format [h]:mm
Regards
ExcelDemy
Can you help, I need to calculate the time between shifts in hours and minutes. I have used the MOD function which works fine for most but where the shift has ended on 4th at 12:17 and re-started on 5th at 13:22 I am missing the additional 24 hours and only getting a difference of 1:06. I am no excel whizz and it is frying my brain!
Date: Rest Hours Time on Time off Duty Hours
30-Sep-24 12:11 08:05 21:38 13:33
01-Oct-24 06:10 03:48 12:10 08:22
02-Oct-24 11:49 23:59 00:01 00:02
03-Oct-24 09:26 09:27 12:43 03:16
04-Oct-24 19:51 08:34 12:17 03:43
05-Oct-24 13:23 02:38 13:15
06-Oct-24 06:23 09:01 19:11 10:10
Weekly Total 65:50 52:21
Daily Average 10:58 07:28
Hello Fiona,
To calculate the time difference across shifts that span multiple days, you’ll need to adjust your formula. Instead of just using the MOD function, you can directly subtract the end time from the start time while ensuring the dates are included.
For example, if your end time is in cell D2 and your start time is in cell C2, you can use:
=(D2 + IF(D2 < C2, 1, 0)) - C2 This formula adds 1 day if the end time is earlier than the start time. Regards ExcelDemy