Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel format for time in minutes, seconds

Excel format for time in minutes, seconds

Excel format for time in minutes, seconds

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.

RE: Excel format for time in minutes, seconds

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!

RE: Excel format for time in minutes, seconds

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


RE: Excel format for time in minutes, seconds

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
Example 2h:14m:53s
Hope this makes sense to you!

RE: Excel format for time in minutes, seconds


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.


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

RE: Excel format for time in minutes, seconds

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.


RE: Excel format for time in minutes, seconds

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?

RE: Excel format for time in minutes, seconds

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.


RE: Excel format for time in minutes, seconds

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.

RE: Excel format for time in minutes, seconds

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close