Convert Time to Decimal - Hours, Minutes, Seconds

Easily convert time to decimal format so that 9:15 AM or 9:15 will become 9.25, which means 9 hours and .25 (25%) of an hour.

This format allows you to use the time to calculate wages, sum total time worked, and more, without any formatting confusion. This method changes the internal format of the number so that it will no longer be in the time-serial format in Excel - this just means that Excel won't see it as a date anymore.

Sections:

Convert time to Hours

Convert time to Minutes

Convert time to Seconds

Split total time worked into Hours, Minutes, and Seconds

Notes

Convert time to Hours

=A2*24

Result:

*24 represents the number of hours in the day. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24 to get the correct result.

Rounded

=INT(A2*24)

This returns full hours and removes any stray minutes from the end.

INT() rounds the number down to the nearest integer, so the decimal amount is simply removed.

Why Not to use HOUR()

The HOUR() function will return the hour number from a time, however, it only works for times that are under 24 hours. If you, for instance, store a time that has 27 for the hour, the HOUR() function will return 3 instead of 27.

You can test this by entering this number into a cell: 27:15:30 and formatting it like [h]:mm:ss and then using the HOUR() function to try to get the total hours. This example is also in the last section of this tutorial.

Formatting (Important)

Make sure the cell that outputs the new number is formatted as General. Home tab > Number > General or Right-click the cell > Format Cells > Number tab > General.

Often, the cell will have some sort of date format by default and won't output the correct value until you change the formatting; you may think that you did something wrong when, in fact, it's just the formatting of the cell.

Convert time to Minutes

=A5*24*60

Result:

*24*60 represents the number of minutes in the day; hours in a day * minutes in an hour. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24*60 to get the correct result.

You could also just multiply it by 1440 (total minutes in a day), but it is often easier to remember 24*60 since that is the number of hours in the day multiplied by the number of minutes in an hour.

Rounded

=INT(A5*24*60)

This returns full minutes and removes any stray seconds from the end.

INT() rounds the number down to the nearest integer and so the decimal amount is simply removed.

Formatting (Important)

Make sure the cell that outputs the new number is formatted as General. Home tab > Number > General or Right-click the cell > Format Cells > Number tab > General.

Often, the cell will have some sort of date format by default and won't output the correct value until you change the formatting; you may think that you did something wrong when, in fact, it's just the formatting of the cell.

Convert time to Seconds

=A8*24*60*60

Result:

*24*60*60 represents the number of seconds in a day; hours in a day * minutes in an hour * seconds in a minute. Since Excel stores time as a fraction of a day, this is why we can multiply it by 24*60*60 to get the correct result.

You could also just multiply it by 86400 (total seconds in a day), but it is often easier to remember 24*60*60 since that is just the number of hours in the day multiplied by the number of minutes in an hour multiplied by the number of seconds in a minute.

Formatting (Important)

Make sure the cell that outputs the new number is formatted as General. Home tab > Number > General or Right-click the cell > Format Cells > Number tab > General.

Often, the cell will have some sort of date format by default and won't output the correct value until you change the formatting; you may think that you did something wrong when, in fact, it's just the formatting of the cell.

Split total time worked into Hours, Minutes, and Seconds

This will split the time so that one cell each will contain the rounded number of hours, the remainder of the minutes, and the remainder of the seconds.

So 9:15:30 (in a time format) would be broken into 9 for the hours cell, 15 for the minutes cell, and 30 for the seconds cell.

Hours Rounded

=INT(A11*24)

This is the long format version for the hours. You cannot use the short format here: =HOUR(A11) in case your hour number exceeds 24. If it exceeds 24, the HOUR() function will reset; so, for instance, 27 hours becomes 3 hours.

Minutes Remainder

=MINUTE(A11)

Long Format

=INT(A11*24*60)-INT(A11*24)*60

Seconds Remainder

=SECOND(A11)

Long Format

=A11*24*60*60-INT(A11*24*60)*60

Hours Exceeding 24 Example

The first line of functions here uses the HOUR(), MINUTE(), and SECOND() functions and the second line uses the long format formulas. This exhibits the situation where the hours exceed 24 and the HOUR function won't work correctly.

Notes

Working with time can be a pain, but putting the time into a decimal format will allow you to perform mathematical calculations on the time without having to worry about the common issues associated with dates and times in Excel. That said, always make sure to keep the original version of the time somewhere since that can be useful for other types of calculations.

Download the attached workbook to get all of these examples in Excel.