I have a column of time durations in an Excel sheet that is destroying my week. I wish I could make a custom hhh:mm format for this column, but I am only allowed to edit this sensitive document on SharePoint and Sharepoint does not support custom formats. Consequently, this is the best I can do for time duration:

Works fine. Most of the rows use this format. But inexplicably, many of the rows have this format instead:

This format difference messes with formulas, and as you can you see in the picture the time “duration” is different in the formula box. The time as it appears on the sheet is correct This format resists all attempts at change, and I’ve looked up quite a few solutions. Reformatting the whole column has no effect. Copy pasting the values in various ways either retains the problem or produces incorrect results. The Text to Columns fix I’ve read about in a few places gives me the time as it appears in the formula bar, not the (correct) time as it appears in the sheet.

I am extremely desperate for a fix. I appreciate whatever help you can provide. Please keep in mind that I cannot use custom formats on Sharepoint and I cannot edit this document elsewhere.