Fractions of Seconds
Over on Swimming Splits, Olly asks:
I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.
When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?
Good question. I’m not sure that I know this answer definitively, but I’ll tell you what I think based on what I see.
Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It’s only 8:00AM and already a third of the day is gone.
This method is great for adding, subtracting, and generally doing other math operations on dates. What it’s not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you’ve formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren’t displayed in the formula bar.
If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don’t know of any way to get the hundredths to display in the formula bar.
It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn’t seem to be a trade-off here that I can see (other than coding time by developers).
Murray Shactman:
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.
4 March 2007, 2:37 pmMurray Shactman:
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″
4 March 2007, 3:01 pmOlly:
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 March 2007, 3:56 pmDoug Jenkins:
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.
4 March 2007, 7:03 pmRick Williams:
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. =:-)
4 March 2007, 9:32 pmjkpieterse:
So what about this little file:
www.jkp-ads.com/downloadscript.asp?filename=timeeditor.zip
5 March 2007, 1:59 amOlly:
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
5 March 2007, 7:48 amCharles Chickering:
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.
5 March 2007, 9:05 amjkpieterse:
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.
5 March 2007, 9:21 amDoug Jenkins:
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.
5 March 2007, 4:30 pmTushar Mehta:
“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
5 March 2007, 6:55 pmNiek Otten:
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.
6 March 2007, 3:29 amAndrew Hunt:
Dick
You might consider just using a Custom Format for the cells set to
6 March 2007, 7:44 am0″:”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
Olly:
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.
6 March 2007, 12:51 pmJamie Collins:
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.
–
7 March 2007, 4:13 amTrevor Stokes:
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
27 June 2007, 11:14 amxMRG:
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
29 June 2007, 8:50 pm