Here’s what I used for your example.This will show the 27 hours (plus and minutes and seconds too).

The easiest way to convert a time to a number of seconds is to simply multiply the time by 86400, which is equal to 24*60*60 (the number of seconds in one day): How can I convert/calc this to show 27 hours?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.

It also worked correctly for small numbers like 50 seconds and 130 seconds. Formatting the Date Result. Convert milliseconds to hh:mm:ss time with formula. Excel does an excellent job of dealing with a “serial time” value.One limitation seems to be that it works up to 255611462399 seconds, i.e. up to 31 days + 23:59:59.31 days + 23:59:59 = 2764799 seconds.

How would you convert those seconds into an To simplify things we’ll look individually at the three TIME Function arguments: In the following example, the value in cell A2 is 3661 seconds, which is 1 hour, 1  minute and 1 second.We need the remainder of A2/3600, which is MOD(A2,3600) = 61, but still have to convert these seconds to minutes. Since there are 60 seconds in a minute, you may try to divide it by 60, but the result will be a minute and a fraction, not minutes and seconds (in this case, 1.34).

You just have to remember to use square brackets around any cumulative number you want to represent.

Just what I needed. In the example shown, the formula first divides the time stamp value in B5 by 86400, then adds the date value for the Unix Epoch, January 1, 1970. The INT function argument then reduces to INT(61/60), which gives us one (1), because 60 goes into 61 once.MOD(A2/3600) gives us 61 seconds, so we need to divide by 60 and get the 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.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.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.Notice the Remainder of the first division (7510/3600) is the central argument for Minutes and Seconds.You can always convert your number to a time serial number and use Excel custom formatting to show the time format correctly.

To covert to a time serial number, just divide by 86400, which gives you 1.5. What you're seeing in the Result row is the time in B4 expressed as a fraction of 1 day.

A simple example because song duration never exceeded one hour.But what if your data has values greater than 3600 seconds?

However, 80 seconds is actually 1:20 or 1 minute and 20 seconds.To convert seconds to minutes and seconds, you can divide the seconds by the total number of seconds in a day and then apply the time custom number format to display as minutes and seconds.To apply custom number formatting for minutes and seconds, enter or select a time format using the Format Cells dialog box:Below is the Format Cells dialog box with the minutes and seconds custom format:There are several time formats in this dialog box. Thanks for your help. That’s a lot of seconds.Hi Gregory… I have a situation where I have total hours (27) and the cell calcs to 3 hours (cause it’s more than 24 hours). Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. Because time is calculated in Excel as a fraction of a day, you can convert fractions to appropriate time increments by combining calculations and custom number formatting.Let’s say you have entered 80 in a cell (representing 80 seconds) and you want to convert it to the equivalent of minutes and seconds.