removing digits from 00:00:00:000

Hi
I have an excel file that gives time as 00:00:00:000
I cant use MINUTE or HOUR formulas on this. How do I do remove the 000?
Thanks
Conor

Originally Posted by conor00

I have an excel file that gives time as 00:00:00:000[.] I cant use MINUTE or HOUR formulas on this. How do I do remove the 000?

Excel does not display numeric time in that format.

Could you be more complete and more precise?

First, by "excel file", I assume you mean a file with the ".xls", ".xlsx" or ".xlsm" extension; specifically, not ".cvs" or ".xml". Right?

Second, do you mean literally 00:00:00:000; or do you mean "in that form", perhaps hh:mm:ss:nnn? If the latter, is "nnn" literally "000"?

Finally, what exactly is in the Formula Bar when you select the cell? What is the cell format? What is the type of the cell value; for example, what does =ISTEXT(A1) return?

Alternatively, simply attach an example Excel file to your next response.

Since HOUR and MINUTE do not work, I suspect ISTEXT(A1) returns TRUE. If the text always ends with ":000", you use Find and Replace (ctrl+F) to find :000 and replace with nothing. That should also convert the cell value to numeric time.

First, by "excel file", I assume you mean a file with the ".xls", ".xlsx" or ".xlsm" extension; specifically, not ".cvs" or ".xml". Right?

Right, .xlsx

Second, do you mean literally 00:00:00:000; or do you mean "in that form", perhaps hh:mm:ss:nnn? If the latter, is "nnn" literally "000"?

01 Dec 2011 00:00:08:000 is in column 1. I did text-to-columns to split out 00:00:08:000 into cell d2. ISTEXT(D2) returns true. I then used LEFT(D2,2) which returned 00


Alternatively, simply attach an example Excel file to your next response.

Since HOUR and MINUTE do not work, I suspect ISTEXT(A1) returns TRUE. If the text always ends with ":000", you use Find and Replace (ctrl+F) to find :000 and replace with nothing. That should also convert the cell value to numeric time.[/QUOTE]

Originally Posted by conor00

01 Dec 2011 00:00:08:000 is in column 1. I did text-to-columns to split out 00:00:08:000 into cell d2. ISTEXT(D2) returns true.

I presume ":000" is intended to represent milliseconds.

If you are entering the data manually, type 01 Dec 2011 00:00:08.000 instead. Change the decimal separator (".") to comma (",") or whatever your regional decimal separator is.

If you are entering the data automatically (file import) or semi-automatically (copy-and-paste), use Find And Replace (ctrl+F) to change ":000" to ".000".

In both cases, ISTEXT(D2) should now return FALSE. If it still returns TRUE, perhaps Excel does not recognize the form 01 Dec 2011 as a date. If it does not, you might be able to use Text To Columns to convert the date. Let us know if you need more help with that.

However, another problem could be extraneous spaces (or even non-breaking spaces; HTML nbsp) between or around the date and time.

If you continue to have problems, it would be helpful if you attached an example Excel file, as I suggested previously.