Date time format issue

[MDuff]

Hi all I am getting this import out of a data base that formats the time as m/d/yy HH:MM AM/PM and I need to convert this to HH:MM AM/PM with no date attached linked to it . I have tried the following formula but it is not working on all cells (see Example below) any ideas on a formula or VBA to fix this issue
I have tried both =ABS(RIGHT(B5,7)) and =ABS(RIGHT(B5,8))

thanks in advance

Moto IDP.xls
	A	B	C	D
1	Routing Set	Start Time	FC SL %	SL %
2	12:00 AM	11/29/05 12:00 AM
3	##################	11/29/05 12:30 AM
4	##################	11/29/05 1:00 AM
5	1:30 AM	11/29/05 1:30 AM
MO-DS RawData

 

[Norie]

Try this formula

=B2-INT(B2)

If Excel is recognizing the values in column B as date/times then that should strip out the integer part, which represents the date and leave the decimal part that represents the time.

 

[MDuff]

Worked like a charm thanks

 

[MDuff]

Any Ideas why this is not working?

Moto IDP Client.xls
	B	C	D	E
6	Start Time	Time	Time	FC SL %
7	38896.000	0.00	0.00
8	38896.021	0.00	0.00
9	38896.042	0.00	0.00
10	38896.063	0.00	0.00
11	38896.083	0.00	0.00
MO-TS RawData

 

[MDuff]

Sorry to waste everyones time Calc Was set to manaual