In todays tutorial we’ll be looking at How to Calculate Hours Worked in Excel (Midnight Span).
Calculate Hours Worked in Excel (Midnight Span) Video
How to Calculate Hours Worked in Excel (Midnight Span)
If you ever find yourself in a situation where you need to calculate working hours that span midnight, this video is for you!
Let’s get started.
In the last tutorial, we looked at how to calculate hours worked if the ‘Start Work’ and ‘End Work’ times are within the same day.
We also calculated working time for each day, from Monday to Friday.
However, this method won’t work if the working time spans midnight, like in the case of Friday.
On Friday, the employee started working at seven o’clock in the evening and finished at six o’clock in the morning.
Since the working time spanned midnight, we need to use the IF function to calculate the hours correctly.
Let’s do this together!
How to Use Excel’s IF Function to Calculate Hours Worked
First, we need to remove the misfired attempt and, as usual, we type the ‘equal’ sign in the cell that is supposed to show the result.
To start, type ‘I‘ and double-click on the IF option once you see it in the options.
You will then be prompted to enter the logical test.
Once you have entered the logical test, you will be prompted to enter the value for the true case and the false case.
Enter the corresponding values for each and Excel will return the result of the IF function.
In this tutorial, I’m going to show you how to use the IF function in Excel to calculate time.
I’ll provide step-by-step instructions to help you understand how the function works.
Additionally, I’ll be providing a video that goes into greater detail on the function and how to use it. I’ll leave a link in the description below.
Let’s get started!
Putting Excel’s IF Function Into Action
To begin, we will enter a logical test, or condition, in order to determine the value that Excel should use in the calculation.
After the word ‘if’, we will type out the condition, which is that the ‘Start Work’ time (the value in cell C7) is greater than the ‘End Work’ time (the value in cell D7).
Enter a comma
We will then indicate what value Excel should use if the condition is true – by clicking on the cell with the ‘End Work’ time data and adding “+1”, which means that this time belongs to the next day.
Enter a comma again and we will then define the value for the situation in which the condition is false – by clicking on cell D7.
Excel will then treat the time data from cell D7 as is.
So this is how the formula is currently working. If the value in C7 is greater than the value in D7, Excel will use the value from the next day in D7. Otherwise, it will use the value from the same day in D7 as is.
After that, we will close the brackets and subtract the ‘Start Work’ time by using the minus sign, clicking on cell C7 and pressing ‘Enter’.
Excel has now calculated the working hours that span midnight.
Confirm The Formula Works Correctly
To confirm that the formula works, we can drag the bottom-right corner of the cell up through all weekdays.
If the times calculated previously did not change, this means the formula works even in cases where the hours worked did not span midnight.
Are you having trouble adding up all the hours you’ve worked throughout the week?
Sheet Leveller has a tutorial that can help!
In this tutorial, you’ll learn how to sum time in Excel, as well as get solutions to some of the common issues that arise when calculating your total hours.
If you found this tutorial helpful, and if you want to learn Excel our complete guide section is the place to go that will help you use Excel like a boss!
Want to learn how functions work in excel then check out my excel functions page where you will learn many of the functions. Or looking for formulas? Well, we have you covered there too with our Excel Formulas page.
Pingback: How to Sum Time in Excel - Sheet Leveller