+ Reply to Thread
Results 1 to 20 of 20

Change format of times

  1. #1
    Registered User
    Join Date
    05-19-2023
    Location
    London
    MS-Off Ver
    ONLINE
    Posts
    15

    Change format of times

    When I pull the information for call duration for my department it puts it into excel as '1h 22m 59s' as text and I need to change it into a format excel recognizes for time. For example 01:22:59 or even total minutes could be useful. Some calls last less than an hour as well so if there is a formula that can convert both that would be great! I have also attached a short list of times if this helps.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Change format of times

    Try:

    =(SUBSTITUTE(SUBSTITUTE(B2,"m",":"),"s","")+0)/60

    and format as mm:ss

    Ignore this reply. I forgot about hours...
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 05-19-2023 at 04:25 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    One way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    05-19-2023
    Location
    London
    MS-Off Ver
    ONLINE
    Posts
    15

    Re: Change format of times

    Wow thanks a lot! Sorry I should have mentioned this in my question, is there a way to use the same formula for calls that last under a minute? so will only be displayed as '34s' in the cell?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    What other variations do you get?

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Change format of times

    Not very eloquent, but this should work for the three variations
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    This for 34s
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    If you have 1h 34s, my formula will give a wrong result. AskMeAboutExcel's solution will work for that combination too.

  9. #9
    Registered User
    Join Date
    05-19-2023
    Location
    London
    MS-Off Ver
    ONLINE
    Posts
    15

    Re: Change format of times

    Thank you so much!!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    You're welcome.



    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Change format of times

    Late to the party, after a bad start...

    an alternative:

    =TIME(TEXTBEFORE(B2,"h",,,,0),TEXTAFTER(" "&TEXTBEFORE(B2,"m",,,,0)," ",-1),TEXTAFTER(TEXTBEFORE(B2,"s",,,,0)," ",-1))

    format as hh:mm:ss
    Attached Files Attached Files

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    @Glenn: close, no prize. It doesn't work for 34s. 1h 34s, yes. 34s, no.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Change format of times

    Mehh! Stubbornness might encorage me to have another go. But not immediately, my laptop is flat out of juice...

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    Just for fun, and because we can, and because I’ve spent some time with this …
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Change format of times

    Either this, copied down:
    =TIME(TEXTBEFORE(B2,"h",,,,0),TEXTAFTER(" "&TEXTBEFORE(" "&B2,"m",,,,0)," ",-1,,,0),TEXTAFTER(TEXTBEFORE(" "&B2,"s",,,,0)," ",-1,,,0))

    or this, as a spill array (delete expected results first):
    =BYROW(B2:B7,LAMBDA(x,TIME(TEXTBEFORE(x,"h",,,,0),TEXTAFTER(" "&TEXTBEFORE(" "&x,"m",,,,0)," ",-1,,,0),TEXTAFTER(TEXTBEFORE(" "&x,"s",,,,0)," ",-1,,,0))))
    Attached Files Attached Files

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    Happy now?

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Change format of times

    Ecstatic... but I think the OP has gone away and forgottten the poor, downtrodden, abandoned souls that continue to toil at the coal face of intractable Excel problems...

    Or as you once said.... "Elvis has left the building".

  18. #18
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,466

    Re: Change format of times

    And I enter the building.

    Everyone has nice and good solutions.

    Here's another solution from my side:

    Please try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by HansDouwe; 05-19-2023 at 01:32 PM. Reason: The LET-function is removed

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,697

    Re: Change format of times

    Another winner!

  20. #20
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,378

    Re: Change format of times

    Power Query
    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. Replies: 4
    Last Post: 12-04-2017, 05:22 PM
  2. Replies: 3
    Last Post: 02-24-2015, 02:58 AM
  3. change times
    By swimdave in forum Excel General
    Replies: 4
    Last Post: 11-13-2014, 07:35 AM
  4. Replies: 5
    Last Post: 04-01-2014, 09:37 AM
  5. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  6. Replies: 8
    Last Post: 08-15-2012, 11:36 AM
  7. How to change all the times
    By Biscuits in forum Excel General
    Replies: 10
    Last Post: 02-07-2010, 03:43 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