I've been googling for hours and haven't been able to find a solution to this, but I've gotten so close to getting it that I feel it must be possible!!

I've got a column of Durations that I need to add together. The raw data I pull is in the format of "hh:mm:ss". As per one forum post I found, I figured out that I can change the format to "hh:mm:ss.0" to use the sum function, but in order for it to work I have to manually add ".0". This won't be possible because I've got thousands of entries to alter and I'd like to do this report every day.

I've tried looking for a way to add ".0" to every single entry, but the only thing I've been able to find is the CONCATENATE function. Using that, I was able to join my duration with a .0 at the end like I needed. I then pasted the values to get rid of the formula, but it won't take the correct format no matter what I do.

This is what I mean. I copied the data below. The first 2 cells are when I manually enter the ".0" after the duration, and the last 3 are when I use the CONCATENATE function.

Duration
0:00:23.0
0:01:47.0
0:03:30.0
0:00:02.0
0:00:46.0

It doesn't seem to recognize the formatting I'm trying to apply to the last 3 cells. If I simply double click the cell and step off, it suddenly takes on the right formatting, but again, I don't have the time to do that to thousands of cells every day.

Any ideas?