+ Reply to Thread
Results 1 to 4 of 4

Change time value from string to timevalue

  1. #1
    Registered User
    Join Date
    09-18-2006
    Posts
    25

    Change time value from string to timevalue

    I need to change cells with a string like 2 hrs 55 min 1 sec to h:mm:ss (2:55:01). I'd be fine if they used leading zeroes, but unfortunately they don't.

    Some example data:
    10 min 13 sec
    1 hrs 9 min 1 sec
    59 sec
    2 hrs 10 min 53 sec

    =IF(MID(D2,LEN(D2)+2-6-1,1)=" ",MID(D2,LEN(D2)+2-5-1,1),MID(D2,LEN(D2)+2-5-2,2)) got me the seconds into one column, but then I ran out of ideas for minutes and hours.

    Any ideas would be great.
    Thanks,
    Tas
    Last edited by tas666; 04-14-2020 at 07:51 AM. Reason: Added "Solved" prefix

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Change time value from string to timevalue

    Try this

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-18-2006
    Posts
    25

    Thumbs up Re: Change time value from string to timevalue

    Phuocam: you are amazing! I had to alter it a bit (it would't work for anything starting with a single digit minute like 1 min 13 sec) but you solved something I'd been working on for waaaaay too long.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Change time value from string to timevalue

    Edit formula:

    Please Login or Register  to view this content.

+ 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. Convert timevalue to time
    By jeffreyj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-24-2018, 12:55 PM
  2. Convert time to text for timevalue function
    By JonathanEngr in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-24-2018, 11:02 AM
  3. Cells time for TIMEVALUE formula
    By umirin in forum Excel General
    Replies: 1
    Last Post: 08-31-2016, 10:44 AM
  4. Change TimeValue to reference a Cell entered value instead
    By coreytroy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2013, 03:36 AM
  5. if statement with timevalue? Tracking Time.
    By whywhybother in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2012, 08:58 PM
  6. Replies: 5
    Last Post: 10-20-2009, 06:45 AM
  7. passing a time string with milliseconds into TimeValue()
    By twd000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2009, 12:18 AM

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