Two digit format for hh:mm:ss
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 twodigit hour, twodigit minute, or twodigit second. I need to have it show 0 in front if it's a singledigit 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!
Answers

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.

@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.

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?

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!

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 threedigit 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)))
Help Article Resources
Categories
Check out the Formula Handbook template!