Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Date string to date format | Excel Discussion (Misc queries) | |||
Changing the date format on the date table in a chart | Charts and Charting in Excel | |||
Cell contents changing to date and not date | Excel Worksheet Functions | |||
When I open my past invoice it keeps changing date to todays date | Excel Worksheet Functions | |||
Converting a string date into a Excel Date | Excel Programming |