Two digit format for hh:mm:ss

Helen Rim

Hi all,

I researched through numerous posts and threads regarding calculating time but just couldn't figure out one last part. I hope it's an easy one for some experts here.

I am trying to calculate the average of a range of time and display it in hh:mm:ss format. By reading through the posts here, I was able to figure out that I need to convert the data into numbers first, average it, and then convert that number back to hh:mm:ss format. I got that working.

However, the outcome is not always displayed as a two-digit hour, two-digit minute, or two-digit second. I need to have it show 0 in front if it's a single-digit number but I am stumped!

My average formula is as follows: [Column36]26 is the average number. I am trying to convert that to hh:mm:ss

=(INT([Column36]26 / 3600) + ":") + (INT(([Column36]26 - INT([Column36]26 / 3600)) / 60) + ":") + ROUND(([Column36]26 - (INT([Column36]26 / 3600) * 3600) - (INT(([Column36]26 - (INT([Column36]26 / 3600) * 3600)) / 60) * 60)), 0)

Depending on the answer, some will show 0:17:11 or 0:5:22 or 0:1:7.

Please help!

Paul Newcome

You would need to adjust the formula that is outputting it in hh:mm:ss. It has nothing to do with the actual average column.

=IF(number < 10, "0", "") + number

Basically you use an IF statement to say that if the number is less than ten, output a "0" (being in quotes is important) then "add" the number to it.

You can replace number with a formula or a cell reference as needed.

Helen Rim

@Paul Newcome - thank you! I was able to follow and got most of it working.

Question is, what if the minute number is greater than 0?

For example, my numeric avg was 346.62, so when I applied the IF statement, it didn't show two digits but came out to 00:5:47.

Paul Newcome

You would have to use that logic for each of the number sets. Are you able to copy/paste the formula you are currently using?

Helen Rim

I think I figured it out! I put a "0" instead of "" in the IF statement for the minute portion which created the two digits. My formula is as follows - please feel free to suggest any improvements!

=(IF(((INT([Primary Column]27 / 3600) + ":")) < 10, "0", "") + ((INT([Primary Column]27 / 3600) + ":"))) + (IF(((INT(([Primary Column]27 - INT([Primary Column]27 / 3600)) / 60) + ":")) < 10, "0", "0") + ((INT(([Primary Column]27 - INT([Primary Column]27 / 3600)) / 60) + ":"))) + (IF((ROUND(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600) - (INT(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600)) / 60) * 60)), 0)) < 10, "0", "") + (ROUND(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600) - (INT(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600)) / 60) * 60)), 0)))

Sharing this in case anyone is looking for a similar solution.

Thank you so much!

Helen Rim

Acutally... I spoke too soon! 🤣

When the average number goes above 600 min, it does not calculate the hh:mm:ss properly. I gives me a three-digit minute answer.

Ex: 600.2 average number

Using the formula above: 00:010:00

Any suggestions? Again, the formula that I am using is:

=(IF(((INT([Primary Column]27 / 3600) + ":")) < 10, "0", "") + ((INT([Primary Column]27 / 3600) + ":"))) + (IF(((INT(([Primary Column]27 - INT([Primary Column]27 / 3600)) / 60) + ":")) < 10, "0", "0") + ((INT(([Primary Column]27 - INT([Primary Column]27 / 3600)) / 60) + ":"))) + (IF((ROUND(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600) - (INT(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600)) / 60) * 60)), 0)) < 10, "0", "") + (ROUND(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600) - (INT(([Primary Column]27 - (INT([Primary Column]27 / 3600) * 3600)) / 60) * 60)), 0)))