Time calculation

Hi,

I am sorry if this is a simple, easy answer but I havent been able to work it out. I am completing some time trial sheets and need some help.

I am given a trial time winner information as minute-seconds.milliseconds eg- 3-23.8 (3 mins 23.8 seconds). I am then given the other runners time of how many seconds they were off the leader eg. 1.2 seconds. I want to be able to give a total time for each runner eg - 3-23.8 + 1.2 seconds therfore total of 3 mins and 25 seconds.

I am having trouble getting excel to recognise the minute-seconds.milliseconds eg- 3-23.8 as a 'time' and then being able to add the x.x seconds onto it? Can anyone help me out with this?


Thanks

A1=3:23.8 (note the ":")
B1=0:1.2 (note the leading "0:")

Formula:

Please Login or Register  to view this content.

The ":" tells Excel you are working with times and not just real numbers.

thanks for the replies.

I am already given this information as I have described, it will not be worth re-entering x-xx.x as xx:xx:xx and x.x to 0:x.x by hand for 100s of bits of data, I was hoping there was someway to 'convert' it - I eventually worked out I could select the column with x-xx.x and use the 'replace' tool to replace - for : ? That seems to work ok

But does anyone know an easy way I can convert x.x seconds eg 3.4 in a cell to 0:3.4 ? I really just need to get a 0: entered in front of each bit of data in a coloumn?

Sorry to be a pain but if this could get worked out it would be great!

If not it sounds like I need to find another supplier of the data with it entered in a better format?

Thanks

well after some googling I have worked it out, as I said I can replace the - with :, and I had to format the cell with the x.x seconds to [h]:mm:ss.000

Sorry I missed your first reply.

For future reference, A1 has 3-23.8, B1 has 1.2. First do a replace all of "-" with ":" and A1 will revert to 03:23.8.
Then

Formula:

Please Login or Register  to view this content.

Now C1 has the difference as a time also and you can add C1 to A1 to get the "real" time of second place.

A similar forumla could be used to add ":00" to the end if you were given hours and minutes with no seconds.