+ Reply to Thread
Results 1 to 15 of 15

Convert Text :> Time... Using VBA

  1. #1
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Question Convert Text :> Time... Using VBA

    Hi all,

    In a export of data i have a few colums containing time info as text in the following format...

    1h 45m 25s
    1h 6m 45s
    17m 16s
    1m 45s
    42s
    4m 28s
    10m 44s
    6h 8m 15s
    10m 8s
    4h 48m 56s
    1m 33s
    3h 52m 44s
    2h 3m 33s


    I want to know if there is a VBA script I can use to run through those columns converting the data to actual time... so stripping the "h" "m" "s" from it, and returning the data in something like "01:06:54" format. I've attached a "sample" spreadsheet, which is simply 1 small column of data. Note: Some entries are blank, as this best replicates the data I get, so a formula needs to be able to skip blank entries.



    ^^ OK, scratch that, I cant upload data like this from the office, so I'll need to do it when I get home a little later.... but it really is just...

    Avg Response Time in Bhrs
    29m 16s
    1m 32s
    30s
    56m 39s
    2h 9m 49s
    8m 17s

    22m 0s
    5h 35m 37s
    43m 32s
    38s

    1h 36m 36s
    5m 45s
    2d 20h 36m 40s
    57m 29s
    58s
    43m 37s
    2h 11m 8s
    10s
    2m 36s

    3d 22h 2m 9s
    1h 9m 51s

    37s
    1h 45m 25s
    1h 6m 45s



    Many thanks in advance...

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Convert Text :> Time... Using VBA

    You could try this UDF.
    Please Login or Register  to view this content.
    Last edited by Norie; 04-06-2021 at 12:13 PM.
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,802

    Re: Convert Text :> Time... Using VBA

    Hi Norie, based on the function name, I think your last line of code needs to be changed to:
    Please Login or Register  to view this content.
    Otherwise it returns 0.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Convert Text :> Time... Using VBA

    Paul

    You are right - I changed the name of the function at the last minute and forgot to update it in the body of the code.

  5. #5
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    Hey Guys,

    Thanks for this, I'll test this today, if it works, sweet, I'll let you know. If not, I'll throw up a sample sheet and details of what happens.

  6. #6
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    OK, I'm really grateful for this, but if I'm honest I'm having a little trouble seeing how to use this function... I have a SUB already running to manipulate some other areas of the data... I really want to provide a sample, but its full of private customer data.... can I "call" this function? How do I indicate which columns/rows to run it against?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Convert Text :> Time... Using VBA

    It's a user defined function.

    You would put the code in a standard module and then use it on a worksheet like any other function.

    For example, I put the data you posted in column A and then put this formula in B2 and copied it down.

    =GetTimeFromString(A2)

    It could also be called from other code.

  8. #8
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    ahhhhhh awesome, I'll give that a go, thank you!

  9. #9
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    OK, superb! This works in the sheet as described! Thank you so much.

    Is it possible for me to run through the column in VBA, under the following conditions...

    Check if A2(then a3 etc, for as many as there is data for)
    converting each one as it goes....


    If this is too much, or not possible so far as you know I totally understand, and really appreciate what you've done for me so far.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Convert Text :> Time... Using VBA

    Not 100% sure what you mean but you could do something like this.
    Please Login or Register  to view this content.
    For the above to work we need to make a minor change to the function.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    OK, as a function this is working fine, i haven't tried to run it as part of script yet, but otherwise it's fine (I also modified the Module name, and it stopped working, that took me a few minutes to figure out!).

    However now I'm working with a larger dataset, I can see that there are Mos (months) and W (weeks) in here too....

    23h 18m 39s
    3d 6h 52m 30s
    13s
    27m 22s
    9m 24s
    1mos 0w 4d 22h 56m 0s
    1mos 0w 0d 17h 28m 30s
    1w 4d 19h 54m 59s
    1d 19h 50m 14s


    Considering that "Mos" and "W" are just preset numbers of Hours, I can see a way to amend your function to include this... Does anyone have any thoughts?

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,838

    Re: Convert Text :> Time... Using VBA

    When Mos w d are there what should be the format of result. Is it dd hh:mm:ss or [h]:mm:ss.
    And also Mos is be treated as 30 days.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  13. #13
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    79

    Re: Convert Text :> Time... Using VBA

    Hi there kvsrinivasamurthy, thanks for the reply.

    [h]:mm:ss is the preferred format. In a perfect world, it would depend on the month, but for the purposes of this report, 30 days would be fine in most cases.

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,838

    Re: Convert Text :> Time... Using VBA

    Code for UDF DayAndTime. Format cells for [hh]:mm:ss.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-12-2021 at 06:09 AM.

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,670

    Re: Convert Text :> Time... Using VBA

    Please try
    Formula
    =SUMPRODUCT((TEXT(MID(0&A2,FIND({"mo","w","d","h","m ","s"},"00"&A2&" mowdhsm ")-3,2),"0;;0;\0"))*{30,7,1,1,1,1}/{1,1,1,24,1440,86400})

    or UDF =gTime(A2)

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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 Text Field with time > 24hours to time
    By Viema in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2020, 07:26 AM
  2. [SOLVED] Convert text time to decimal time
    By HappierThan in forum Excel General
    Replies: 5
    Last Post: 06-13-2020, 12:51 AM
  3. Convert Text Time From A Userform Textbox to A Time Value
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2019, 09:53 PM
  4. Replies: 15
    Last Post: 08-23-2017, 03:32 PM
  5. Excel convert Text time formatted 10h 58m to time so I can sum together
    By slinka in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-04-2016, 10:17 AM
  6. Time stored as text. How can I convert to data and time?
    By matthewbutterworth in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-07-2013, 10:19 AM
  7. convert time imported as text to time format for calculations
    By batfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 07:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1