Results 1 to 2 of 2

Thread: Removing special characters from time format

  1. #1

    Removing special characters from time format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Current Format Format After removing non printable characters using substitute formula 48:02 48:02:00 1:36:17 1:36:17 1/2/1900 12:02:00 AM format displaying after using the below formula 48:02 minutes will reflect as 48:02:00 hours I want it to reflect as 0:48:02 in time format after removing non printable characters. Data that is greater than 0:59:59 is coming correctly but less than 1 hr is showing wrong. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"/",""),"-","")," ","") Kindly help me with a formula for the same. Data attached.
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    It helps to ask questions clearer and remove irrelevant information.
    "I need to format column B as time,
    I tried =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"/",""),"-","")," ","")
    but it failed where the time is less than 1 hour (i.e. 48:02 -> 48:02:00 not 00:48:02)"

    This should do it
    =IF(ISERROR(FIND(":",SUBSTITUTE(B2,":","|",1))),TIMEVALUE("00:"&B2),TIMEVALUE(B2))

    I remove the first : and look for the second

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •