Removing special characters from time format

ajithkumar

New member
Joined
Oct 13, 2014
Messages
2
Reaction score
0
Points
0
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.
 

Attachments

  • Work file productivity.xlsx
    33 KB · Views: 10

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
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
 
Top