+ Reply to Thread
Results 1 to 4 of 4

Converting an elapsed time in decimal number format to an actual time :S

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Question Converting an elapsed time in decimal number format to an actual time :S

    Hi All,

    So my title description may be a little confusing but let me clarify. I have a series of elapsed times that have been recorded with a decimal seperating minutes and second (ie; 5 minutes and 10 seconds = 5.10). These lap times are recorded in the same format over approximately half an hour. I have a start time (ie; 7:27:30 AM) and I wish to determine the actual time based on the elapsed time after the start time for each lap such that...

    Start Time Lap Actual Time
    7:27:30 AM 2.35 7:30:05 AM
    4.27 7:31:57 AM
    5.10 7:32:30 AM

    Notice how the last time is incorrect! This should read 7:32:40 AM. Assuming that the table above spans A1:C4 I have the following formula entered in C2 and dragged down to determine Actual time;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Unfortunately this formula, even tho the lap time cells are formated to always show 2 digits, reads 5.10 as 5.1 and thus only adds one second instead of 10 seconds. Likewise if I have 5.00 a similar problem occurs. Unfortunately I cannot change the format of the raw lap times. I have also tried;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But this also returns errors.

    Any Ideas??? Im sure this is a simple fix. Thankyou in advance to anyone that helps

    Kind regards,

    Ben

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,040

    Re: Converting an elapsed time in decimal number format to an actual time :S

    Try:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Converting an elapsed time in decimal number format to an actual time :S

    or... an alternative

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn



  4. #4
    Registered User
    Join Date
    02-23-2014
    Location
    Michigan, US
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Converting an elapsed time in decimal number format to an actual time :S

    Try:
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    Re: Converting an elapsed time in decimal number format to an actual time :S

    Sorry, there was a mistake in my earlier post. Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    format as h:mm:ss AM/PM

  6. #6
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: Converting an elapsed time in decimal number format to an actual time :S

    Thankyou to SnarlingSheep; That solution worked perfectly, even when my lap time was something like 9.00 which excel reads as 9!

    My final solution ended up being;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks to everyone else for your ideas. SnarlingSheep's method was the only one which worked

    Regards.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,635

    Re: Converting an elapsed time in decimal number format to an actual time :S

    Another way:

    A
    B
    C
    D
    1
    Start Time
    Lap
    Actual Time
    2
    7:27:30 AM
    2.35
    7:30:05 AM
    C2: =$A$2+DOLLARDE(B2,60)/1440
    3
    4.27
    7:31:57 AM
    4
    5.10
    7:32:40 AM
    Last edited by shg; 07-20-2014 at 08:56 PM.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

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