Format cell for elapsed time

Topic

New Reply

Greetings everyone!

I have a spread sheet where I am tracking phone stats for a help desk organization. Specifically, I am tracking the average time to answer and the average duration time of calls. I am entering time in minutes and seconds, but Excel seems to have issues with formatting the numbers correctly.

I have done some research, and have found these two cell formats.

[h]:mm:ss

and

h:mm:ss

First of all, what’s supposed to be the difference between those two?

And secondly, I’ve been using the first one and it appears that if I want to enter “1 minute, 25 seconds”, I have to type it out as “0:01:25”. If I just type “1:25”, the formatting changes it to “1:25:00”, or 1 hour, 25 minutes.

Is there another (easier?) way to either format the cell or enter the information so that Excel understands what I’m talking about?

…or have I already found the best way?

Thank you!

Reply | Quote

Replies

Ed,

I did a little poking around and it looks like you’ve found the best way already.
I tried skipping the leading 0 for hours and it displayed ok but when I tried to =Sum()
a column of numbers they wouldn’t add up.

Depending on the volume of numbers you have to enter you could set up a form and some VBA code to streamline the operation where you’d have separate input areas for hours, minutes, seconds and just use the tab key to move between them then have the VBA code assemble it for entry into the cell. You could even have the form default to the minutes field because I doubt if you have many entries where Hours are a factor.

The form would also be good for error checking because if you enter 0:59:80 into a time field formatted [h]:mm:ss it will convert to 1:00:20.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

VBA huh?

A very interesting idea. I don’t have much VBA experience but it will give me something to look up and study.

Thanks RG! I’ll give it a try.

Reply | Quote

You could enter the H,M,S into 3 seperate columns and then compute the duration using the time function.
This will then give the actual duration

The Formula Syntax is =TIME(H,M.S)

IN ADDITION, the time function would support 73 min and correctly convert to 1 Hour 13 Min

The difference between the formats is

h:mm:ss Shows time of Day and if H exceeds 24 it will become 1 Day

[h]:mm:ss Shows time duratations so as long as you use this format throughout you will get total time in Hours Min Secs

See attached spreadsheet and image for an example using 3 columns to enter Data (If needed you could also hide the computed column).

I have used SUMIF and COUNTIF to calculate Average against the Computed Time Columns because
Average would consider the 0’s to be significant and give a false average.

IF you are using Excel 2007 you have an AVERAGEIF Function available to you

Reply | Quote