WORK CLOCK HOUR COUNTER MOD
Notice the minus sign in front of this MOD that is because we are subtracting this break from the total time worked in the day. MOD(F12-E12,1) calculates the time of the second break. MOD(D12-C12,1) calculates the time of the first break. MOD(G12-B12,1) calculates the total time that was worked, using the first time IN and last time OUT. We use one MOD function for each IN/OUT segment and subtract the breaks from the total time worked. Day Shift Hours Worked with Breaks and Lunch Now that you have hours and decimals for the time someone worked, you can easily use this number to calculation wages or sum hours worked per week or month or year, etc. Hint: Ctrl + Shift + ~ will quickly change all selected cells to the General format. If the format is still set to a time or date format, it will not display correctly. If you get a weird time result when you multiply the time by 24, make sure to change the formatting of the cell to General. This effectively converts the time into a decimal form. *24 was added to the end of the formula, which multiplies the time by 24. The current format is still a time format and is not very useful for calculating how much to pay someone, among other considerations, so let's change the time to hours. This formula breaks-down very quickly in the real world though, so it won't be covered here however, I felt it was important to mention it in this note. With simple times, you can subtract the OUT time from the IN time to get the result, such as =B1-A1 where A1 has the IN time and B1 has the OUT time. Note: the easiest way to perform this calculation is also the least useful in the real-world and so I won't cover it beyond this next sentence.
WORK CLOCK HOUR COUNTER FULL
Let's start with a simple example in the next section and work our way up to the full example. This formula also lets us take breaks and lunch into account we simply create this formula for each break from work and then subtract that from the total time between the first IN and last OUT of the day.Įverything in this tutorial will be an extension of this formula, basically just adding it again for each IN/OUT section. Using the MOD function, we are able to seamlessly calculate the number of hours and minutes worked during a day shift, night shift, or over both without the hassel of unmanageably long formulas.
This simple formula is the building block for the rest of the tutorial and works for day and night shifts alike. *24 is what changes the time format into a decimal format that is easier to read and can be used in mathematical calculations, such as for wages.
Time_Out is when they stopped work for whatever reason. Notes Magic Formula to Calculate Hours Worked =MOD(Time_Out - Time_In,1)*24
Night Shift Hours Worked with Breaks and Lunch (Some times in this tutorial are presented using the 24 hour clock, or military time, but that doesn't change anything in regard to the formulas or their outcomes.) Sections:ĭay Shift Hours Worked with Breaks and Lunch This tutorial will show you the simple formula that you can use for this and tell you how you can customize it to work for your situation, where you might have more breaks or fewer breaks for which to account. Single simple formula to calculate the hours worked for a day shift or night shift and including lunch and all breaks in the calculation.
0 kommentar(er)
