Fixing Date Data

[cstimart]

When I extract my data, not all of the dates are in the same format. I can format the date to appear as mm/dd/yyyy, however, the value in some of the cells still includes time detail. How do I get rid of the time and retain the date?

 

[VoG]

Try

=TRUNC(A1)

 

[cstimart]

Cell D2 shows "03/19/2002 1:00:00 AM"

In D3 I used =TRUNC(D2,10) and it displays "03/19/2002"...if I then copy/paste value I get "03/19/2002 1:00:00 AM"

 

[geospatial]

Make sure that you use paste special values and number formats.

 

[VoG]

=TRUNC(D2)

and format as Date.

 

[cstimart]

Is there a way in VBA to Format the entire column as "mm/dd/yyyy" without using a for/next loop? I have nearly 20k entries, and the list grows.

I tried the following, but it crashed.

Columns("E:G").NumberFormat = "mm/dd/yyyy"

 

[VoG]

That worked OK for me.

 

[cstimart]

That worked OK for me.

OK, it's working for me (not crashing), however, it's not removing any of the time stamps?

Is it possible to do such a thing w/o a for/next loop?

 

[VoG]

That is only changing the displayed value, not the underlying value. You would need to loop and use INT().