CODEWITHSUNDEEP
xpathgoogle-sheets
Aleksandar
Aleksandar

Reputation: 736

Google Sheet importXml by default converts time value in decimal value

I'm trying to get time value from html table by importXml google sheet function. When I set cell format to "plain text", for value "06:50"in html table I get this value "0.2847222222" in sheet cell. I'm using xpath "//tr[1]/td[1]".

How do I get the same value as in html table in plain text? Google "automatic" formatting displays correct value in "time" formatting but I just want plain text because I use it in script.

Upvotes: 1

Views: 1576

Answers (1)

user3717023
user3717023

Reputation:

Strings like "1:00" are automatically converted to a datetime object. Internally, dates and times are represented by a float on the scale 1 = one day, and this is what you get by directly imposing plain text format. To create a human-readable string, use text formula: 

=text(importxml(...), "HH:mm")

There are other variations like "hh:mm" or "h:mm": see the docs linked above.

Upvotes: 2

Related Questions