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 find out the difference between two dates and times in numerous formats.

Download Exercise Workbook

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 B and the Ending date in column C.

Difference between two dates

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.

Difference between two dates

i) Difference Between Two Dates in Days

To find out the difference between two dates in days, we can use the DAYS function. Type the formula in cell D5

= DAYS (end_date,start_date)

Here, end_Date = cell C5 and start_Date = cell B5

Difference between two dates

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

Difference between two dates

ii) Difference Between Two Dates in Weeks

To find out the difference between two dates in weeks type the formula in cell D6

= DAYS (end_date,start_date)/7

Here, end_Date = cell C6 and start_Date = cell B6

Difference between two dates

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

Difference between two dates

iii) Difference Between Two Dates in Months

To find out the difference between two dates in months type the formula in cell D7

= DAYS (end_date,start_date)/30

Here, end_Date = cell C7 and start_Date = cell B7

Difference between two dates

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

Difference between two dates

iv) Difference Between two Dates in Years

To find out the difference between two dates in months type the formula in cell D8

= DAYS (end_date,start_date)/365

Here, end_Date = cell C8 and start_Date = cell B8

Difference between two dates

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

Difference between two dates

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

2. Getting Difference Between Two Times

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 B and Exit time in column C.

Difference between two times

Remember the difference must be calculated in Number format.

Difference between two times

i) Difference Between Two Times in Hours

To find out the difference between two times in hours type the formula in cell D5

= (C5-B5)*24

Difference between two times

ii) Difference Between Two Times in Minutes

To find out the difference between two times in minutes type the formula in cell D6

= (C6-B6)*1440

Difference between two times

iii) Difference Between Two Times in Seconds

To find out the difference between two times in seconds type the formula in cell D7

= (C7-B7)*86400

Difference between two times

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.

Difference between two dates and times

To find the difference in hour units we can use the HOUR function. In cell D5, type the formula,

=HOUR(C5-B5)

Difference between two dates and times

To find the difference in minute units we can use the MINUTE function. In cell E6, type the formula,

=MINUTE(C6-B6)

Difference between two dates and times

To find the difference in the second unit we can use the SECOND function. In cell F7, type the formula,

=SECOND(C7-B7)

Difference between two dates and times

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


Similar Readings


4. Find the Date and Time Difference Simultaneously

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.

Difference between two dates and times

To find out the the difference, type the formula in cell D5

=INT(C5-B5) & " Days, " & HOUR(C5-B5) & " Hours, " & MINUTE(C5-B5) & " Minutes, " & SECOND(C5-B5) & " Seconds "

Difference between two dates and times

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

5. Calculating 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.

Difference between a time and present time

To find that we will use NOW function. In cell C5 type the formula.

=Now()- B5

Difference between a time and present time

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. For calculating negative time, go to File> Options.

Calculating negative times

Excel Options box will appear. Go to Advanced and Scroll down. Check the box Use 1904 date system. Click on OK.

Calculating negative times

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

Calculating negative times

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

Conclusion

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.


Related Articles

Prantick

Prantick

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

ExcelDemy
Logo