Functions for Converting Time in Excel
By Michele Jensen
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.
References
Writer Bio
Michele Jensen started writing professionally for businesses in 1999. Her writings include articles for eHow, Answerbag and COD, marketing materials and project-related documentation. She received her Bachelor of Science degree in electrical engineering from the University of Houston and a Master of Science degree in international relations from Troy State University.