# Date time format issue

#### MDuff

##### Well-known Member
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))

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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.

Worked like a charm thanks

Sorry to waste everyones time Calc Was set to manaual

Replies
2
Views
189
Replies
2
Views
167
Replies
8
Views
289
Replies
9
Views
290
Replies
2
Views
239

1,214,275
Messages
6,118,623
Members
448,844
Latest member
Jayee04e

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back