Date time format issue

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529
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
ABCD
1Routing SetStart TimeFC SL %SL %
212:00 AM11/29/05 12:00 AM
3##################11/29/05 12:30 AM
4##################11/29/05 1:00 AM
51:30 AM11/29/05 1:30 AM
MO-DS RawData
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,665
Members
412,481
Latest member
nhantam
Top