Calculate Hours Worked - Day or Night Shift & Breaks in Excel

Single simple formula to calculate the hours worked for a day shift or night shift and including lunch and all breaks in the calculation.

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.

(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:

Magic Formula to Calculate Hours Worked

Simple Hours Worked

Day Shift Hours Worked with Breaks and Lunch

Night Shift Hours Worked with Breaks and Lunch

Notes

Magic Formula to Calculate Hours Worked

=MOD(Time_Out - Time_In,1)*24

Time_Out is when they stopped work for whatever reason.

Time_In is when they started work.

*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.

This simple formula is the building block for the rest of the tutorial and works for day and night shifts alike.

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 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.

Everything in this tutorial will be an extension of this formula, basically just adding it again for each IN/OUT section.

Let's start with a simple example in the next section and work our way up to the full example.

Simple Hours Worked

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. 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. 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.

=MOD(C2-B2,1)

C2 is the time work stoped. OUT

B2 is the time work started. IN

This returns a time like this:

Get Hours from the Time

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.

=MOD(C2-B2,1)*24

*24 was added to the end of the formula, which multiplies the time by 24. This effectively converts the time into a decimal form.

Now:

8 hours and 30 minutes becomes 8.5

8 hours and 45 minutes becomes 8.75

Etc.

Result:

Problem: Weird Format for the Time

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.

If the format is still set to a time or date format, it will not display correctly.

Hint: Ctrl + Shift + ~ will quickly change all selected cells to the General format.

Wage Calculation

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.

Day Shift Hours Worked with Breaks and Lunch

We use one MOD function for each IN/OUT segment and subtract the breaks from the total time worked.

=(MOD(G12-B12,1)-MOD(D12-C12,1)-MOD(F12-E12,1))*24

MOD(G12-B12,1) calculates the total time that was worked, using the first time IN and last time OUT.

-MOD(D12-C12,1) calculates the time of the first break. 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. 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.

*24 this is put on at the end in order to convert the time format into an hour decimal format that is easy to view and use in calculations such as how much to pay someone. It converts something from 8:30 into 8.5 or 8:45 into 8.75, etc.

() remember to enclose all of the MOD functions together within a set of parentheses before multiplying by 24 or the result will be incorrect.

Result:

This is the basic formula.

Add or remove as many MOD() chunks as you need in order to account for all of the breaks that someone can take during the day.

Funky Formatting

If the result doesn't look right, make sure to set the result cell's formatting to General. You can do this from the Home tab or use the keyboard shortcut Ctrl + Shift + ~.

Night Shift Hours Worked with Breaks and Lunch

This heavenly formula is exactly the same as the one for the day shift! 

We use one MOD function for each IN/OUT segment and subtract the breaks from the total time worked.

=(MOD(G13-B13,1)-MOD(D13-C13,1)-MOD(F13-E13,1))*24

Cell references have been updated, from the day formula; in Excel I literally just copied the formula down one cell, so the only change was the automatically updating relative cell references.

MOD(G13-B13,1) calculates the total time that was worked, using the first time IN and last time OUT.

-MOD(D13-C13,1) calculates the time of the first break. Notice the minus sign in front of this MOD; that is because we are subtracting this break from the total time worked.

-MOD(F13-E13,1) calculates the time of the second break. Notice the minus sign in front of this MOD; that is because we are subtracting this break from the total time worked.

*24 this is put on at the end in order to convert the time format into an hour decimal format that is easy to view and use in calculations such as how much to pay someone. It converts something from 8:30 into 8.5 or 8:45 into 8.75, etc.

() remember to enclose all of the MOD functions together within a set of parentheses before multiplying by 24 or the result will be incorrect.

Result:

This is the basic formula.

Add or remove as many MOD() chunks as you need in order to account for all of the breaks that someone can take.

Funky Formatting

If the result doesn't look right, make sure to set the result cell's formatting to General. You can do this from the Home tab or use the keyboard shortcut Ctrl + Shift + ~.

Notes

There are many different ways to calculate time and hours worked in Excel, but, every single way, when used in the real world, is going to be more complicated and confusing than using the MOD function method exhibited in this tutorial. With the MOD function, everything is simple and logical and easy-to-follow. As such, I didn't spend time showing you other methods because, in reality, you shouldn't use any other method for the vast majority of situations.

Use the MOD function, keep the code modular, build on it as needed with required logic, and it will all work out, just give it time!

Download the workbook for this tutorial to view these examples in Excel.