Wednesday, 24 December 2008

Microsoft Excel: Overnight shift hours calculation

Microsoft Excel can be used for your work record to keep track of number of hours worked in each shift. It is useful for those people who don't work regular day jobs and have to work at odd hour and/or have to work irregular lengths of shifts. However, it is not always easy to calculate the hours of day shifts and over-night shifts using the same formula. Here is formula which will accurately calculate lenghth of the shift whether it is a day shift or over-night shift:

=IF(((B2-A2)*24)<0,(b2-a2)*24+24,(b2-a2)*24)

B2 and A2 are the addresses of two cells. Make sure that any cell replacing B2 contains the time of shift's end and one replacing A2 contains shift's start time.

Here are two examples in work:

Example 1:

Example with military style time

Example 2:

Example with AM/PM style time

The column with Hours heading contains the above mentioned formula.

2 comments:

Kay said...

New to being self-employed, I'm finding it distracting to track time spent working on a project, much less spend time developing a formula to do the arithmetic of adding it up. So I went to the Internet. After trying at least 7 different formulas (including excelforum)that don't require "military time," yours is the first one to work! Giving the result in number form is a big bonus. Thank you for taking the time to share your talents.

Anonymous said...

Yes, thanks very much!! This dows give a valid answer while many other formulas for overnight time calculations don't. Now if I can just figure out how this works...