Importhtml on google sheet converted data to a different format

[Abdmujib]

I have sport data that I imported from an api

There is a particular column named G in which, two data are combined and partitioned with a colon ( : ) (attached is the pic)

So when the column is imported into the sheet, it changes format to hh:mm:ss (time format) e.g 56:45:00.

Kindly check the 2nd row of the column, the initial data was 106:65, but the imported record has been round up to 107:05:00. For every number greater than 60, it is converted in to 1 min and added it to the hh.

For the table that has not entered 100, i.e 93:90 won't be converted to hh:mm:ss format, i believe it will be mm:ss.

That's what is challenging for me, so that's why I need a solution to importhtml function to bring exact data

Please kindly provide a way around it. Thanks

 

[Alex Blakenburg]

This is a duplicate of the post:

IMPORTHTML return data that has column has partition as a date format

I have sport data that I imported from an api # Team MP W WO LO L G Pts Form 1. Red Eagles Hokkaido 26 20 2 0 4 110:55 64 2. Nikko Ice Bucks 24 15 0...

stackoverflow.com

I don't know much about Google Sheets or Ice Hockey and I can't find a glossary on the web site you are pulling the data from which might be this one or a variation of it:

Japan Cup 2024/2025 scores, Ice hockey Japan - Flashscore

Japan Cup 2024/2025 scores on Flashscore.co.za offer livescore, results, Japan Cup 2024/2025 standings and match details.

www.flashscore.co.za

If I had to guess it isn't time at all. Could it be for and against Goals ?
I Power Query I would be reading it in as text, I don't know if Google Sheets has a similar option or not.
It is likely that just having as text will suffice what you need. It seems unlikely that you would want to split it into 2 columns.

 

[Abdmujib]

This is a duplicate of the post:

IMPORTHTML return data that has column has partition as a date format

I have sport data that I imported from an api # Team MP W WO LO L G Pts Form 1. Red Eagles Hokkaido 26 20 2 0 4 110:55 64 2. Nikko Ice Bucks 24 15 0...

stackoverflow.com

I don't know much about Google Sheets or Ice Hockey and I can't find a glossary on the web site you are pulling the data from which might be this one or a variation of it:

Japan Cup 2024/2025 scores, Ice hockey Japan - Flashscore

Japan Cup 2024/2025 scores on Flashscore.co.za offer livescore, results, Japan Cup 2024/2025 standings and match details.

www.flashscore.co.za

If I had to guess it isn't time at all. Could it be for and against Goals ?
I Power Query I would be reading it in as text, I don't know if Google Sheets has a similar option or not.
It is likely that just having as text will suffice what you need. It seems unlikely that you would want to split it into 2 columns.

Yeah, I was the one that posted it there as well.

the stats are for and against.

 

[Alex Blakenburg]

Like I said Power Query would handle it fine but unless there is new functionality there seems to be no way to tell Google Sheets to import a column as text.
If you want to Google it then use date rather than time in your search criteria, it's basically the same issue. Example in link below.

IMPORTHTML incorrectly formatting as dates - Google Docs Editors Community

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.