Convert Hours and Minutes To Hundredths in Excel [Explained]

Imagine you being an HR manager. Your duty is to process the weekly timecards of your employees for payroll purposes. But you ran into a situation where the employees have recorded their work hours in minutes.

So what to do now? Because you need to convert these minutes to hundredths of an hour, which is the standard format used in payroll systems.

Keep Reading, As I’ve got you covered. This step-by-step guide will help you get rid of a situation like this one.

So, Let’s get started.

Is there a Simple Formula to Convert Time in Hundredths to Minute?

Yes, there is a simple formula to convert time in hundredths to minutes. You can multiply the value containing the time in hundredths of an hour by 60. For example: =A1*60. Here, A1 is the cell containing a value in hundredths.

Let’s say you have a value of 0.75 in a cell which refers to the time in hundredths. By multiplying the value with 60, the formula would return 45, which represents 45 minutes.

At a glance, look at the table to clear out your confusion regarding conversions.

Now, let’s move on to the simplest ways for minutes to hundredths conversion.

How to Convert Minutes to Hundredths in Excel

You might have seen several companies using tenths or hundredths of minutes to calculate wages. Do you know why they do this?

This is because recording time in hundredths of an hour allows for a higher level of precision in tracking and calculating the amount of time worked. Not only that, payroll systems can easily calculate the number of hours worked without having to convert between different units of time.

The use of hundredths of an hour is a common industry standard in many countries, making it easier for companies to align with industry practices and comply with local regulations.

So, without any further ado, let’s get to know the conversion process.

Here are the methods to convert minutes to hundredth in Excel:

1. Multiply the Cell Value By 24

If your dataset is in hh:mm format like 9:45 then you need to multiply it by 24 to convert minutes to hundredths. But, there is one condition. The active cell needs to be in General or Number Format.

Press Ctrl+1 to open the Format Cells window or you can go to Home Tab > General. Click on the dialog box expander to open all the formatting options.

Now that you have pre-formatted the cells, it’s time to execute the formula. Use the equals sign (=) and select the cell that you want Excel to convert. Use the asterisk symbol (*) and multiply the cell by 24. Press Enter. After that, use the Autofill option to drag the formula to the desired range.

Done! You now converted minutes to a hundredth of an hour. If you want more alternatives, I can walk you around with two more. Follow the next section if you failed to perform this method as specified.

2. Use the SUBSTITUTE and TEXT Functions

Here, I am going to use a combination of two functions (SUBSTITUTE and TEXT). I am using the SUBSTITUTE function to replace specific characters within a text string with another character or set of characters. For Example: Replacing a comma(,) with a colon(:).

The formula I am using is:

This formula is used to convert a decimal number in cell D4 to time format. Here’s how it works:

D4 * 24: You are multiplying the decimal number in cell D4 by 24 to convert it to hours.

TEXT(D4*24,”00.00″): The TEXT function formats the result of step 1 as a string with two decimal places, and with leading zeros, resulting in a string such as 12.34.

SUBSTITUTE(TEXT(D4*24,”00.00″),”.”,”:”): The SUBSTITUTE function replaces the decimal point in the string from step 2 with a colon, resulting in a string such as 12:34. Excel interprets this string as a time value.

So, the overall effect of the formula is to convert a decimal number in cell D4 to a time value, with hours and minutes separated by a colon.

3. Apply the Combination of TEXT and FLOOR Functions

Suppose you have a list of times recorded in minutes in a single column in an Excel spreadsheet. You want to convert these times to hundredths of an hour, which is the format commonly used in payroll or time tracking systems.

The formula I will be using here is:

I am using this formula to convert a time value in cell D4 to a custom format of hh:mm. Here’s how it works:

TEXT(D4,”[hh]”): The TEXT function formats the time value in cell D4 as a string, displaying only the hours part of the time. The format string [hh] tells the TEXT function to display the hours with leading zeros if needed.

MINUTE(D4): Next is the MINUTE Function. It returns the minute part of the time value in cell D4.

MINUTE(D4)*100/60: This expression calculates the minute part of the time value in cell D4 as a fraction of an hour, with two decimal places. For example, if cell D4 contains the time value 1:15 PM, then MINUTE(D4) would return 15 and MINUTE(D4)*100/60 would return 25.

FLOOR(MINUTE(D4)*100/60,1): The FLOOR function rounds down the result of MINUTE(D4)*100/60 to the nearest integer. The second argument 1 tells the FLOOR function to round down to the nearest multiple of 1.

& “:” &: The & operator is used to concatenate the result of TEXT(D4,”[hh]”) and FLOOR(MINUTE(D4)*100/60,1), separated by a colon.

So the overall effect of the formula is to convert the time value in cell D4 to a custom format of hh:mm.

Final Words

In conclusion, only Method 1 requires pre-formatting, whereas the format of other alternative methods generates automatically. Now, you can easily generate payrolls and ensure that your employees are compensated accurately for the time they work.