21 thoughts on “Fractions of Seconds”

1. If you use custom formatting for the cell as: “h:mm:ss.00?, then the cell displays the correct value as “0:01:23.03? while the formula bar displays “12.01:23 AM”. I am using Excel XP if that makes any difference.

2. I tried it again, and when I click in the cell, I get the same results as you do. The hundreths are lost. It truncates the hundreths out of the number. It did not round up so “1:23.5? ended up “0:01:23.00?

3. Thanks for taking the time out to help me guys, it is appreciated. If this is happening with you then it must be an Excel thing in general and not my setup.

   It looks as though I am going to have to be extra careful and lock any cells that i put data into as the athletes are going to be rather upset if they see that the times they post are always rounded up or even worse down!!!

4. I was surprised to discover that the Excel (2003) Time() function truncates the seconds, so:

   TIME(11,9,42) – TIME(11,9,42.999) = 0

   Also you can’t enter a time format to show greater precision than milliseconds, and any decimal seconds entered after the third place are truncated.

   I think a small UDF would be useful where fractions of a second are required, e.g.:

   Function ETime(Hours As Double, Mins As Double, Secs As Double) As Double
   ETime = ((Secs / 60 + Mins) / 60 + Hours) / 24
   End Function

   This will always preserve fractional seconds, and will store fractions of a second less than 1 millisecond, even if you can’t display them.

5. I think the UDF is a good idea, though if other users are using the file to post their times, I would suggest using separate cells to enter hours, mins and seconds, and have the fourth cell using the ETime function above, with the cell number format set to “hh:mm:ss.00?.

   This would allow the last cell to be protected if desired, but would still allow viewing of the correctly formatted time. It would also remove the risk of truncating the seconds if a revision was made, as the ‘seconds’ cell would just be a ‘General’ or ‘0.00’ formatted cell. =:-)

6. So what about this little file:

   http://www.jkp-ads.com/downloadscript.asp?filename=timeeditor.zip

7. Doug that idea sounds good, the only thing is I cant see where the fractions of a second fit in. There seems to be the availability for say 1hr 38mins 10secs but nothing for the fractions e.g 1hr38m10s.50

   I cant seem to adapt the formula that you have provided to take this into account

8. Here’s another problem with Excel’s date system: Every day after February 28th 1900 is off by one day. This is due to the fact that any 100 year that is not evenly divisble by 4 is NOT a leap year. Excel’s programmers obviously didn’t do their homework very well when they devised this system. At some point someone realized this mistake and they came out with the “1904? date system which corrects it by making 1 = 01-Jan-1904.

9. In fact it was Lotus that made the error a loooong time ago. When MSFT was designing Excel, it decided to copy the bug in order to maintain compatibility with Lotus 123 models. Made sense in those days, given that 123 had the biggest marketshare back then.

10. Olly – just enter the seconds as 10.5.

    To get it to display you will need a custom format, go to the Format-cells dialog box, Number tab, select “custom” from the category list and enter “h:mm:ss.000? in the Type box.

    I had a quick look at oo Calc and Gnumeric. Both have avoided the problem with truncating the decimal part of the seconds, but oo Calc seems to have some issues of its own if you enter a time with more than 12 hours.

    For people who need to record fractional seconds easily and reliably, it might be worth considering using Gnumeric.

11. “This method is great for adding, subtracting, and generally doing other math operations on dates.”

    Unfortunately, that is not true. Microsoft made a very bad design decision when it implemented time as a fraction (I wonder if it was for compatibility with Lotus 123). Using a fraction for time exposes time management to all the limitations of floating point precision.

    Try this simple test.

    In G1 enter 3/1/2007. In H1 enter =G1+0.1. In H2 enter =H1+0.1 Copy H2 all the way down to H32.

    In I32 enter 3/4/2007 4:48. In J32 enter =(I32-H32). It will contain 4.36557E-11 not zero.

    Alternatively, format H32 with a Number format with 15 decimal places. It will contain 39145.199999999900000 and not 39145.2

12. I find it perfectly logical to do so. After all: time IS a fraction of a day.

    Indeed there are many problems with floating point arithmetic. It is certainly not suited for many financial applications. True (scaled) decimal arithemetic (as an option) is on my Excel wish list for many years already.

13. Dick

    You might consider just using a Custom Format for the cells set to
    0?:”00.00
    and then enter 123.03 which you can then manipulate later
    It’s only a slightly lateral way of working, but will certainly
    sort quite happily and is much easier to enter than messing with
    colons and fullpoints

14. Doug,

    I realised what I had done a few minutes after I had sent that last post!!

    Your UDF works and the spreadsheet looks good.

    Everyone, thank you very much for taking the time out to help me.

15. Tushar Mehta Said: “Microsoft made a very bad design decision when it implemented time as a fraction… Try this simple test….”

    The point here is that Excel relies on the format of the cell to identify a date. If, instead of using +0.1, you repeat your steps using temporal functionality e.g. +TIME(0, 144, 0), formatting the cells using an appropriate date format e.g. yyyy-mm-dd hh:nn:ss, then I think you’ll find the result is zero. BTW why doesn’t the DATEDIF function support hours/minutes/seconds? (to which the official answer of course is, What DATEDIF function?)

    To get back to the original question, I’d suggest that temporal functionality in Excel and VBA is accurate only to one second granularity. By leveraging double float you can achieve sub-seconds to an accuracy of 3 milliseconds but you’d stray beyond supported behaviour and as a punishment you’d have to write your own revised temporal functionality.

    Further consider that the sole temporal data type in the product is designed for ‘timestamp’ data i.e. instants. I rather suspect intervals are required here, in which case I’d suggest storing the data as an integer, using the smallest time granule required (perhaps milliseconds), with appropriate formatting for display purposes.

    Jamie.

    –

