How to convert Excel Time format to a string when importing to collection?

[mloru]

I'm trying to import an excel file using the collection() method. One of the colums of this file contains a time interval string with the format H:MM:SS (e.g. "0:24:46"), but for some reasons I found out that the rows have two different formats for it:

1. some values had been written as bare text (so they're formatted as number, and the Excel formula bar shows an apostrophe before the value): e.g.: '26:58
2. some others cells are formatted as Time, and the Excel formula bar shows the value as HH:MM:SS: e.g. 00:26:58

When importing, I explode the cell value by the ":" character to know the number of hours, minutes and seconds.
The result is, that when a cell matches the the first case, the value is imported correctly. In the second case, the value is imported as double and I can't correctly handle it.
How can I detect and correctly parse the cells formatted as Time, so I can convert them to the same format as case 1?

[peterjthomson]

Your milage may vary, but for what it's worth, this is number helper we created to solve a similar problem with importing dates from Excel and saving them as Carbon dates:

/Custom number format that takes a date from Excel or a string with a long year or a short year and turns it into a carbon date/

if (! function_exists('parse_date_helper')) {
    $now = \Carbon\Carbon::now();
    function parse_date_helper($value, $format = 'd/m/y')
    {
        try {
            return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
        } catch (\ErrorException $e) {
            if (\Carbon\Carbon::createFromFormat('d/m/Y', $value)->diffInDays(\Carbon\Carbon::now()) <= 100000) {
                return \Carbon\Carbon::createFromFormat('d/m/Y', $value);
            }

            return \Carbon\Carbon::createFromFormat('d/m/y', $value);
        }
    }
}