This guide will discuss how to convert time to integer hours or minutes in Excel using the INT function.
Instead of multiplying the hours or minutes to get the decimal and then rounding it off to get the integer, we can simply use the INT function to calculate the integer hours or minutes immediately.
Excel has a time system that makes one day or 24 hours equal to 1. In other words, time in Excel has fractional values. For instance, 2:00 AM would be 2 hours. And in the Excel time system, this would be a value of 2/24 or 0.08333.
So this allows us to convert the time in Excel to decimals. What if we want to convert it to integers instead? Of course, we can just round up the decimals of the time to get the integers. But, this will take too much time if we handle a large dataset.
Instead, we can use the INT function in Excel to convert time to integers hours or minutes immediately. This will save us time and make working with a large dataset easier.
Let’s take an example.
Suppose you are in charge of creating the invoice for this pay period. And you will need to multiply the salary rate by the number of hours the employees have worked. But, manually converting each employee’s working hours and multiplying them takes too much effort.
So you used the INT function to get each employee’s integer working hours and minutes immediately. Then, simply multiply them by the salary rate. And tada! You have finished your task.
Awesome! Let’s move on and learn how to write the INT function in Excel to properly use it in converting time to integer hours and minutes.
The Anatomy of the INT Function
The syntax or the way we write the INT function is as follows:
=INT(number)
Let’s dissect this formula and try to understand what each term means:
- = the equal sign is just how we start a function in Excel.
- =INT() this is our
INTfunction. This function will return the integer of the selected data. It does this by rounding the number down to its nearest integer. - number refers to the real number you want to round down. In this case, this is the time we want to convert to integer hours or minutes.
We have learned the syntax of the function and also what the argument is for the function. Now let’s move on to an example of converting time to integer hours or minutes in Excel.
A Real Example of Converting Time to Integer Hours or Minutes in Excel
First, let’s see what our dataset containing the time we will be converting to integers looks like:
Let’s try converting the time to integer hours first. The formula for calculating the number of hours would be time multiplied by 24. Since there are 24 hours in a day, we will always multiply by 24 when calculating the hours of a time.
Based on that formula, our INT function argument will be the selected cell containing the time and multiplied by 24.
Now that we have seen the converted time to integer hours, let’s try converting the time to integer minutes. Similarly, the formula for this would be time multiplied by 1440. Since there are 60 minutes in an hour and 24 hours in a day, 60 multiplied by 24 is 1440.
Then, our INT function argument when converting time to integer minutes would be the selected cell multiplied by 1440.
Additionally, we can also convert time to integer seconds. The formula for this would be time multiplied by 86400, which is the number of seconds in a day. In this case, our INT function argument would be the selected cell multiplied by 86400.
You can make your own copy of the spreadsheet above using the link attached below.
Finally, it’s time to learn how to convert time to integer hours or minutes in Excel.
How to Convert Time to Integer Hours or Minutes in Excel
This section will explain the step-by-step process of converting time to integer hours or minutes using the INT function in Excel.
1. First, create a column where we will input the integer hours and another one for the integer minutes.
2. Second, let’s first convert the integer hours. Activate the INT function by inputting the = equal sign in B3. Then, type in the formula ‘INT(A3*24)’ or simply select the cell containing the time you want to convert, then multiply it by 24. Then, press the Enter key to return the integer hours.
3. Next, copy or drag down the formula to the entire column to return the data’s integer hours.
4. Then, let’s convert the time to the integer minutes. Similarly, input the formula ‘INT(A3*1440’ in C3 or select the cell containing the time you want to convert and multiply by 1440. Lastly, press the Enter key to return the result.
5. Again, copy or drag down the formula to the rest of the rows to get all the integer minutes of the time.
6. Additionally, let’s try converting one data into integer seconds. So input the formula ‘INT(A3*86400)’ in D3 or simply select any cell containing the number you want to convert into integer seconds. Finally, press the Enter key to get the result.
7. If your integer hours and minutes have decimal places, we just need to change the number format to make them appear as whole numbers.
First, select the entire Integer Hours and Integer Minutes columns. Then, press Crtl + 1 to open the Number Format window.
8. Next, select Numbers. Then, change the Decimal places by clicking the dropdown arrow and selecting 0. Lastly, click OK.
9. And tada! We have successfully converted the time to integer hours and minutes using the INT function.
That’s pretty much it! Finally, you have learned how to convert time to integer hours, minutes, and even seconds in Excel. Now you can apply this to your own work whenever you are dealing with time.
Are you interested in learning more about what Excel can do? You can now use the INT function in Excel and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
