To calculate hours worked overnight in Excel,
you have to do a little extra math,
but first, the right time formatting matters.
Your start time and end time need to be formatted
using a time format
with an AM or PM designation.
The easiest way to do this is to type the time
and follow it with AM or PM
Any lunch breaks or rest periods need to be formatted
without the AM or PM designation.
You can set this by selecting the cells
that you want to format,
right-clicking, and in Format Cells,
go to The time category and select any of the options
without a PM designation.
And then when you type it,
type it as you would a time format.
So say you want to take a 30-minute break,
you would type 0:30.
Now this could be a bit confusing,
because if you go back to the cell
and you look up here, it shows an AM designation.
However, with the format that we selected,
it will calculate correctly.
So then all you would normally do is subtract the breaks
and the start time from your end time,
but as you can see,
this gets tricky when you work overnight.
The reason for this is that Excel sees time
as a decimal between zero and 0.99.
Each number representing a fraction of a 24-hour period,
and you can see this if I format this
to a General format.
So the trouble with overnight
is that since the decimal number for the AM
is less than that of the PM time,
when you subtract one from the other,
you end up with a negative number.
And this negative number
is outside the zero to 0.99 spectrum
that makes up what Excel sees as time.
We can work around this by using the MOD function.
So MOD stands for modulus, which is a mathematical way
of calculating the difference between your number
and a divisor.
I'll add a link in the notes
if you're interested in learning more
about the math behind it,
but here, we can use MOD by using 1 as the divisor.
And essentially, what it'll do
is help us calculate the difference
between the negative result and 1.
So let's delete what's here,
and we'll type =MOD,
parentheses and a second parentheses for our calculation,
and we'll do the end time minus the start time.
Close the parentheses and we'll use 1 as the divisor,
and that gives us 0.33.
Now don't forget, we still need to take out any breaks,
and now that we have this calculation,
let's format this again as time,
and we see that between 6:00 PM and 2:00 AM,
minus a half-hour break, we worked seven and a half hours.
So the reason why you want to use MOD
if you're tracking night shift hours
is that you can also use this formula
to calculate any day shift hours worked
without affecting the result,
whereas if you just were to subtract one
to get the inverse,
you'd have to remember to update your formula
for each instance.
Notice if I were to copy this formula
to these other line items,
the result would be the same.
So this gives us total hours, but in time format.
Remember that the seven and a half hours is actually 0.3125.
So to convert this to a number
and help us calculate our total pay,
we need to multiply this by 24.
So all we do is take the total hours,
which is, again, a fraction of a 24-hour day,
and multiply that by 24,
and we don't want this formatted as time,
so that gives us seven and a half hours.
We'll plug in our hourly fee as a dollar sign.
And then our total pay will be the hourly fee
times the total hours times 24.
For more tutorials like this,
click on these links. Thanks!