Computer Related > excel time question Miscellaneous
Thread Author: borasport Replies: 10
View Threaded 

New Thu 8 Aug 13 09:50 
 excel time question - borasport
In excel 2010, I have a column that contains a numeric value, which is a number of seconds. I want this to display this as hh:mm:ss

I'd think this a fairly common requirement, so am i being really stupid in being unable to find the answer ? Any attempt to format the column just keeps giving me 00:00:00, and google goes on about having to use INT and MOD and TIME - surely it can't be that complicated
Reply to this message | Report message
       
New Thu 8 Aug 13 12:00 
 excel time question - rtj70
Something like this:

=TEXT(A1/86400,CHOOSE(MATCH(A1,{0,60,3600},1),":ss","m:ss","[h]:mm:ss" ))

Last edited by: rtj70 on Thu 8 Aug 13 at 12:01
Reply to this message | Report message
       
New Thu 8 Aug 13 12:02 
 excel time question - rtj70
I added the last space before the two ) to stop this site wrapping the next to another line. The space is not really needed.

Or using words:

=TEXT(A1/86400,CHOOSE(MATCH(A1,{0,60,3600},1),"s ""sec""","m ""min"" s ""sec""","[h] ""hrs"" m ""min"" s ""sec""" ))
Last edited by: rtj70 on Thu 8 Aug 13 at 12:03
Reply to this message | Report message
       
New Thu 8 Aug 13 12:07 
 excel time question - Focusless
Or you can format the cell(s) using the standard "hh:mm:ss" time format and divide your value by (60*60*24) ie. the number of seconds in a day.
Reply to this message | Report message
       
New Thu 8 Aug 13 12:56 
 excel time question - Focusless
To clarify, in Excel the unit of time is a day. So if you displayed your value X as a date, it would be X/01/1900 ie. X days after the 'start of time' (assuming X < 32).

Your value was displayed as 00:00:00 using the time formats because they only handle up to 1 day/24 hours ie. value 1. However if your value was 1.5, it would be displayed as 12:00:00 ie. the 0.5 part is half a day, 12 hours.
Last edited by: Focusless on Thu 8 Aug 13 at 12:58
Reply to this message | Report message
       
New Thu 8 Aug 13 13:54 
 excel time question - L'escargot
Try this ...... excelsemipro.com/2010/08/convert-seconds-to-a-time-format-in-excel/
Reply to this message | Report message
       
New Thu 8 Aug 13 13:58 
 excel time question - Focusless
>> Try this ...... excelsemipro.com/2010/08/convert-seconds-to-a-time-format-in-excel/

Seems a lot more complicated than my solution.
Reply to this message | Report message
       
New Thu 8 Aug 13 14:02 
 excel time question - L'escargot
>> >> Try this ...... excelsemipro.com/2010/08/convert-seconds-to-a-time-format-in-excel/
>>
>> Seems a lot more complicated than my solution.
>>

What Gregory suggests is in effect the same as your method. It's just a matter of whether you convert the number to a time format first and then divide by 86400, or do it the other way round.
Reply to this message | Report message
       
New Thu 8 Aug 13 14:08 
 excel time question - Focusless
>> What Gregory suggests is in effect the same as your method. It's just a matter
>> of whether you convert the number to a time format first and then divide by
>> 86400, or do it the other way round.

But he appears to be using things like INT MOD and other functions, when all you borasport need is a single division.
Last edited by: Focusless on Thu 8 Aug 13 at 14:09
Reply to this message | Report message
       
New Fri 9 Aug 13 10:34 
 excel time question - borasport
Cheers all, rtj's first solution works for me so I'll go with that.

What I want to do seems fairly predictable to me, Im still baffled that the functionality isn't built in - what am I missing ?
Reply to this message | Report message
       
New Fri 9 Aug 13 10:40 
 excel time question - Focusless
It is, sort of - it's just that your value is in the wrong units (days rather than seconds). Hence my suggestion to just convert it (by dividing by 60*60*24).
Reply to this message | Report message
       
Latest Forum Posts