Excel format for time in minutes, seconds 3

[toolmantwo]

OK I work with Excel 97 SR-2.

When I collect time with a stop watch for production events I get the time as example 2:34 (2 minutes, 34 seconds). When I want to format the cells to enter the data I cannot get the format to be correct. I would like to enter just 2:34 and hit enter on the cell for the input.

The only way I can get close to this desired format is to format the cell to " mm:ss.0 ". Then I have to enter 02:34.0 so it is correct. If I just enter 2:34 get fills the cell display with 2:34 AM in the (View) formula bar (at top of screen) and the cell display is just "34:00.0"

Even when the cell display is Ok with 02:34.0 the (View)formula bar shows " 12:02:34 AM ". This works for me and I can enter the 02:34.0, but this slows me down and I do enjoy being more productive. Is there any way I can get what I want?

I tried a few other things with time format and they did not work.

Any input is appreciated.

 

[pmover]

XL97? it has been awhile, but you will need to format the cells using a time format - probably a custom format.

from the "format-cells" menu, investigate of there are any time formats. if so, select the format of your desire. if not, try a custom format using mm:ss format.

good luck!
-pmover

 

[analogkid2digitalman]

TMT:
If the actual time value is not needed for calculations, you could format the cell as text.

-Pete

 

[aviat]

I find the fastest way is not to bother with the formatting. I use a seperate column for each and then add the two togeter in column c. Format c to number-one decimal. Divide the seconds by 60 to get it in decimal form.
You can sum column c to get total time
minute second C
2 34 =A4+(B4/60)
2 34 2.57
To get the total in hours, minutes, seconds, you can use these formulas: (total is in c17)
hour =IF(INT(C17>60),(INT(C17)/60),0) in e19
minutes =IF(E19>0,(E19-INT(E19))*60,INT(C17)) in e20
seconds =(C17-INT(C17))*60 in e21
=INT(E19)&"h:"&INT(E20)&"m:"&INT(E21)&"s"
Example 2h:14m:53s
Hope this makes sense to you!

 

[joerd]

Aviat,

It would be more convenient if you use the Excel time system, so you can use Excel's time functions, time format, etc. To convert minutes/seconds to an Excel time value, convert it to fractions of the day: = hours/24 + minutes/(24*60) + seconds/(24*3600)
so in this case, in C17: =A17/(24*60)+B17/(24*3600)
Then format C17 as mm:ss (or whatever has your preference). This has many advantages, for example if you later want to display hours as well because the total is more than 60 minutes, you only have to change the cell formatting.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[aviat]

jeord,
You are right in that in this case it is better to format column C in time units. I have used a formula similat to yours in the past, but at present most of the time I need the time to be in decimal format.

Cheers!

 

[toolmantwo]

Thanks for the suggestions guys.

Jeord and aviat, I have done some spreadsheets this way, yet it seems Excel should have the format time choice correct "right out of the box".

Using the current format as "mm:ss" ;In my view if we must enter the leading zero and the one decimal and trailing zero each time is poor and a waste of engineering time. This also takes away concentration and you are more likely to pump in an error.

When using time, to get by I have converted all time to .0000 of hours and then back to hours, minutes, and seconds so the summary will look better on totals, averages, and medians.

When we must build these work around fixes just to get the job done seems like a poor use of resources. To proof read a set of data and summary report is longer than it should be. We are spending more time on tasks that the customer must pay for that is due to the fact we have poor software and shortcomings of that software. Think of what your customer would think if they knew exactly what is done for their money.

Does anyone know of other software that is better for time formating?

 

[IRstuff]

From what you describe, the simplest solution is to preformat entry column as text and to use another column to convert back to time. You enter time as you desire to and use the second column to convert to Excel's mm:ss format

Since you now do that anyway, you have not lost anything on that end, but you save on not having to convert to decimal time on the front end.

TTFN

 

[jghrist]

If you're going to add a column for entry and convert, why not just enter the time in mmss without any punctuation then convert to time in another column? For instance in A1 enter 234 for 2min 34sec and put =(INT(A1/100)+(A1-100*INT(A1/100))/60)/60/24 in B1 and format as mm:ss.

This would minimize entry time.

 

[toolmantwo]

jghrist Thanks, that is worth a star. I will try that for entry. There might be a small chance of a entry error, but I think I build some if then statements for checking on those type errors.

I like to save keyboarding because 1. I am not fast, 2. I do not like tons of entry (voice recognition for input would be great). I wonder if Microsoft is getting any feedback on their product (Excel)