Changing format using VBA

[timely]

I am bringing in data that is setup with date and time but once in I need to get rid of the time. I am not sure what vba function I can use to change the format to only have the date and the time be totally erased so that I can line things up by date in a pivot table without the issue of time.

Right now I am taking the long way with the VBA by inserting another row and copying the header over and then having it put in the first one a text() function and changing it that way. That seems like to much to do this and I am sure it would only take a couple of lines to do the same thing.

Help Please

 

[SiJP]

Dim myDate as Date
myDate = [DateField]

Format(myDate, "dd/mm/yyyy")
-or-
Format(myDate, "d mmm yyyy")

etc

------------------------
Hit any User to continue

 

[SkipVought]

This sounds like data on a sheet.

I would NOT, I repeat NOT use the FORMAT function on sheet data. The Format function CHANGES the underlying data from a DATE to a STRING that can no longer be used as a date! Very BAD! Self defeating!

Please read and UNDERSTAND -- Why do Dates and Times seem to be so much trouble? faq68-5827

But I must ask another question.

Will you never need or use the TIME component or do you just not want to not display the TIME component?

In the FORMER instance, you must change the underlying value by replacing the value with the integer value
[tt]
With MyCell
.Value = Int(.Value)
End With
[/tt]

In the second instance, change the NumberFormat property of the range

MyCell.NumberFormat = "yyyy/mm/dd"

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}