Functions for Converting Time in Excel

By Michele Jensen

Hours and minutes
i digital clock image by JoLin from <a href='http://www.fotolia.com'>Fotolia.com</a>

Microsoft Excel provides many useful functions for converting data. For times, it is crucial they be converted to the same units in order to perform calculations on them. For example, hours cannot be subtracted from minutes and a text representation of a time cannot be subtracted for a decimal representation of time.

Convert Between Units

To convert between two types of time units such as years to days, use the function CONVERT(number, from, to). Use "yr" for year, "day" for day, "hr" for hour, "mn" for minutes and "sec" for seconds. Number can reference a spreadsheet cell.

Convert Time to Decimals

To convert time from the standard hour: minute format, use the function INT(number). INT returns the number rounded down to the nearest integer. To use INT to convert time, subtract INT(time) from the time and multiply by 24, because there are 24 hours in a day. The calculation returns the number of hours in decimal since 12:00 a.m. Time can reference a spreadsheet cell.

Convert Decimals to Time

To convert decimals to time in the standard hour: minute format, use the function TEXT(value, format). The value needs to be divided by 24, since there are 24 hours in day. Use "h:mm" as the format. The calculation returns the time since 12:00 a.m. The value can reference a spreadsheet cell.

Convert Time to Serial Number

To convert text to time represented as a serial number, or decimal fraction of a day, use the functions TIME(hours, minutes, seconds) or TIMEVALUE(text). For example, TIME(18, 0, 0) returns 0.75 since 18:00, or 6:00 p.m., means three quarters of a day has elapsed. TIMEVALUE("19-Feb-2010 6:00 p.m.") also returns 0.75. The hours, minutes, seconds and text can reference spreadsheet cells.

Convert Serial Number to Time

To convert a serial number, or decimal fraction of a day, to hours, minutes and seconds use HOUR(serial number), MINUTE(serial number) and SECOND(serial number). Each formula returns only the portion of time requested. Serial numbers can also be a text string such as "6:30 p.m."). Excel will convert the string to a decimal fraction before performing the conversion. The serial number can reference spreadsheet cells.

×