 # Time format hh:mm:ss.000

• I'm trying to extract only hh:mm:ss from the mm/dd/yyyy hh:mm:ss.000 format. reason behind this is, i need to search exact hh:mm:ss, when it is found i will locate that row as starting and copy range of records.For that what i'm doing is

Ex.
A1 12/28/2012 09:10:10.123 B1 =Left(Mod(A1,1),find(".",Mod(A1,1))-1)
A2 12/28/2012 09:10:10.678 B2 =Left(Mod(A2,1),find(".",Mod(A2,1))-1)
A3 12/28/2012 09:10:11.108 B2 =Left(Mod(A2,1),find(".",Mod(A3,1))-1)

getting error 0 value but expected value is 09:10:10, 09:10:10, 09:10:11

Appreciate ur great help

Regards,
venkates k

• Re: Time format hh:mm:ss.000

In b1 input =Rounddown(a1,0), this extracts the date segment, then =a1-b1 will give you the time segment.[TABLE="width: 83"]

[tr]

[TD="class: xl63, width: 111, bgcolor: transparent, align: right"][/TD]

[/tr]

[/TABLE]

• Re: Time format hh:mm:ss.000

Another option: =MID(A1,FIND(":",A1)-2,8)+0 and format cells as hh:mm:ss

• Re: Time format hh:mm:ss.000

Thanks all for ur effort. got the solution by using below formula.
= LEFT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-4)

Regards,
Venkateswaran K

Quote from [email protected];642004

Another option: =MID(A1,FIND(":",A1)-2,8)+0 and format cells as hh:mm:ss

• Re: Time format hh:mm:ss.000

Quote from venkateswaran;642235

Thanks all for ur effort. got the solution by using below formula.
= LEFT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-4)

Regards,
Venkateswaran K

Hello,

It's good that you answered this year Solution found is text and you can not do any further calculations.

Happy New Year.

• Re: Time format hh:mm:ss.000

Actually my requirement: I have *.csv file which has device power quality data, every sec 25 lines will be logged into *.csv file(it will be in millisecond). To extract the correct report i need to locate the specific time ("1:38:30") then i select range of cells for custom report file. Problem i faced is "1:38:29.533" is consider as "1:38:30" while find option using macro. so using this formula to locate row value and then extracting the range
of cells
= LEFT(TEXT(A1,"h:mm:ss.000"),LEN(TEXT(A1,"h:mm:ss.000"))-4)

Regards,
Venkateswaran K

Quote from venkateswaran;642235

Thanks all for ur effort. got the solution by using below formula.
= LEFT(TEXT(A1,"h:mm:ss"),LEN(TEXT(A1,"h:mm:ss"))-4)

Regards,
Venkateswaran K