10/14/2015

How to Calculate Time in Microsoft Excel

In this Microsoft Excel Tutorial, we will calculate time difference using Microsoft Excel, and if you think that it is an easy task, subtracting end time from the start time, then you are surely going to face two problems.

Let's check out, how to calculate time difference in Excel.

Here we have, Employee Working Hour sheet, in which I want to calculate the Working Hours by subtracting Exit Time from Entrance Time. To do this, I use the formula, equals to b5 minus b4. The first problem that you will face is, Excel will always display the answer in AM, or PM format. Secondly, when I copy the same formula to the corresponding cells, Excel is showing hash errors. As we are performing direct subtraction, the result might be negative in some cells, and as the time should not be negative, Excel display the hash error.

To solve the first problem, you need to change the number format of the cells. To do this, select the cells, then right-click and choose, Format Cells. You can also use a keyboard shortcut to open Format Cells. Hold down the Control Key, and then press 1. Under the Number tab, from the Category list, click Custom, if it is not already selected, and then change the value in the Type box to h:mm.

Through this method, you can solve the first problem. But we still have another problem in this worksheet, and that is, the hash error. To overcome from the second problem, you need to change the formula, which we have used previously.

Select the Working Hours row and press, F2 to change the formula. Now, change the formula as follows, equals to, b5 minus b4, plus, (b4 > b 5) and instead of pressing Enter key, press Control plus Enter key. This way, formula will be applied to all the selected cells, and you no longer need to copy and paste the formula to another cells.

In this formula, we are using logical operation. Using the Logical Operation, the negative result will be treated as "True", and is treated as "1", and "False" is treated as "0". So, the time difference will no longer shows the negative values, so no more hash errors.

Hope you will enjoy this lesson. Do leave a comment below, and if you like our video, then don't forget to hit like, and share. Thanks for watching, and keep subscribing.