Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel and Time Handling

    Hello,

    Every time I try to use times in Excel, it wants to make them into times of the day instead of just time. In this case I have a bunch of times related to a cycling time trial. Riders ride a 10.25 mile course and have a time to complete the ride in minutes and seconds. So I do not want to deal with hours at all. Ultimately I want to enter time and calculate speed. The calculation is 615 divided by the time. 615 is 10.25 times 60 to convert minutes to hours and 10.25 is the mileage of the course. Here are my issues:

    First: I want to be able to enter data as 22:07 for 22 minutes and 7 seconds. If I enter data this way it will be entered as 22 hours and 7 minutes. The only way I can seem to get what I am looking for is to format the cells as [mm]:ss and enter the data as 0:22:07.

    Second: if my time data is in column C and we are in row 5, here is my calculation for the speed.

    =(615/((MINUTE(C5))+((SECOND(C5))/60)))

    I probably have too many ()'s in there but I wanted to make sure I got it right Here are my questions on this one:
    1. Are there any issues with my formula? - it seems to work fine
    2. Is there an easier way to do the calculation?
    3. How can I handle data entry more easily? (entering an extra "0:" for every rider is a lot of extra work)

    Thanks,

    Andy

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    A simpler calc would be 10.25/C5/24
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Geez Rory - that was kind of simple

    That makes the calculation much easier. Any thoughts on the entry of the time without the preceding "0:", so I can just enter minutes:seconds?

    This group never ceases to amaze me with how simple things can be

    Thanks!

    Andy

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you enter it as 22:07, then you can simply make the formula:
    =10.25/(C5/60)/24

    If you enter it as 22.07, then you could use:
    =10.25/((INT(C5)+(MOD(C5,1)*100)/60)/1440)/24

    otherwise you would need code to alter the entry to minutes and seconds from hours and minutes.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I am trying to implement the [=10.25/(C5/60)/24] formula and it works but when I enter the data, I get values such as:

    22:07
    22:37
    23:35

    but then, the ones greater than 24, change

    24:37 appears as 0:37
    25:57 appears as 0:57

    if I look at the formula field near the top of Excel the upper values show as 10:07:00 PM and the ones over 24 show as 01/01/1900 12:37:00 AM.

    The formatting for the cells is Custom h:mm in all cases.

    I can not figure out how to get that date out of the value. I want to show both the times in min:sec, along with the mph values.

    Thanks and sorry to be a pain...

    Andy

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Format the cells as [hh]:mm
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for getting me unwrapped from the axle

    Have a good one - that worked like a champ

    Andy

Posting Permissions

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