Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Time to Decimal

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.

Watch our free training video on How to Convert Time to Decimal in Excel:

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 WORKBOOK

 

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.

Time to Decimal

 

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.

See also  Jump To A Cell Reference Within An Excel Formula

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

Time to decimal

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

Time to decimal

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

=HOUR(A2)

Time to decimal

STEP 3: Enter the addition symbol (+).

=HOUR(A2)+

Time to decimal

STEP 4: Enter the minute function.

See also  Create a Named Range in Excel

=HOUR(A2)+MINUTE

Time to decimal

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

Time to decimal

STEP 6: Enter the addition symbol (+).

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

Time to decimal

STEP 7: Enter the second function.

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

Time to decimal

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

Time to decimal

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

Time to decimal

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 –

See also  10 + 1 Ways to Round Numbers in Excel

STEP 1: Enter the CONVERT function.

=CONVERT

Time to decimal

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

=CONVERT(A2

Time to decimal

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

=CONVERT(A2,”day”

Time to decimal

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

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

Time to decimal

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

Time to decimal

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

 

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.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  How to Calculate Ratio in Excel - 4 Useful Methods
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!