Hello everyone
I am working on scraping code and it is working well. Just one point that annoyed me and I couldn't figure it out
The dates that are on the website (I have attached a snapshot in the file) is different from the scraped dates .. The weird is that not all the time, the dates are different. Generally, it seems that the dates are treated as text not as dates. How can I convert the dates to be real?
I have tried DateValue / CDate / CDBL/Clng and many other methods with no use ..
Thanks advanced for help
Adjust dates to be real dates
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Adjust dates to be real dates
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 7209
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Adjust dates to be real dates
I think the error you are getting is due to the difference between American and non-American date formats.
09-01-2020 is imported by Excel as September the first, and so decoded to 01/09/2020
15/10/2020 can not be decoded similarly as there aren't 15 months in the year, and so remains 15/10-2020
I had a similar issue some years ago when importing a CSV - the only workaround I found was to rename the .csv file as a .txt file, and then import it as a delimited file.
** Note that in column A, the dates that are converted have '-' separators and the others have '/'.
09-01-2020 is imported by Excel as September the first, and so decoded to 01/09/2020
15/10/2020 can not be decoded similarly as there aren't 15 months in the year, and so remains 15/10-2020
I had a similar issue some years ago when importing a CSV - the only workaround I found was to rename the .csv file as a .txt file, and then import it as a delimited file.
** Note that in column A, the dates that are converted have '-' separators and the others have '/'.
Leif
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Adjust dates to be real dates
The dates are in European dd/mm/yyyy format, but your code has tried to interpret them as USA mm/dd/yyyy format. Where this is possible, the value has become a date, but with day and month reversed (for example, the 1st of September becomes the 9th of January). Otherwise, it has become a text value (for example, 15/10/2020 is not a valid mm/dd/yyyy date).
Perhaps your code can take into account that the dates are dd/mm/yyyy.
Perhaps your code can take into account that the dates are dd/mm/yyyy.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Adjust dates to be real dates
The code that I created grabs the whole table using selenium VBA .. so there is no chance fixing that except on the worksheet itself. is it possible to adjust the dates in that case?
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Adjust dates to be real dates
Try this:
Code: Select all
Sub CorrectDates()
Dim a
Dim m As Long
Dim i As Long
Dim v
Application.ScreenUpdating = False
m = Range("A1").End(xlDown).Row
a = Range("A2:A" & m).Value
For i = LBound(a) To UBound(a)
v = a(i, 1)
If TypeName(v) = "Date" Then
a(i, 1) = DateSerial(Year(v), Day(v), Month(v))
Else
a(i, 1) = DateSerial(Right(v, 4), Mid(v, 4, 2), Left(v, 2))
End If
Next i
Range("A2:A" & m).Value = a
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Adjust dates to be real dates
Amazing my tutor. That fixed the dates in a great way.