format for doing Hundredths of a second in Excel

Posted on 1998-06-15
Medium Priority
Last Modified: 2008-02-01
Is there a way to format an excel cell to track Minutes:seconds:hundredths of a second. This is for keeping times for a track team.
Question by:hmharvey

Expert Comment

ID: 1609591

Sorry, but to the best of my knowledge there is no built in method to accomplish this in Excel.

It may be able to be done using a macro depending on what you want to do. The only way I can think of off hand would be a macro that changes your entries into decimal seconds and stores the result in another sheet but then displays the result as text in the format you want on your main sheet. The macro would then look to the other sheet for numbers to  do calculations and then update the text entries displayed in your format. Depending on what you're doing with the numbers, it could get tricky.

If you are just imputting them for display purposes (no calculations or graphs, etc.) then entering them as text would work.

Wish I had a better answer for you, but I don't.  Maybe someone else will.


Accepted Solution

the_whelk earned 100 total points
ID: 1609592
You can achieve this in the formatting of the cells as follows.

Number format codes for dates and times

·      To display days, months, and years, include the following format codes in a section. If you use m immediately after the h or hh format code, Microsoft Excel displays the minute instead of the month.

To display      Use this format code

Months as 1-12      m
Months as 01-12      mm
Months as Jan-Dec      mmm
Months as January-December      mmmm
Days as 1-31      d
Days as 01-31      dd
Days as Sun-Sat      ddd
Days as Sunday-Saturday      dddd
Years as 00-99      yy
Years as 1900-2078      yyyy
·      To display hours, minutes, and seconds, include the following format codes in a section.

To display      Use this format code

Hours as 0-23      h
Hours as 00-23      hh
Minutes as 0-59      m
Minutes as 00-59      mm
Seconds as 0-59      s
Seconds as 00-59      ss
Hours as 4 AM      h AM
Time as 4:36 pm      h:mm pm
Time as 4:36:03 p      h:mm:ss p
Hours as 25.02      [h]:mm
Minutes as 63:46      [mm]:ss
If the format contains an AM or PM, the hour is based on the 12-hour clock, where AM, am, A, or a indicates times from midnight until noon, and PM, pm, P, or p indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock. The m or mm must appear immediately after the h or hh format code, or Microsoft Excel displays the month rather than the minute.
You can also use the decimal point to create time formats that display fractions of a second.

To display hours greater than 24, or minutes or seconds greater than 60, place brackets around the leftmost part of the time code. For example, the time code [h]:mm:ss allows the display of hours greater than 24.

As it says above you can achieve a display showing fractions of a second 1/100ths by using a decimal point in that formatting.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question