The TIME Function in Excel

by Gregory on August 19, 2010

I’ve been looking at converting a number of seconds into a minutes:seconds format. My example is converting 274 seconds by using the INT Function to extract 4 minutes and the MOD Function to extract 34 seconds.

In this post I’m using those values inside the TIME Function.

TIME Function

The Time Function has three arguments: Hour, Minute, Second.

TIME Function Dialog Box

This particular spreadsheet contains song duration in seconds so the Hour argument will always be zero.

The Minute and Second arguments come from the INT and MOD columns, respectively. The INT Function was used to extract the minutes and the MOD Function was used to extract the seconds.

The result of the calculation is 0.003171296, which is the time serial number and is displayed in the cell as 4:34 because the cell formatting is set to custom type m:ss.

Format Cells Dialog Box-Custom Time

In my next post I’ll insert the INT and MOD Functions in the TIME Function.

Related Posts Plugin for WordPress, Blogger...
The Wise July 27, 2012 at 11:23 am

Or you could have just done =Time(0,0,A2) and thus avoiding doing the work of the Time function for it…

Or you could directly divide the seconds by 86400 (number of seconds in a day) to get the time serial number.

Gregory July 27, 2012 at 11:43 am

You are perfectly correct. Using the Time Function is great if the number of seconds is less than 32767. And dividing by 85,400 is even better, if you explain how to change the cell formatting to see a hours:minutes format. Both methods are covered in other post I have written elsewhere. This post was one of a series that logically followed the INT and MOD functions to parse time elements.

I find that it helps to keep my subjects small to keep my readers interested and focused.

Comments on this entry are closed.

{ 2 trackbacks }

Previous post:

Next post: