for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

lark-the-herald-angels-sing-im Dec 2018 1:56pm, 14 Dec 2018 6,277 posts	Quote Pin lark-the-herald-angels-sing-im Yep - in the html itself the times are stored as seconds (to facilitate sorting the table) but the visible values are strings which GoogleSheets clearly reads incorrectly. I can work around it, but would still like to manipulate the data on its way into the spreadsheet, rather than after it has arrived.
JCB Dec 2018 3:18pm, 14 Dec 2018 217 posts	Quote Pin JCB I wasn't aware of that functionality in Googlesheets. Pretty cool. I played around and found the same problems you did about those pesky values. Fortunately there is an attribute for the distances, times and dates. =IMPORTXML("https://www.runbritainrankings.com/runners/profile.aspx?athleteid=145945", "//td[@sorttable_customkey]/@sorttable_customkey") Unfortunately it's not a one-stop solution, as the resulting list has three values, but it might be of some assistance?
lark-the-herald-angels-sing-im Dec 2018 3:22pm, 14 Dec 2018 6,280 posts	Quote Pin lark-the-herald-angels-sing-im If you use google sheets query() function with it too it becomes immense. parkrun had to disable the scraping capability of google sheets because enough people were grabbing bucketloads of data from there using it.
lark-the-herald-angels-sing-im Dec 2018 3:28pm, 14 Dec 2018 6,281 posts	Quote Pin lark-the-herald-angels-sing-im Modified slightly to add [2] only brings in the time in seconds (it's the [2] custom sort key on each table row I think) =IMPORTXML("https://www.runbritainrankings.com/runners/profile.aspx?athleteid=145945", "//td[@sorttable_customkey][2]/@sorttable_customkey")
JCB Dec 2018 12:31am, 15 Dec 2018 218 posts	Quote Pin JCB Great improvement. If you still wanted the distances, you can get [1] in another column.
GregP Dec 2018 9:59am, 24 Dec 2018 109,898 posts	Quote Pin GregP Sure we've done this before but it's something I have to do every now and again and can never remember a simple way to do it from one time to the next: 'copper days' start at 6am - so 'today' doesn't finish until 5:59 tomorrow morning - 24/12/18 03:00:00 is really 23/12/18 24/12/18 15:00:00 is 24/12/18 25/12/18 03:00:00 is also 24/12/18 Anyone got a quick way to convert 'real' datetimes to 'copper' dates?
um Dec 2018 10:18am, 24 Dec 2018 686 posts	Quote Pin um Quick way? Set your locale to US Mountain time? Will go & play with time formats for a bit
um Dec 2018 10:31am, 24 Dec 2018 687 posts	Quote Pin um Greg - Excel stores days as integers (days since 1-jan-1900, I think), time as a decimal of the day. So 6 hours is .25 If you just take your dd/mm/yy hh/mm - 0.25 and reformat to the just dd/mm/yy, that shoud do it?
GregP Dec 2018 10:43am, 24 Dec 2018 109,900 posts	Quote Pin GregP Does that work Um? It's brilliant if it does.
um Dec 2018 11:07am, 24 Dec 2018 688 posts	Quote Pin um Does for me - as long as excel knows (& stores) your dd/mm/yy hh:mm as something like Date & time Stored as Formula Result Reformat as dd/mm/yy 25/12/18 05:52 43459.244444 =a1-.25 24/12/18 23:52 24/12/18