I have a cell with the following content :
The cell is date formated
Then I copy the value and put it in my module class :
Set Line = New CTravelLine Line.Date= Cells(1, 8).value
Everything works fine until the moment I put this value in another cell :
The value 01/02/2015 becomes 02/01/2015. I am using this format (dd/mm/yyyy). I have the impression that when the days are numerically lower than the month, the 2 values are reversed. The values are reversed whatever the method I tried :
method 1 :
Dim WrdArray() As String, datetest As String WrdArray() = Split(travelLine.Date, "/") datetest= WrdArray(0) & "/" & WrdArray(1) & "/" & WrdArray(2) Cells(5, 5) = datetest
method 2 :
Cells(5, 5) = travelLine.Date
I don't understand how I can solve this problem. Thanks in advance for your help.
This might have happened due to 'Regional formatting problem'.
Excel has a habit of forcing the American date format (mm/dd/yyyy) when the dates have been imported from another data source. So, if the day in your date happens to be 1 - 12, then Excel will switch the date to mm/dd/yyyy.
When dates are imported from a text file, there is an option in the VBA code to apply regional format which corrects this problem.
Change number format of date column in excelsheet from 'date' format category to 'text'; save it. (After Saving run the VBA Code if you have any. Now check whether the date format is 'text' or changed back to 'date'.)
If it has changed back to 'date' try to fix it as 'text'
If it's 'text'; Correct the erroneous date cells and save the excel sheet. This will make dates not to change automatically to American Format.