I have set up a simple Goolge Sheet that uses IMPORTXML to extract timestamp data using this XPath
/html/body/section/article/section/div/div[2]/div/div[2]/div/div[2]/span[2]
for article URLs from this website (https://earlygame.com/). With this formula
=IMPORTXML(B2,$K$2)
it is able to take the URL in B2 and will produce a result that looks like this: "October 6th 2023, 17:22 GMT+2"
.
The date is accurate but Google Sheets doesn't recognize the date as an actual date value and so I can't do any conditional formatting (Ideally I want to put something like, if older than 1 month highlight red etc.)
I get this error "parameter '17:22 GMT+2 ' cannot be parsed to date/time." So i tried to remove time and timezone with this formula
=DATEVALUE(MID(C2, 1, FIND(",", C2) - 1))
so that it would only use the date, but then i got "DATEVALUE parameter ' October 6th 2023' cannot be parsed to date/time".
Does anyone have any suggestions or workarounds?
You can use REGEXREPLACE
to remove the parts of the string that make it an invalid date.
Note that this also removes the information about the time and the timezone.
=REGEXREPLACE(A1,"st|nd|th|,.+",)