The last several posts have shown how to convert and aggregate a given number of seconds that represent song duration. A simple example because song duration never exceeded one hour. But what if your data has values greater than 3600 seconds? How would you convert those seconds into an hour:minute:second format? Luckily, the previously mentioned functions:, still work. However, the arguments get a little more complicated.
![Milliseconds Milliseconds](/uploads/1/2/5/5/125592639/829849330.png)
![Seconds Seconds](/uploads/1/2/5/5/125592639/698999427.png)
To simplify things we’ll look individually at the three TIME Function arguments: Hours, Minutes, Seconds. Going so far as to create a formula for each, then combining into one big formula. A Short Review The INT Function returns the integer portion of a division and the MOD Function returns the remainder. If I divide 7 by 2, then INT(7/2) = 3 and MOD(7,2) = 1. Two goes into seven, three times, with one left over. Additionally, there are two conversion factors. 1 hour = 60 minutes/hour x 60 seconds/minute = 3600 seconds.
Convert Seconds to a Time Format in Excel The last several posts have shown how to convert and aggregate a given number of seconds that represent song duration. A simple example because song duration never exceeded one hour.
1 minute = 60 seconds/minute = 60 seconds The TIME Function Arguments In the following example, the value in cell A2 is 3661 seconds, which is 1 hour, 1 minute and 1 second. Hours Argument Getting the number of hours is easy, just use A2/3600 for the INT Function argument. Minutes Argument We need the remainder of A2/3600, which is MOD(A2,3600) = 61, but still have to convert these seconds to minutes. The answer is to divide MOD(A2,3600) by 60 and get the integer portion for the number of minutes.
In other words, 3600 goes into 3661 once, with 61 seconds left over. The MOD function gives us the 61 seconds. The INT function argument then reduces to INT(61/60), which gives us one (1), because 60 goes into 61 once. Seconds Argument MOD(A2/3600) gives us 61 seconds, so we need to divide by 60 and get the remaining seconds by using the MOD function again. Since the inside MOD function equals 61 seconds, MOD(61,60) gives the us one (1), because after you divide 61 by 60 there is one (1) second left. TIME Function Putting all of these together inside the TIME Function makes the formula rather large.
Yet knowing how the functions work in each argument makes things a little more understandable. Another TIME Function Example Since 1:01:01 was easy math, but lousy use of too many 1’s I’ll use the value in cell A3, 7510, as a second example. Hours: INT(7510/3600) = 2 hours. Remainder: MOD(7510, 3600) = 510 seconds.
Minutes: INT(510/60) = 5 minutes. Seconds: MOD(510,60) = 10 seconds. TIME(2,5,10) = 2:5:10 Notice the Remainder of the first division (7510/3600) is the central argument for Minutes and Seconds. Post author You can always convert your number to a time serial number and use Excel custom formatting to show the time format correctly. You just have to remember to use square brackets around any cumulative number you want to represent. As an example, lets say you have a number of seconds like 129600, which is more that one day.
To covert to a time serial number, just divide by 86400, which gives you 1.5. Then change the cell formatting to h:mm:ss and you will see 36:00:00 which is 36 hours. Another example. To show 46.5 in an hours and minutes format, just divide by 1440 minutes (the number of minutes in a day) and then change the cell formatting to the same custom format h:mm:ss and you will see 0:46:30 or you can use m:ss to leave off the hours and show just 46:30. So the key is to convert to a time serial number by dividing by the lowest common denominator (24 to show hours, 1440 to show minutes, or 86400 to show seconds) and then use a custom time format with square brackets that allow cumulative time in the formatting. A time serial number is a fractional number between zero and 1. Bob Burg Awesome!
Just what I needed. It converted 108 seconds to 41:59:03 perfectly after I divided by 86400 and set the custom time format for the cell. It also worked correctly for small numbers like 50 seconds and 130 seconds. Thanks for your help.
For most calculations, I plan to use this instead of the Excel ‘Time’ function to avoid potential unrecognized errors due to the 24-hour limitation of the “Time” function. Other than deciding correctly what to divide by, are there any limitations or caveats using this process?. Post author Select the cell with the 3:00 and use the keyboard combination Control+1 (or Command+1 on a Mac) to bring up the Format Cells dialog box. Click on the Number tab, and under Category, select Custom where you will see something in the type box like this h:mm;@ Change it to add square brackets around the h so that Excel will treat hours as a cumulative number, instead of one that repeats every 24 hours. Here’s what I used for your example.
h:mm:ss This will show the 27 hours (plus and minutes and seconds too).