• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Times formula

N

NeverSayDie

New Member
Hi All,

I have been trying to convert race times to seconds. I download them and they come formatted as such 01:12.5 which equates 1 minute 12.5 seconds. I want to convert the formatted time to 72.5 I have tried the 'secound' function but it just returns '13' which is the last bit rounded. I tried INT(A1*1440) which separated the one minute in a helper column and then in another helper column I put =A1 and formatted as 'ss.0' which

gave me the 12.5 I needed so then I multiplied the 1 minute by 60 and added it to the formatted 12.5 which resulted in 60.00083854. So I have been trying to make the formatted .00083854 back the a number [12.5] All messy I know but that is the extent of my lame efforts so far. I'm sure somebody out there will have a chuckle and give me a simple formula. I'm certain it's a bit more difficult than it sounds though.

Thanks.
 
NeverSayDie


Firstly, Welcome to the Chandoo.org forums.


A simple
Code: 
=A1*60*60*24

will do the job

then format the cell as a Number not as a time


of course that can be simplified as =A1*86400
 
Hi NeverSayDie,


Hui's post is spot on for calcultaing the the total seconds in day and caters for hours, minutes and seconds.


24 hours in a day x 60 minutes in a day x 60 seconds in a minute = the total seconds in a day.


The convert minutes to seconds you would only need to multiple the minute portion by sixty and add the 12.5 seconds you already have = 72.5 seconds.


Please also be aware that in "Surveying" the term seconds is also used (i.e Degrees Minutes Seconds) and this is a standard of measure not a measure of time.


I hope I dont confuse issues
 
Too easy for some and thanks Hui.

I was trying to make it more difficult than it was obviously. Every time I used any maths on it, it just increased the time value in the result cell as it auto formatted it the same. The formatting back to 'general' was the trick. I was trying to avoid after downloading the data, to then have to reformatt the result cell to general. Will this be avoided if I have in place a formula like =IF(A1="","",A1*86400) or will I have to general formatt after every download ?

Thanks again.
 
Hi NeverSayDie,


How about changing the format to

Custom >>


or


=TEXT(A1,"")


Regards,

Deb
 
You must log in or register to reply here.
Back
Top