Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Date Conversion Code
I have the below code that is part of an existing macro to change my date in
Column C from this odd format of Y/MM/DD to MM/DD/YYYY however it is not calculating correctly. Here are a few examples of the coversion after the macro is run "60526" converted to "26/05/2006" "60523" converted to "23/05/2006" I need to change where the month is to where the day is and where the day is to the month. With Selection Dim RowNum As Integer Dim NextValue As String Dim NextDate As Date For RowNum = 2 To Range("C1").CurrentRegion.Rows.Count NextValue = Range("C" & RowNum).Value NextDate = DateSerial(2000 + Mid(NextValue, 1, 1), Mid(NextValue, 2, 2), Mid(NextValue, 4, 2)) Range("C" & RowNum).NumberFormat = "dd/mm/yyyy" Range("C" & RowNum).Value = NextDate Next RowNum End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Date Conversion Code
Range("C" & RowNum).NumberFormat = "mm/dd/yyyy"
HTH -- AP "JOUIOUI" a écrit dans le message de news: ... I have the below code that is part of an existing macro to change my date in Column C from this odd format of Y/MM/DD to MM/DD/YYYY however it is not calculating correctly. Here are a few examples of the coversion after the macro is run "60526" converted to "26/05/2006" "60523" converted to "23/05/2006" I need to change where the month is to where the day is and where the day is to the month. With Selection Dim RowNum As Integer Dim NextValue As String Dim NextDate As Date For RowNum = 2 To Range("C1").CurrentRegion.Rows.Count NextValue = Range("C" & RowNum).Value NextDate = DateSerial(2000 + Mid(NextValue, 1, 1), Mid(NextValue, 2, 2), Mid(NextValue, 4, 2)) Range("C" & RowNum).NumberFormat = "dd/mm/yyyy" Range("C" & RowNum).Value = NextDate Next RowNum End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Static Date and Time code problem | Excel Discussion (Misc queries) | |||
Date Conversion Problem | Excel Discussion (Misc queries) | |||
problem regarding date conversion | Excel Programming | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) | |||
Date problem from code to text box to spreadsheet | Excel Programming |