Stripping data?

[djarcadian]

I have some data in a spreadsheet that's being exported from a CRM system so I can't change the formatting.

It has data information which includes the time. Like 5/16/13 8:00AM.

I want to reference it in other cells but want to strip the time from it. Formatting the cell to display just the date doesn't strip the time from it. Is there anything else I can do?

[judgeh59]

it's probably not a real date (according to excel)...do this to confirm....in an open cell =ISNUMBER(A1).....assuming a CRM date is in there....if it's a real excel date it should say TRUE....if it is false you are going to have to parse the two pieces....let me know if this is a real date or not....

[Kevin UK]

Hi

If that is text and assuming it is in A1, try:
=REPLACE(MID(A1,FIND(" ",A1)+1,255),LEN(MID(A1,FIND(" ",A1)+1,255))-1,0," ")+0
Format as time.

[djarcadian]

it's probably not a real date (according to excel)...do this to confirm....in an open cell =ISNUMBER(A1).....assuming a CRM date is in there....if it's a real excel date it should say TRUE....if it is false you are going to have to parse the two pieces....let me know if this is a real date or not....

I got a TRUE.

[FDibbins]

Can you upload a sample workbook with a small sample of your data?

[Tony Valko]

If you just want the date portion you'll have to extract that into a different cell.

A1 = 5/16/13 8:00 AM

To extract just the date:

=INT(A1)

Format as Date (if needed).