Need formula to convert a timestamp to date

[Anthony_2905]

Okay, so the data I'm looking at exports in this format "10/1/2013 9:25:00 AM", which I need to convert to "10/1/13", or something similar, stripping out the time element. The obvious answer, format cells/date is not helpful, as I need to manipulate the exported data in a pivot table, which would just revert the formatted value back to a timestamp.

My question: does anyone know a formula that will change 10/1/2013 9:25:00 AM to 10/1/13, so that when I look to display three months of data, I have around 90 date values, and not 3,000 or so? I Google'd this once before and found it, but I can't find it again. I do not work with any macro's, backend coding, etc., so please someone post a formula for this and I will be much appreciative!

 

[Weazel]

if you insert a column then take the int of the timestamp...

=int(a1) then drag it down and format it as a short date

 

[Anthony_2905]

Hey thanks a lot for taking the time to answer this post - it worked. When I used that formula, it changed the datetimestamp from "1/9/2007 3:59" to "1/9/2007 0:00". Then I formatted cells "1/9/2007 0:00" to date, which removed the zeros. When I ran the pivot table it did not have the timestamp.

Thanks again, I spent 40 minutes scouring the internet, getting all this unnecessary UNIX-related information, etc. and this took 5 seconds. Glad I joined this website today.