+ Reply to Thread
Results 1 to 13 of 13

Racing Times to be converted into seconds by formula

  1. 07-30-2024, 01:15 PM #1
    ThomasB61
    ThomasB61 is offline
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    42

    Racing Times to be converted into seconds by formula

    Hello Xlers,
    I face the issue that my racing times found in columns "I" to "M" are not always in the right format.
    I need all columns in seconds.
    In example: cell I2 shows the number: 01:32:44.010 (1 hour: 32 minutes: 44 seconds and 010 hundredths of a sceond). I need it in seconds which will be : 5564.010 seconds
    Which function or formula can do that?

    My original file consist of 60,000 lines and growing.

    I attach a sample file and hopefully someone can help me to have those 4 columns being revised into "seconds", showing me the correct function or formula.

    Thank you very much
    Thomas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-30-2024, 01:19 PM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Racing Times to be converted into seconds by formula

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


    Copy across and down

    With 60K rows you might want to consider VBA.
    Last edited by JohnTopley; 07-30-2024 at 01:54 PM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  3. 07-30-2024, 03:03 PM #3
    ThomasB61
    ThomasB61 is offline
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    42

    Re: Racing Times to be converted into seconds by formula

    Thank you John

    But unfortunately this gives me a #Value error.
    What am I doing wrong?

    Thomas
    Reply With QuoteRegister To Reply
  4. 07-30-2024, 05:23 PM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Racing Times to be converted into seconds by formula

    Please see the attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-31-2024, 02:43 AM #5
    PKowalik
    PKowalik is offline
    Forum Contributor
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    365v.2409 - office laptop (used also elsewhere); 2016 - office desktop, 2010, 2019 - private laptop
    Posts
    196

    Re: Racing Times to be converted into seconds by formula

    Quote Originally Posted by ThomasB61 View Post
    Thank you John
    But unfortunately this gives me a #Value error.
    The same in my Excel.
    Przemyslaw Kowalik, Lublin Univ. of Technology
    Reply With QuoteRegister To Reply
  6. 07-31-2024, 02:54 AM #6
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Racing Times to be converted into seconds by formula

    Even with the file I posted? Results are in columns N:R.

    Any #VALUE error is usually due to data incompatibility i.e data is text rather than numeric. All time formulas in columns I:N are numeric so I am confused as to why error is occurring.
    Reply With QuoteRegister To Reply
  7. 07-31-2024, 03:16 AM #7
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Racing Times to be converted into seconds by formula

    All ok to me. It's best for Thomas to attach his workbook showing the errors.
    Reply With QuoteRegister To Reply
  8. 07-31-2024, 03:24 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,185

    Re: Racing Times to be converted into seconds by formula

    It's fine for me - has to be a locale-related issue.

    Out of interest, if you are getting #VALUE!, please try this:

    =SUSTITUIR([@Sec4];".";",")*86400 (Spanish)

    =PODSTAW([@Sec4];".";",")*86400 (Polish)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  9. 07-31-2024, 06:50 AM #9
    PKowalik
    PKowalik is offline
    Forum Contributor
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    365v.2409 - office laptop (used also elsewhere); 2016 - office desktop, 2010, 2019 - private laptop
    Posts
    196

    Re: Racing Times to be converted into seconds by formula

    Thanks, that's it. At the moment I have Office switched to English, but locale is still Polish (which is identical to Spanish as of the decimal separator).
    Reply With QuoteRegister To Reply
  10. 07-31-2024, 09:25 AM #10
    ThomasB61
    ThomasB61 is offline
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    42

    Re: Racing Times to be converted into seconds by formula

    Thanks to all of you.
    I figured it out to seperate the data (@Time) using functions LEFT, PART and RIGHT and multiply each with its seconds. Issue is also that the new data (I needed it in seconds) must be formated separetly again to "standard". I am using german version of excel365
    Thomas
    Reply With QuoteRegister To Reply
  11. 07-31-2024, 09:30 AM #11
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Racing Times to be converted into seconds by formula

    seperate the data (@Time) using functions LEFT, PART and RIGHT
    Why ?

    The file posted gave the answers you required.
    Reply With QuoteRegister To Reply
  12. 07-31-2024, 02:43 PM #12
    PKowalik
    PKowalik is offline
    Forum Contributor
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    365v.2409 - office laptop (used also elsewhere); 2016 - office desktop, 2010, 2019 - private laptop
    Posts
    196

    Re: Racing Times to be converted into seconds by formula

    I guess that it can make the resulting formula "locale-independent". If you deal with substrings, you don't have to care about dot/comma.
    Reply With QuoteRegister To Reply
  13. 07-31-2024, 02:59 PM #13
    ThomasB61
    ThomasB61 is offline
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    42

    Re: Racing Times to be converted into seconds by formula

    Thank you very much John.
    I overlooked this reply. Ofcourse I appreciate your time you looked into it.
    Thank you
    Thomas
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula for getting seconds from the difference between times
    By stormj1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-25-2021, 10:31 AM
  2. Formula to find call times over 70 seconds for a given date from CSV
    By Awilding101 in forum Excel General
    Replies: 4
    Last Post: 01-06-2015, 05:51 PM
  3. fill out a form converted to excel multiple times via groupings on another worksheet
    By mabm529 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2014, 12:16 PM
  4. Need template for Racing qualifying Lap Times
    By smokem14 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-17-2013, 02:11 PM
  5. After pasting converted times, updating will show 0000
    By rodvold in forum Excel General
    Replies: 2
    Last Post: 12-29-2009, 07:22 AM
  6. Daily Racing Form: Horse racing
    By mehtalmann in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-11-2006, 04:40 PM
  7. How do I set up stopwatch racing times in Excel?
    By Armand in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2006, 08:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1