changing a string date into a 'date'
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 |
changing a string date into a 'date'
DowningDevelopments wrote:
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 |
changing a string date into a 'date'
Try:
NewDate=Datevalue(OldDate) "DowningDevelopments" wrote: 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 |
changing a string date into a 'date'
Hi Amit,
You have omitted to indicate the string date format that you want to convert. --- Regards, Norman "DowningDevelopments" wrote in message ... 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 |
changing a string date into a 'date'
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 "Bill Martin" wrote: DowningDevelopments wrote: 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 |
All times are GMT +1. The time now is 10:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com