changing a string date into a 'date'

[Guest]

I usually work on Access databases but i was given an excel worksheet that
was exported from Sage accounting software so that the acountants could work
with it. HOwever it seems to be exporting dates in a string format that we
just cant change into a date.

so i tried to make a sub that can be called from a macro which takes a range
of cells and reformats them into dates.

Dim oldDate As String
Dim NewDate As Date
Dim day As Integer
Dim month As Integer
Dim year As Integer

For Each c In Range(ActiveWindow.RangeSelection.Address)
oldDate = c.Value
day = Left(oldDate, 2)
month = Mid(oldDate, 4, 2)
year = Right(oldDate, 4)
NewDate = day + "/" + month + "/" + year
Next c

End Sub

However im getting the following error:
Type Mismatch and its highlighting that last line 'Newdate = ....'
when i look at the variables i can see that htey are holding the values i
want but i cant rebuild it into a date, any help on this??

with much thanks
Amit

 

[Bill Martin]

I usually work on Access databases but i was given an excel worksheet that
was exported from Sage accounting software so that the acountants could work
with it. HOwever it seems to be exporting dates in a string format that we
just cant change into a date.

so i tried to make a sub that can be called from a macro which takes a range
of cells and reformats them into dates.

Dim oldDate As String
Dim NewDate As Date
Dim day As Integer
Dim month As Integer
Dim year As Integer

For Each c In Range(ActiveWindow.RangeSelection.Address)
oldDate = c.Value
day = Left(oldDate, 2)
month = Mid(oldDate, 4, 2)
year = Right(oldDate, 4)
NewDate = day + "/" + month + "/" + year
Next c

End Sub

However im getting the following error:
Type Mismatch and its highlighting that last line 'Newdate = ....'
when i look at the variables i can see that htey are holding the values i
want but i cant rebuild it into a date, any help on this??

with much thanks
Amit

-----------------------------

You're trying to force text into a variable you've defined as a date type.
Instead try:

NewDate = DateSerial(year, month, day)

Bill

 

[Guest]

Try:

NewDate=Datevalue(OldDate)

 

[Norman Jones]

Hi Amit,

You have omitted to indicate the string date format that you want to
convert.

 

[Guest]

excellent,
that was really helpful, i knew there had to be a function somewhere but i
just couldnt find it all afternoon!

with much thanks.

Amit