Time column is in decimal seconds from some completely random timebase, which I took one calibration of.
Created a column with format "Time" of type hh:mm:ss. Formula for that column was:
=TIMEVALUE(TEXT("8:21:00 AM"+(A2 - 906897)/86400, "h:mm:ss"))
i.e. A2 contains a time in seconds, at 8:21 AM the seconds were 906897, and the conversion factor is expecting decimal hours (86400 seconds per hour). Converting it with TIMEVALUE produces fraction of a day, but that is then displayed properly due to the cell formatting.
This does not address the case of crossing a date boundary. Date conversion functions are not useful because the only cell formats available for date do not include seconds.
