ParseDate Error (dateModified)

#1

Hello
I would like to extract dateModified from https://www.tarifdetektiv.de/internet/internet-tarife/ and display the date in German date format (DD.MM.YYYY) .
The formula = RegexpFindOnUrl (A2; "dateModified" ":" "([0-9] {4} - [0-9] {2} - [0-9] {2})"; 1) works. Unfortunately, the date format cannot be converted. Error: invalid format
See screen. Does anyone have an idea what is wrong? or is there another way to change the date format?

Thanks and Greetings
Chris

#2

Hi,

The input string should match the string you're trying to parse, that is "yyyy-MM-dd", and then you can format it however you want with the Excel support.

Does that work?

#3

Thanks for the answer. I have found the solution. The formula must start with = TEXT(

=TEXT(RegexpFindOnUrl("https://www.tarifdetektiv.de/internet/internet-tarife/";"dateModified"":""([0-9]{4}-[0-9]{2}-[0-9]{2})";1);"TT.MM.JJJJ")

#4

Nice solution, but ParseDate should work also :slight_smile:

#5

The problem seems to be in the German version of Excel. ParseDate (A1; "yyyy-MM-dd") returns the same result as DATEVALUE.

DATEVALUE

#6

That means it works, all dates are stored as a number in Excel (number of days since 1900-01-01). You can chose a custom date format for this cell.