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

1. ## 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:
`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:
`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. ## Re: Converting an elapsed time in decimal number format to an actual time :S

Try:

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

Regards, TMS

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

or... an alternative

Formula:
`Please Login or Register  to view this content.`

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

Try:
``Please Login or Register  to view this content.``

5. ## 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:
`Please Login or Register  to view this content.`

format as h:mm:ss AM/PM

6. ## 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:
`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. ## 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

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