How to Convert Minutes to Hundredths in Excel (3 Easy Ways)

In this article, we demonstrate how to use arithmetic operators and formulas to convert minutes to hundredths in Excel.

Say we have the dataset below containing the working hours of an employee. Let’s convert the minutes into hundredths.

Method 1 – Multiplying by 24

Multiplying hh:mm by 24 converts minutes to hundredths. However, the cells need to be in General or Number format. To pre-format the cells as General or Number, click the Number Setting icon (Home > Number section) or press CTRL+1 to display the Format Cells window.

Steps:

Enter the following formula in any adjacent cell:

=E6*24

E6 contains time in hh:mm format. Multiplying it by 24 converts the minutes to hundredths.

Drag the Fill Handle down to convert the other cells.

Method 2 – Using SUBSTITUTE and TEXT Functions

We can obtain the outcome of Method 1 with custom cell formatting, without the need to pre-format the cells.

Steps:

Enter the below formula into any blank cell:

=SUBSTITUTE(TEXT(E6*24,"00.00"),".",":")

Formula Breakdown

The TEXT function displays the (E6*24) value in (“00“) format_text.
Then the SUBSTITUTE function replaces the (“.“) old_text with (“:“) new_text.

Use the Fill Handle to convert the other minutes to hundredths.

Method 3 – Using TEXT and FLOOR Functions

As an alternative to Method 1, we can use the TEXT and MINUTE functions to obtain the hours and minutes respectively, then the FLOOR function to return the hundredths of minutes up to the assigned multiples.

Steps:

Enter the following formula into cell F6:

=TEXT(E6,"[hh]") & ":" & FLOOR(MINUTE(E6)*100/60,1)

Formula Breakdown

The TEXT function derives the hours.
The MINUTE function derives the minutes from cell E6, then converts the minutes to hundredths by multiplying them by 100/60.
The FLOOR function takes MINUTE(E6)*100/60 as its Number. 1 is assigned as Significance, which is the multipleused to round the resultant value.
The Ampersand (&) concatenates the hour and minute portions with a “:” between them.