+ Reply to Thread
Results 1 to 2 of 2

Using duration in a calculation H:mm:ss to decimal

  1. #1
    Registered User
    Join Date
    Hobart, Tasmania
    MS-Off Ver
    Excel 2003

    Using duration in a calculation H:mm:ss to decimal


    I am trying to produce a calculation for dividing distance (in km) by the time it takes to run (in the [h]:mm:ss format), to work out the average speed in km/h. The use of the duration format creates a problem in the usual calculation (dist/dur)*60.

    I need to keep the duration format to keep the column subtotals.


    It takes 14 min and 23 sec to jog 2km. (2/14.23)*60 = average speed of 8.43km/h.

    With the format as described above (ie (2/0:14:23)*60 = 12013.90 in Number format, which is clearly rubbish.

    Can anyone please advise me on how I can get the info I need?
    Last edited by pike; 04-27-2011 at 05:05 AM.

  2. #2
    Forum Expert
    Join Date
    MS-Off Ver
    XL2003 / 2007 / 2010

    Re: Using duration in a calculation

    Hi and welcome to the board

    If you enter 0:14:23 in cell A1 as time and distance in B1 your speed is given by =(B1/(A1*1440))*60 .
    You can of course simplify the function, I left it so you can see how to convert time to decimal time ( *1440)

    More info on how XL stores dates and times

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1