+ Reply to Thread
Results 1 to 14 of 14

Format unusual time

  1. #1
    Registered User
    Join Date
    06-07-2018
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    12

    Format unusual time

    I'm still an excel beginner and I can't seem to get a formula on how to convert unusual time format to just hh:mm:ss

    Here are some examples:

    0d:0h:4m:48 s:
    0d:0h:3m:39 s:
    0d:0h:8m:37 s:
    0d:0h:31m:57 s:
    0d:0h:4m:9 s:

    I would like to have it converted to just MM:SS

    4:48
    3:39
    8:37
    31:57
    4:09

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Format unusual time

    If the "unusual time" always starts with literally "0d:0h", then enter the following into a parallel column (I assume the original data is in column A):

    =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "0d:0h", "0"), "m", ""), " s:", "")

    The double negate (--) converts text time to numeric time.

    More generally, if it is not always zero days and zero hours, then:

    =INT(LEFT(A1, FIND("d",A1)-1)) + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1, FIND(":",A1)+1, 99), "h", ""), "m", ""), " s:", "")

    In both cases, format the cells as Custom [mm]:ss .

    The square brackets ensure that minutes greater than 59 are displayed.

    PS.... I am surprised that the original data ends with space followed by "s:". If that is a posting typo, change " s:" accordingly.
    Last edited by joeu2004; 06-07-2018 at 07:16 PM. Reason: minor

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Format unusual time

    with PowerQuery (Get&Transform)
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sandy666; 06-07-2018 at 07:30 PM. Reason: typo

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Format unusual time

    This will modify your text as requested


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Format unusual time

    Hi, to all!

    Another options could be:

    =--SUBSTITUTE(SUBSTITUTE(MID(A1,2+FIND("h:",A1),99),"m","")," s:","")

    or simply (if allways have 0d:0h: at the beginning) :

    =--SUBSTITUTE(SUBSTITUTE(MID(A1,7,99),"m","")," s:","")

    Give "hh:mm" format (without quotes). Blessings!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Format unusual time

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



    A
    B
    1
    0d:0h:4m:48 s:
    4:48
    2
    0d:0h:3m:39 s:
    3:39
    3
    0d:0h:8m:37 s:
    8:37
    4
    0d:0h:31m:57 s:
    31:57
    5
    0d:0h:4m:9 s:
    4:09
    Last edited by FlameRetired; 06-07-2018 at 07:54 PM.
    Dave

  7. #7
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Format unusual time

    Quote Originally Posted by johnmpl View Post
    =--SUBSTITUTE(SUBSTITUTE(MID(A1,2+FIND("h:",A1),99),"m","")," s:","")
    [or]
    =--SUBSTITUTE(SUBSTITUTE(MID(A1,7,99),"m","")," s:","")
    Give "hh:mm" format (without quotes).
    It is important to understand that these formulas convert to h:mm as noted, not m:ss as required.

    They might look the say, but it might make a big difference if the converted time is used in calculations.

    For example, if A1 is "0d:0h:4m:48 s:", and B1 has one of those formulas, =B1*1440 is 288.00 instead of 4.80, the latter being the decimal number of minutes.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Format unusual time

    Another way. Will resolve the time values.

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



    A
    B
    1
    0d:0h:4m:48 s:
    4:48
    2
    0d:0h:3m:39 s:
    3:39
    3
    0d:0h:8m:37 s:
    8:37
    4
    0d:0h:31m:57 s:
    31:57
    5
    0d:0h:4m:9 s:
    4:09

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Format unusual time

    A little shorter. Array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

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

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Format unusual time

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

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Format unusual time

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Format unusual time

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

    Format as Custom, m:ss

    v A B
    1 0d:0h:4m:48 s: 4:48
    2 0d:0h:3m:39 s: 3:39
    3 0d:0h:8m:37 s: 8:37
    4 0d:0h:31m:57 s: 31:57
    5 0d:0h:4m:9 s: 4:09
    Last edited by AlKey; 06-08-2018 at 11:26 AM.

  13. #13
    Registered User
    Join Date
    06-07-2018
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    12
    Thanks for all the inputs! This made our work faster

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Format unusual time

    If that takes care of your original question, & to say Thanks and for better Motivation, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
      then
    2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

+ 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. Need Unusual Report Format
    By michaelfairbairn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-11-2018, 06:53 PM
  2. Unusual Date Format - need a fix
    By simon4amiee in forum Excel General
    Replies: 61
    Last Post: 01-08-2015, 11:00 AM
  3. [SOLVED] Converting unusual time format
    By essee in forum Excel General
    Replies: 23
    Last Post: 09-24-2012, 08:36 AM
  4. Counting for an unusual time format.
    By percyth1 in forum Excel General
    Replies: 5
    Last Post: 01-31-2012, 11:08 PM
  5. Unusual Number Format With DDE
    By gpercy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2008, 11:44 AM
  6. Text Import Unusual Format
    By rerhart in forum Excel General
    Replies: 2
    Last Post: 01-02-2007, 04:07 PM
  7. Unusual data format
    By Salty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2005, 08:45 AM

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