Date format sorting woes-put me out of my misery!!

**u23892** · 10-12-2007, 05:03 PM

I'm data mining for a work project and the entire team is shut down of this issue. I have the date and time in one colum (well, sure, it's the colum I need to sort ALL my 25Mb worth of data of of). It's in the format of:

Dec 15, 2005 14:38
Dec 15, 2005 16:41
Dec 22, 2005 9:15
Dec 22, 2005 13:39
Dec 27, 2005 10:26
Dec 28, 2005 13:08
Dec 30, 2005 22:55

I need to format it like:
Dec 15, 2005
Dec 15, 2005
Dec 22, 2005
Dec 22, 2005
Dec 27, 2005
Dec 28, 2005
Dec 30, 2005

With the first format, I go to do my pivot table and each one is only counted once because of the flipping time stamp. There has to be a way, no way I'm going through all this data and taking out the time stamp one cell at a time.

Any words of wisdom???

**daddylonglegs** · 10-12-2007, 05:45 PM

You could extract the date only into another column with

=INT(A1)

where your date and time is in A1

**Ikaabod** · 10-12-2007, 05:48 PM

Assuming your data starts in cell A1, I believe this will take the time out and return only the date:
=ROUNDDOWN(A1,0)
Copy/paste this down in a helper column, then you can copy/pastespecial/values into your data column and delete the helper column

Hope that helps.

**u23892** · 10-12-2007, 05:57 PM

So, I have this formula in my colum I created that referances the date time string. How do I get the formula to spit out a number? Right now, it just displays the formula.

Thanks for the tip, BTW. I was thinking about using the truncating function (interger) but did not have a warm fuzzy this would get me there.

Just want to write a VB macro that says " Hey go to this Z colume, count X number of placeholders over, then truncate the rest. Put the results in colum Y and move to the next row in colume Z"

I'm not going anywhere with the function in the cell instead of the outcome of the function.

I can't be this hard...