November 25, 2020

How to calculate timings of workers in Excel sheet?

Let’s say we have the below table in a spreadsheet containing the office in time and out time for couple of workers. Now we need to calculate the time duration they spent at office based on the respective IN & OUT timings. 

Here in column D2 if we straightforward put the formula as C2-B2, it will return the time duration in the form of fractional parts of decimal numbers.

So to get the time difference in hours here we have used the below formula in cell D2 and then dragged the same for the following cells which will also auto-update the cell references for the ‘OUT Time’ & ‘IN Time’ columns of the respective rows in the formula.

=(C2-B2)*24

In this process, if the IN Time is greater than OUT Time, then the result will be in negative as follows.

If you don’t bother about which time is greater and you only need to get the time difference in hours then you can use the ABS function as follows. This will convert any negative value into positive.

=ABS((C2-B2)*24)

The above formula we entered in cell D2 and then dragged the same for the following cells which will also auto-update the cell references for the ‘OUT Time’ & ‘IN Time’ columns of the respective rows in the formula.

Remember, in this process the time difference we are getting is in decimal value. This process will also work if only time format is provided as source in ‘IN Time’ and ‘OUT time’ columns or for all kinds of date format/time format/date time format.

Leave a Reply

Your email address will not be published. Required fields are marked *