Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am doing my Msc Ecology thesis and in the process of organizing my data set. When inputting my data for the amount of time a skink spent in a hole I inputted h:mm:ss for the time it went in and the time it came out.

I then subtracted the 'out' time from the 'in' time to find the amount of time it spent in the hole. I formatted this cell as [ss]. I am now trying to find the average time the skink spend in a hole. I am having problems because I can only get the average number of seconds, using =average(), when I use only numbers in [ss] format.

Some of my other numbers are in general format (due to how I collected data). When I ask for say the average of 16.1 sec in general format and 86 sec in [ss] format the answer is 8.1. Thus I would like to change the formatting of the [ss] formatted data to general or number format. I have tried this and instead of keeping the number of seconds, it changes it to a much smaller number e.g. 86 seconds becomes 0.00099537.

share|improve this question

migrated from stackoverflow.com Feb 28 '14 at 4:52

This question came from our site for professional and enthusiast programmers.

1 Answer 1

0.00099537 is the portion that 86 seconds is of one day. So for a value of 86 multiply by 60*60*24 (by 86,400). But you might be wiser to keep 86 represented that way and change other values to suit. ie divide 16.1 by 86,400 for average etc purposes and format the (very small value) result as seconds.

The import point is to be consistent, but fortunately that should not require retyping. You can determine the format of a cell, say G6, with =CELL("format",G6) and if there is any sort of arrangement for your data drag such a formula across and/or down and that should be sufficient to identify which cells to divide (or multiply, if you must) in order to achieve consistency.

Attempting clarification (but risking obfuscation):

SO21446095 example

The CELL format code "G" for General is also generic in that it includes some Custom formats such as [ss].

share|improve this answer
Thanks heaps! I will use that. –  Terra Jan 30 '14 at 1:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.