16. I’d like to use Excel for a cycling club Time Trial.
    In a time trial we start a master clock, then let the riders go at one minute intervals (there’s no zero’th rider). When they cross the line we subtract (start number * 1 min) from the elapsed time.

    I roughed out a work sheet and was hoping to be able to enter an accurate time using Ctrl+Shift+; (I’m sort of using Xcel as a stop watch!)
    But, regardless of how I format the cell, it only records hh:mm and not the seconds.
    Is there any way of entering an accurate, current time in a cell (other than typing it in)?

    Thanks

17. You colud make a macro and assign a hot key to it.

    If you don’t know how then follow this

    In Excel press Alt-F11.
    The Visual Basic Editor will open.
    In Visual Basic Editor (VBE) select the menu Insert then Module.

    Copy and paste the following subroutine into the VBE Module

    Sub RightNow()
    ActiveCell = Now
    End Sub

    Close the Visual Basic Editor
    From Excel press Alt-F8
    Highlite the RightNow macro
    Select Options…
    Set the Ctrl key combination you want to use to execute the macro
    Press OK and exit the Macro dialog
    Format your cells as HH:MM:SS
    Press your Ctrl key combo to insert the time into the active cell.

    Hope this helps.

    Also, checkout this Mr. Excel Videocast for using events. You can just use a mouse click to input the time. You will have to modify his example a tiny bit but I think you will get the idea.

    http://mrexcel.libsyn.com/index.php?post_id=217885

    If you need to time in fractions of a second the macro will need to be a bit more complicated. The following may give you a start down that path.

    http://www.dailydoseofexcel.com/archives/2004/06/28/timing-macros/

    Good luck

18. Hello all

    I just updated 2 of my Excel files:

    – a file with all major SWIMMING records: men/women – long course/short course – all distances – World/American/European/Oceania/African/Asian/Olympic/Championship/Belgian record. For each record: time/holder/nationality/city/country/date

    – a file with all major ATHLETICS records: men/women – outdoor/indoor – all events – World/American/European/Oceania/African/Asian/Olympic/Championship/Belgian record. For each record: time/holder/nationality/city/country/date

    Sources: various websites, including IAAF, FINA, Wikipedia, …

    I put it on my website, but the site is in Dutch, so feel free to email me if you want a copy via email. wimmekegielis@hotmail.com

    I also store the data in TM1 cubes (see http://www-306.ibm.com/software/data/cognos/tm1/); I am a BI consultant mainly working with TM1.

    Cheers,

    Wim Gielis
    AKA Wigi on various Excel boards

19. I use excel extensively for timing rowing trials and head of river races. I find if you enter the numbers as time values – h:m:s.00 (e.g. 0:2:33.45) it does store the fractions of a second. It only becomes a problem if you try to edit that value – but then you just have to enter the whole time again correctly.

    You can perform calculations (subtraction for start time from finish time to find race time) and this works perfectly OK.

    You need to set custom display formats as already mentioned (m:ss.00 normally works for most rowing activities).

    If you want to access the actual seconds value, you can also use the custom format [s] – this just displays the time as seconds only.

    Finally, thanks to the explanation of the first contributor (Dick Kusleika) – I realised if you multiply a time serial (with no date) by 86400 (number of seconds in a day) – this gives you the actual time in seconds – including any fractions you’ve entered. So then you can plot time values on a chart as well.

20. I believe the trick is to preformat the field before you put data in it. Unfortunately that is usually the opposite way we typically do things, and sometimes challenging especially if there are hundreds of rows of data. I used general as the format for the field that will contain the 2 times I am going to do math on and hh.mm.ss.000 as the field to hold the math result after the sourec fields are pasted into excel.
    some gotcha’s …
    If the source of the pasted fields is a text file format ie. notepad then excel will always change the formating of the time destination field to a time format and round the result. If the sorce of the past field is a TSO session (green screen, 3270 based format) the destination field remains as general and is vieable as hh:mm:ss.0000.
    Math can be done on the pasted fields into the result field as indicated above.

    The “text to columns” feature in excel always reformats the fields as hh:mm:ss.0 …
    So if you only need to paste a couple of cells you can do it, if you are transfering a file with many rows or columns you are basically out of luck.

21. Just to make things a bit more confusing, it is possible to use a custom format like mm/dd/yyyy hh:mm:ss.000 to get the time to display in the cell to miliseconds (example “01/01/2009 14:33:12.012?). However, should you be using VBA to do something like write this data out to a load file format for Oracle, and you use
    Format(Cells(Row, Col).Value, “mm/dd/yyyy hh:mm:ss.000?)
    you do not get the miliseconds, but rather get “01/01/2009 14:33:12.000?.

    This is with Excel 2003.

    It must be a feature.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.