# Posts by venkateswaran

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

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

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

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

• ## 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