Forum Discussion

Itzel_Samaniego's avatar
Itzel_Samaniego
Copper Contributor
Dec 11, 2023

Leading zeros

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 

 

 

excel
Excel on Mac
Formulas and Functions
  • LouisDeconinck's avatar
    LouisDeconinck
    Brass Contributor
    Dec 11, 2023

    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)

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 11, 2023

    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.

Resources