Excel's time format isn't always suitable for calculations, so converting time to decimal numbers—such as hours, minutes, or seconds—is often necessary. There are several methods to transform time values into decimals in Excel, including arithmetic operations, the CONVERT function, or combining the HOUR, MINUTE, and SECOND functions. This guide explores these methods to help you convert time to decimal numbers effectively.

Converting Time to Decimal Numbers in Excel

For example, if you have a time value like 5:40:22 PM, you might want to extract:

  • Number of hours: 5
  • Number of minutes: 40
  • Number of seconds: 22

You can achieve this using any of the following methods to convert time to hours, minutes, or seconds.

Convert Time to Decimal Number Using Arithmetic Operations

One straightforward way to convert time to decimal numbers is by using arithmetic calculations. By multiplying the time value by the total number of hours, minutes, or seconds in a day, you can extract the desired unit.

First, it's important to know the total number of hours, minutes, and seconds in a day:

  • 24 hours in a day
  • 60 minutes in an hour
  • 1,440 minutes in a day (24 × 60)
  • 60 seconds in a minute
  • 86,400 seconds in a day (24 × 60 × 60)

When you enter 12:00 in Excel, it recognizes it as a time value and stores it as 0.5 when formatted as a number, since it's half of a 24-hour day.

Convert Time to Hours in Excel

To convert a time value to hours, multiply it by 24 (the number of hours in a day). For instance, if you have 12:00 PM in cell A2 and want to convert it to hours, use the following formula:

=A2*24

Initially, you may see 12:00 AM as the result because Excel formats the cell as time by default. To display the result as a decimal number, change the cell format to 'General' or 'Number' under the 'Home' tab in the Number group.

This adjustment will display the number of hours as a decimal value. For example, 2:30 PM multiplied by 24 will give you 14.5, indicating 14 full hours and 30 minutes as half an hour.

If you prefer to obtain only the integer value of hours without the fractional part, you can use the INT function as follows:

=INT(A2*24)

Convert Time to Minutes in Excel

To convert time to minutes, multiply the time value by 1,440 (the number of minutes in a day). If you have 4:45 AM in cell A3, use the following formula:

=A3*1440

If you can't recall the number of minutes in a day, you can multiply by 24*60 instead:

=A3*24*60

This will give you the total number of minutes that have passed since midnight for the given time. In this example, 285 minutes.

Convert Time to Seconds in Excel

Similarly, to convert time to seconds, multiply the time value by 86,400 (the number of seconds in a day). For example, if you have 5:50:10 AM in cell A3, use this formula:

=A3*86400

Or, equivalently:

=A3*24*60*60

The result will show the total number of seconds since midnight for the given time, which is 21010 seconds in this example.

Convert Time to Decimal Number Using the CONVERT Function

The CONVERT function is another useful method for transforming time values into decimal numbers. This function converts a number from one unit of measurement to another.

The syntax of the CONVERT function is:

=CONVERT(number, from_unit, to_unit)

Where:

  • number – The value to convert
  • from_unit – The unit of the original number
  • to_unit – The unit to convert the number to

For time conversions, you'll primarily use the following units:

  • "day" – Days
  • "hr" – Hours
  • "mn" – Minutes
  • "sec" – Seconds

Convert Time to Hours in Excel

If you have a time value in cell B2 and want to convert it to hours using the CONVERT function, you can use the following formula:

=CONVERT(B2, "day", "hr")

Here, the function converts the time from days to hours.

If you want to obtain only the whole hours without fractions, wrap the formula with the INT function:

=INT(CONVERT(B2, "day", "hr"))

Convert Time to Minutes in Excel

To convert time to minutes, adjust the units in the CONVERT function accordingly:

=CONVERT(B2, "day", "mn")

Convert Time to Seconds in Excel

Similarly, to convert time to seconds, use:

=CONVERT(B2, "day", "sec")

Convert Time to Decimal Number Using Excel Time Functions

Another method involves using Excel's time functions HOUR, MINUTE, and SECOND to extract each component of the time value.

Convert Time to Hours Using Time Functions

You can convert time to hours by extracting each time component and converting them all to hours. Use the following formula:

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

Convert Time to Minutes Using Time Functions

To convert time to minutes, convert hours to minutes and seconds to minutes, then sum them up:

=HOUR(B2)*60 + MINUTE(B2) + SECOND(B2)/60

Convert Time to Seconds Using Time Functions

Similarly, to convert time to seconds, convert hours and minutes to seconds and add them together:

=HOUR(B2)*3600 + MINUTE(B2)*60 + SECOND(B2)

Converting time to decimal numbers in Excel can be accomplished using various methods. Whether you prefer arithmetic operations, the CONVERT function, or time functions, you can choose the method that best suits your needs.