How to Convert Time to Decimal in Excel Using Formulas

When working in Excel, you often need to import data from external sources where time is recorded as a decimal number. For example, one hour and 30 minutes (1:30) is written as the number 1.5. At the end of the article you can download a time to decimal fraction converter.



Converting Decimal to Minutes and Hours in Excel

The display format of time in a decimal can be quickly and easily changed to a standard time format in two steps:

  1. Divide the original decimal number by 24.
  2. Divide decimal numbers by 24.
  3. Change the cell format to "Time."
  4. Time cell format.

As a result, decimal time numbers are replaced with the natural time format without errors.

Dividing any number of hours by 24 allows Excel to treat the resulting value as time.

To convert the number of minutes recorded in decimal form, you need to divide the original number by 1440 — the exact number of minutes in a day (24 hours, 24*60=1440). For example, if 64.51 minutes are recorded, you will get 1 hour, 4 minutes, and 31 seconds (1:04:31):

convert number of minutes.

To convert the number of seconds recorded as a decimal into the time format, divide the original value by 86,400 — the exact number of seconds in a day (60*60*24=86400). The following formula returns the decimal seconds in the "Time" format 00:06:30:

number of seconds.

That is, 390.55 seconds equals 6 minutes and 31 seconds. Verification: 6*60=360; then: 390.55-360=30.55; rounding to whole seconds gives 31 seconds.

For reverse conversion, other arithmetic formulas and constants should be used in the arguments.

Formulas for Converting Time to Decimal in Excel

time to decimal converter

Download Time to Decimal Converter in Excel download file

  1. Hours to decimal.
  2. =A3+B3/60+C3/3600
  3. Minutes to decimal.
  4. =A3*60+B3+C3/60
  5. Seconds to decimal.
  6. =A3*3600+B3*60+C3

Thus, in Excel, you can create simple time calculator templates and converters to decimal or back.