+ 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 2007,2010,365
    Posts
    44,086

    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 - Retired Excel/VBA Consultant

    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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  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 Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    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 Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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)

Similar Threads

  1. [SOLVED] Converting time hh:mm:ss into decimal format? mm.ss
    By moxman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-16-2013, 06:53 PM
  2. Replies: 2
    Last Post: 04-19-2012, 10:34 PM
  3. Replies: 4
    Last Post: 07-14-2010, 03:17 PM
  4. Converting Time Sheet Into Decimal Format
    By TropicalRain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2007, 02:30 AM
  5. [SOLVED] Converting a decimal number into time
    By M.A.Tyler in forum Excel General
    Replies: 7
    Last Post: 05-25-2006, 11:45 PM

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