Calculate the difference between two times in Excel

 Its tough to manage times and sometimes it is difficult to manage the time related calculations even in Excel. Suppose you have a situation where you are given Start and End time and you have to find the time difference between two clock times. This is a usual calculation on time sheets to calculate the number of hours a worker has worked so that we can compute the compensation/wages/salaries accordingly.

Consider the following data:

Now it seems quite simple as one can simply deduct the end time values from start time and we should have the time difference. However if we actually try to solve it we face two problems. Have a look at the following illustration and pay close attention to what we get in the end:

First problem is that result is not formatted properly and the totals are with AM or PM

Second problem is that some results are hash errors. This is because the result is negative and time cannot be negative. Therefore Excel is showing the result as #’s.

The first problem can be sorted by changing the number format but to fix the second problem we will have to make slight modification in the formula.

To correct the first problem so that we can get rid of AM/PM take following steps:

1. Select the cells containing total hours results and hit CTRL+1. Format cells dialogue box will open
2. Click custom on the left hand side and in the type field put this:
   h:mm
3. Click OK

And now the result is much more meaningful.

To correct the second select the cells containing total hours and hit F2 key and change the formula as below:

=C6-C5+(C5>C6)

And instead of hitting Enter key press Ctrl+Enter to confirm the change in all selected cells at once.

By attaching a logical operation we have managed the negative result problem as TRUE is treated as “1” and FALSE is treated as “0”. And thus fixing the problem of negative figure (and one hidden problem because of 24 hours system embedded in excel).