Leading zeros

[Itzel_Samaniego]

Hello all!

I have a sheet on which I paste from a website. The values are times with three spaces for hours (hhh:mm:ss), the problem is that I need to sum up the values in a column, and for some reason, I can't get Excel to do it without removing the first zero from the time. I've tried the custom format [hhh]:mm:ss and made a few changes to my PC's region and whatnot and it still doesn't work! Please help, this is driving me nuts 

[#2]

@Itzel_Samaniego 

You can use a formula such as

=SUM(IF(A1:A10<>"",TIMEVALUE(MID(A1:A10,2,8))))

If you do not have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Apply the custom number format [h]:mm:ss or [hh]:mm:ss to the cell(s) with the formula.

[#3]

@Itzel_Samaniego You can remove the leading zero using the following formula, when doing this Excel will automatically recognize it as a time value.

=RIGHT(A1,LEN(A1)-1)