Convert Time to Decimal in Excel – Top 3 Methods

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 using 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.

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

STEP 3: Enter the addition symbol (+).

STEP 4: Enter the minute function.

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

STEP 6: Enter the addition symbol (+).

STEP 7: Enter the second function.

STEP 8: Select the cell containing the time and divide the time by 3600 to convert the second component to an hour.

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

 

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.

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

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

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

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

 

FAQs

1. Why does Excel store time as a decimal?

Excel stores time as a fraction of one day.

2. What is the easiest way to convert time to decimal hours?

Multiply the time value by 24.

3. Can I use functions to convert time to a decimal?

Yes. Use the HOUR, MINUTE, and SECOND functions together.

4. Does the CONVERT function work in all Excel versions?

No. It is available in Excel 365.

5. Why is my result still showing as time?

Change the cell format from Time to Number.