How to Convert ‘1 Hour 30 Minutes’ into Decimal Format in Excel

スポンサーリンク
スポンサーリンク

How to Convert "1 Hour 30 Minutes" into Decimal Format?

This article explains how to convert time written as a string, such as "1 hour 30 minutes," into a decimal format like 1.5 hours in Excel. We’ll demonstrate how to split and calculate the string using formulas.

Example: Converting Time Strings to Decimal Format

If column A contains time strings like "1 hour 30 minutes," column B can display the decimal equivalent as follows:

A B
1 Time Time (Decimal Format)
2 1 hour 30 minutes =LEFT(A2,FIND("hour",A2)-1) + MID(A2,FIND("hour",A2)+5,FIND("minute",A2)-FIND("hour",A2)-5)/60
3 2 hours 15 minutes =LEFT(A3,FIND("hour",A3)-1) + MID(A3,FIND("hour",A3)+5,FIND("minute",A3)-FIND("hour",A3)-5)/60
4 3 hours 45 minutes =LEFT(A4,FIND("hour",A4)-1) + MID(A4,FIND("hour",A4)+5,FIND("minute",A4)-FIND("hour",A4)-5)/60

Explanation of the Formula

  • LEFT(A2,FIND("hour",A2)-1): Extracts the portion before the word "hour," which represents the hours (e.g., 1).
  • MID(A2,FIND("hour",A2)+5,FIND("minute",A2)-FIND("hour",A2)-5): Extracts the portion between "hour" and "minute," which represents the minutes (e.g., 30).
  • /60: Divides the minutes by 60 to convert them into a fractional hour.
  • +: Adds the hours and fractional hours to calculate the total time in decimal format.

Result

The string "1 hour 30 minutes" is converted into 1.5 hours, as shown below:

A B
1 Time Time (Decimal Format)
2 1 hour 30 minutes 1.5
3 2 hours 15 minutes 2.25
4 3 hours 45 minutes 3.75

Example: Converting Time Data to Decimal Format

If column A contains time in the hh:mm format, you can use a formula to convert it into decimal format in column B:

A B
1 Time (hh:mm) Time (Decimal Format)
2 1:30 =A2*24
3 2:45 =A3*24
4 3:15 =A4*24

Explanation of the Formula

  • A2 * 24: Excel internally represents time such that 1 day equals 1. Multiplying by 24 converts time values to decimal hours.
  • Formatting: To display the result with two decimal places, format the cell as "Number" with two decimal places.

Result

The following table shows the conversion of time data into decimal format:

A B
1 Time (hh:mm) Time (Decimal Format)
2 1:30 1.50
3 2:45 2.75
4 3:15 3.25

Summary

In Excel, you can easily convert time strings like "1 hour 30 minutes" into decimal format using formulas. For time data, simply multiply by 24 to get the decimal equivalent. This method helps streamline your workflow and handle data efficiently.