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 hours 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 any shift which is not longer than 24 hours 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. Please be sure to change the format of this column to 2-place decimal number.
Posts
2 comments:
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.
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...
Post a Comment