Convert Time to Decimal in Excel – Top 3 Methods

In this article, we will delve into three approaches for converting time to decimal format in Excel.

You can download the Excel Workbook below to follow along and learn how to Convert Time to Decimal in Excel – download excel workbookConvert-Time-to-Decimal.xlsx

Table of Contents

Approach 1 – Using Multiplication

In Excel, time is stored as numerical values where a full 24-hour day equates to 1. To transform time into a number of hours, multiply the time value by 24.

Approach 2 – Using HOUR, MINUTE, and SECOND

A straightforward method to convert time to decimal in Excel involves the use of HOUR, MINUTE, and SECOND functions. These functions help in extracting the hour, minute, and second parts from a specified time.

Assume you have time in the format hh:mm:ss in column A and aim to convert it to decimal format.

The general formula for converting time to decimal is as follows:

=HOUR(serial_number) MINUTE(serial_number)/60 SECOND(serial_number)/3600

• HOUR(serial_number) – Extracts the hour part from the time value.
• MINUTE(serial_number)/60 – Extracts the minute part from the time value and converts it to hours by dividing by 60.
• SECOND(serial_number)/3600 – Extracts the second part from the time value and converts it to hours by dividing by 3600.

Combining these components, the formula calculates the decimal equivalent of the time value. Follow these steps to apply this method:

STEP 1: Begin by entering the HOUR function.

=HOUR

STEP 2: Select the cell with the time value, for example, A2.

=HOUR(A2)

STEP 3: Add the addition symbol ( ).

=HOUR(A2)

STEP 4: Enter the MINUTE function.

=HOUR(A2) MINUTE

STEP 5: Select the cell with the time value and convert the minute part to hours by dividing by 60.

=HOUR(A2) MINUTE(A2)/60

STEP 6: Add the addition symbol ( ).

=HOUR(A2) MINUTE(A2)/60

STEP 7: Enter the SECOND function.

=HOUR(A2) MINUTE(A2)/60 SECOND

STEP 8: Select the cell with the time value and convert the second part to hours by dividing by 3600.

=HOUR(A2) MINUTE(A2)/60 SECOND(A2)/3600

This method allows you to convert time from the hh:mm:ss format to decimal format.

Click here to explore more about time-related functions in Excel.

Approach 3 – Using CONVERT

This function is specifically available for Excel 365 users. It facilitates conversion from one measurement system to another. The syntax is:

CONVERT(number,from_unit,to_unit)

• Number – Represents the value to convert.
• From_unit – Specifies the unit of the original value.
• To_unit – Specifies the unit for the result.

Follow these steps to use this method:

STEP 1: Start by entering the CONVERT function.

=CONVERT

STEP 2: Enter the first argument, which is the number. Here, it's the cell with the time in hh:mm:ss format (A2).

=CONVERT(A2

STEP 3: Enter the second argument, which is from_unit. Here, it is “day”.

=CONVERT(A2,”day”

STEP 4: Enter the third argument, which is to_unit. Here, it is “hr”.

=CONVERT(A2,”day”,”hr”)

The CONVERT function enables easy conversion of the time value to decimal format.

Click here to discover more about the CONVERT function in Excel.

Conclusion

In conclusion, although Excel displays time in the HH:MM:SS format, converting it to decimal format is essential for detailed calculations. This article has examined three methods – using multiplication, employing HOUR, MINUTE, and SECOND functions, and utilizing the CONVERT function.

The above is the detailed content of Convert Time to Decimal in Excel – Top 3 Methods. For more information, please follow other related articles on the PHP Chinese website!