Conversion From Minutes & Seconds to a Decimal in Excel (2024)

Most numbers used in business are based on 10 – commonly known as the decimal system. One dollar is $1.00 and a half-dollar is $0.50.

Time, of course, is an exception because it's based on 12. The day and the night are each 12 hours, while hours and minutes are divided into 60 (or 5 x 12). While we can all calculate times quite easily, converting the 12-based system into the 10-based decimal system can often be a chore.

Fortunately, Microsoft Excel can convert them very easily for you. So if you need to show that time is money, using Excel to run your calculations will probably save you a bit of both.

How toConvert Time to Decimal Format

Converting time to decimal format just involves basic division. There are 24 hours in a day, 60 minutes in an hour and 60 seconds in a minute.

Converting Seconds to Decimal

If your time is in cell A1, you can convert it to a decimal of another time measurement by dividing A1 in another cell. For example:

=A1/60 converts seconds into a decimal fraction of one minute

=A1/3600 converts seconds into a decimal fraction of one hour

=A1/86400 converts seconds into a decimal fraction of one day

Converting Minutes to Decimal

If you are converting minutes to decimals, your formula would be one of these:

=A1/60 converts minutes into a decimal fraction of one hour

=A1/1440 converts seconds into a decimal fraction of one day

How toUse Custom Time Formats in Excel

Excel has several custom formatting options for duration of time, and it will recognize most times you enter, provided you use a colon between the hours, minutes and seconds.

For example, if you simply type "1:30" into Excel, the software will assume you mean one hour and 30 minutes. If you enter 1:30:50 to add an extra 50 seconds, Excel will understand this too, although it will truncate the number to 1:30 in the cell. However if you want to enter 24 hours or any duration larger than that, you will need to select a custom duration format, such as [h]:mm or [h]:mm:ss.

To use a custom time format, highlight and right-click the cells and select "Format Cells." Under the Number tab, select "Custom" and then scroll down to find a suitable time format.

If you are using Excel as a time calculator, you can manipulate these numbers just as you would any other number system. Subtracting 00:30 from 1:00, for example, will give you 00:30.

How toConvert Custom Time Formats to Decimals

If you or an employee used custom time formats in Excel, you can convert them to decimals simply by changing the format. Highlight the cells, right-click them and then select "Format Cells." Under the Number tab, select "Number."

Alternatively, if you click the Home tab, you should see "Custom" in the Number menu of the ribbon. Click this, and select "Number." Either way you do it, the time automatically changes to a decimal number.

For example 0:30:15 – which is zero hours, 30 minutes and 15 seconds – will become 0.0210069444444444, which is a decimal fraction of a day, or 0.02 if you use the default setting of two decimals.

If the time duration was 22:10:00, this becomes 0.923611111111111, or 0.92, which is a larger decimal fraction of a day.

To change the number from a fraction of a day to a fraction of an hour, multiply this cell by 24 in an adjacent cell. Thus, 0.92 of a day becomes 0.50 of an hour by using the formula =A1*24.

Tip

Even if you have the Number format limited to two decimals in Excel, the program remembers a full 15 decimals and will use the fuller number when performing calculations.

References

Tips