Problem of wrong date format when scraping date content from web by VBA Selenium

user_11391323 0
Viewed 252

I try to scrape date underformat of "dd/mm/yy" from web to excel by VBA Selenium library.

Sheets("Sheet1").range("A1").value = driver.FindElementById("table-body-scroll").FindElementsByTag("tr").FindElementsByTag("td")(3).Text ' to get the 3rd col

The date value on web is formated as "dd/mm/yyyy" but when I scrape them to Excel, it turns to mm/dd/yyyy How to force Excel to understand the date as "dd/mm/yyyy" The url I navigate is: https://iboard.ssi.com.vn/bang-gia/chung-quyen The column contains the date I get is the 3rd col named "GDCC"

selenium-chromedriver vba excel
2 Answers
Dim DateAry() As String
DateAry = Split(driver.FindElementById("table-body-scroll").FindElementsByTag("tr").FindElementsByTag("td")(3).Text, "/")
Range("A1").Value = DateAry(1) & "/" & DateAry(0) & "/" & DateAry(2)
Range("A1").NumberFormat = "dd/mm/yyyy"

or

Dim DateAry() As String
DateAry = Split(driver.FindElementById("table-body-scroll").FindElementsByTag("tr").FindElementsByTag("td")(3).Text, "/")
Range("A1").Value = DateAry(1) & "-" & DateAry(0) & "-" & DateAry(2)
Range("A1").NumberFormat = "DD-MM-YYYY"
user_121702600

I like to use this function I wrote. It returns a date object. Handles the year being 4 or 2 numbers long (i.e 2020 or 20). And doesn't care which "-" or "/" separator you use.

Function convert_date_to_uk(dte)
Dim days, months, weeks As String

    months = Left(dte, 2)
    If Len(dte) = 10 Then
        years = Right(dte, 4)
    Else
        years = Right(dte, 2)
    End If
    days = Mid(dte, 4, 2)
    
    convert_date_to_uk = DateSerial(years, months, days)
    
End Function

Then it can be used as follows with your example

Sheets("Sheet1").range("A1").value = convert_date_to_uk(driver.FindElementById("table-body-scroll").FindElementsByTag("tr").FindElementsByTag("td")(3).Text)
user_33482640
Related