Convert Time to Decimal in Excel – Top 3 Methods

In Excel, time is represented in the format HH:MM:SS, where HH represents hours, MM represents minutes, and SS represents seconds. But, you may need to convert time to decimal to perform complex calculations and analysis. This process is particularly useful in scenarios where you need to perform mathematical operations on time durations.

In this article, we will explore three methods mentioned to convert time to decimal in Excel –

Download the Excel Workbook below to follow along and understand How to Convert Time to Decimal in Excel – download excel workbookConvert-Time-to-Decimal.xlsx

 

Method 1 – Multiplication

Excel stores time as numbers and a 24-hour day is equal to 1. To convert time to a number of hours, multiply the time by 24.

 

Method 2 – HOUR, MINUTE and SECOND

One straightforward way to convert time to decimal in Excel is by utilizing the HOUR, MINUTE, and SECOND functions. These functions are used to extract the hour, minute, and second components from a given time, respectively.

Suppose you have time displayed in the hh:mm:ss format in column A and you want to convert it into decimal format.

The general syntax to convert time to decimal is –

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

• HOUR(serial_number) – This extracts the hour component from the time value.
• MINUTE(serial_number)/60 – This extracts the minute component from the time value, and divides it by 60(the number of minutes in an hour) to convert it to hours.
• SECOND(serial_number)/3600 – This extracts the second component from the time value and divides it by 3600(the number of seconds in an hour) to convert it to hours.

By combining these three parts, the entire formula calculates the decimal representation of a given time value. Follow the steps below to achieve this result –

STEP 1: Enter the hour function.

=HOUR

STEP 2: Select the cell containing the time. Here, it is A2.

=HOUR(A2)

STEP 3: Enter the addition symbol (+).

=HOUR(A2)+

STEP 4: Enter the minute function.

=HOUR(A2)+MINUTE

STEP 5: Select the cell containing the time and divide the time by 60 to convert the minute component to an hour.

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

STEP 6: Enter 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 containing the time and divide the time by 3600 to convert the second component to an hour.

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

The time from the hh:mm:ss format can be converted into decimal format using this method.

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

 

Method 3 – CONVERT

This function is exclusively available for Excel 365 users. It can be used to convert a number from one measurement system to another. The syntax is –

CONVERT(number,from_unit,to_unit)

• Number – It is the reference value.
• From_unit – It is the units of the original value.
• To_unit – It is the units for the result.

Follow the steps below to achieve this result –

STEP 1: Enter the CONVERT function.

=CONVERT

STEP 2: Enter the first argument i.e. number. Here, it is the cell containing time in hh:mm:ss format (A2).

=CONVERT(A2

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

=CONVERT(A2,”day”

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

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

The time value will easily be converted to decimal format using the CONVERT function.

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

Conclusion

In conclusion, while Excel represents time in the HH:MM:SS format, converting it to decimal becomes crucial for intricate calculations. This article explored three methods – employing the multiplication operator, utilizing HOUR, MINUTE, and SECOND functions, and leveraging the CONVERT function.