Hi,

I have a dataset I extract from another tool and import into Excel. The
dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
programmatically change this to be just dd/mm/yyyy format in the columns
containing the dates. I've tried looping through and using datevalue() and
just trimming the contents to only the first 10 characters but neither work
for me, can anyone send me a snippet of code to get the job done?

Regards

Jonathan

Jonathan,
Date/time is stored as a double in Excel; the whole number part is the date
and the fractional part is the time. So if you remove the decimal part, you
have a date only - or rather a midnight on that day.

=INT(A1)

NickHK

"Jonathan" <(E-Mail Removed)> wrote in message
news:938AD718-1E1E-4185-A728-(E-Mail Removed)...
> Hi,
>
> I have a dataset I extract from another tool and import into Excel. The
> dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
> programmatically change this to be just dd/mm/yyyy format in the columns
> containing the dates. I've tried looping through and using datevalue() and
> just trimming the contents to only the first 10 characters but neither
work
> for me, can anyone send me a snippet of code to get the job done?
>
> Regards
>
> Jonathan

Jonathan,

Try this with the range altered to suit:-

Sub stantial()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:A1000") '<======Alter to suit
For Each c In myRange
c.Value = Int(c.Value)
Next
End Sub

Mike

"Jonathan" wrote:

> Hi,
>
> I have a dataset I extract from another tool and import into Excel. The
> dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
> programmatically change this to be just dd/mm/yyyy format in the columns
> containing the dates. I've tried looping through and using datevalue() and
> just trimming the contents to only the first 10 characters but neither work
> for me, can anyone send me a snippet of code to get the job done?
>
> Regards
>
> Jonathan

I'll give it a try, I managed to get it sorted using a clunky loop to convert
the dates to the decimal number format and then change the cell format to
dd/mm/yyyy, it also allows me to deal with the cells that have the text
<void> instead of a valid date from the other tools output, still falls over
as soon as it encounters a completely blank cell but this only occurs at the
end of the imported data ;-)

"Mike H" wrote:

> Jonathan,
>
> Try this with the range altered to suit:-
>
> Sub stantial()
> Dim myRange As Range
> Set myRange = Worksheets("Sheet1").Range("A1:A1000") '<======Alter to suit
> For Each c In myRange
> c.Value = Int(c.Value)
> Next
> End Sub
>
> Mike
>
> "Jonathan" wrote:
>
> > Hi,
> >
> > I have a dataset I extract from another tool and import into Excel. The
> > dates from this toolset are in dd/mm/yyyy hrs/min/sec format, I need to
> > programmatically change this to be just dd/mm/yyyy format in the columns
> > containing the dates. I've tried looping through and using datevalue() and
> > just trimming the contents to only the first 10 characters but neither work
> > for me, can anyone send me a snippet of code to get the job done?
> >
> > Regards
> >
> > Jonathan