Adjust dates to be real dates

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Adjust dates to be real dates

Post by YasserKhalil »

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
You do not have the required permissions to view the files attached to this post.

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Adjust dates to be real dates

Post by Leif »

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 '/'.
Leif

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adjust dates to be real dates

Post by HansV »

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.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Adjust dates to be real dates

Post by YasserKhalil »

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?

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adjust dates to be real dates

Post by HansV »

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

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Adjust dates to be real dates

Post by YasserKhalil »

Amazing my tutor. That fixed the dates in a great way.