Our expert trainers can help you get the best results.
If you would like any further information please contact one of our training advisors.
As I watched the Olympic Games I was reminded of the importance of accurate timing. So how precise is Microsoft Excel time resolution and what tricks and limits are to be found?
In our blog “Automatic Data Type Conversion Problems in Excel/ Dates – The Big Issue” we explain how Excel stores dates. Dates are a count of the number of days from 1 Jan 1900 (or 1904) and times are the decimal fraction. In this article I would like to look at times and the resolution we are able to achieve in our calculations and display formats.
There are several features which are relevant to Excel time resolution:
| Number precision | 15 digits |
| Smallest allowed positive number | 2.2251E-308 |
| Earliest date allowed for calculation | January 1, 1900 (January 1, 1904, if 1904 date system is used) |
| Latest date allowed for calculation | December 31, 9999 |
| Largest amount of time that can be entered | 9999:59:59 |
In this article I will refer to resolution and not concern myself with the subtleties of precision and accuracy!
Let’s just take a look at the results of using Excel’s volatile function NOW() which returns the system date and time and if Excel is given the opportunity it will automatically format the cell with a date / time format to match your regional settings.
So the detail to notice here is that if our time includes a date then that will determine the maximum resolution that we can achieve in Excel.
If we were to work with the smallest number that Excel allows and then treat it as a time what is the resolution in seconds?
Note that is 5.2E+302. (See E Notation or scientific notation elsewhere for an explanation). A glance in common references suggests that this is much smaller than any standard term used for small intervals of time. EG Yoctosecond which is defined as 10-24 Secs. (References vary here!) This is where the discussion could venture into the world of accuracy and of atomic physics but I am not going there.
If we consider dates between 18/05/1927 and 14/10/2173 which are the date equivalents of 10,000.00000000010and 99,999.99999999990 we still have aresolution of 10 decimal places.
So the resolution here is better than a Millisecond (10-3 Secs) but not quite a Microsecond (10-6 Secs)
Remember that Excel will offer date and time formats as defined in Control Panel > Regional Settings. However using ‘Custom Number Formats’ you are able to achieve virtually any date time format. The codes (d dd ddd m mm mmm mmmm mmmmm yy yyyy hh:mm:ss.000 ) combined with spaces and other punctuation are permitted. If necessary, place unusual punctuation in quotes.
Note how square brackets placed around [hh], [mm] or [ss] stops Excel incrementing the days, hours or minutes respectively and allows the value to be held as a total of the hours, minutes or seconds.
Excel is a little fussy as to what date / time values it will accept as input. When entering minutes and seconds only it is necessary to enter zero hours.
E.g. 00:30:20 will give the expected hh:mm:ss format but input 30:20 and Excel will assume [h]:mm:ss format and displays 30:20:00. Whereas 23:50 (Note less than 24 hrs) adopts the hh:mm format.
Excel will not display negative values in a date / time format unless you use the 1904 date system. This is not recommended as throughout the workbook ALL dates will be shifted by 4 years and it will get confusing if you link workbooks using different base dates. The offset is 1,462 days.
Interesting note: The reason that there is a 1904 date system is that Excel followed the convention adopted in Lotus 1-2-3 which failed to recognise that 1900 was not a leap year. Microsoft’s statement is: “The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.” When Excel for a Macintosh was released it adopted the 1904 date system.
Excel for Mac 2011 now is aligned with Excel for a PC and uses the 1900 base date.
Excel 2013 now offers the ability to save in “Strict Open XML Spreadsheet (*.xlsx)” format which starts on 30 Dec 1899, doesn’t display the 0 Jan 1900 or 29 Feb 1900.
Excel time resolution and accuracy, with regard to how dates and times are handled, has on the surface some frustrating characteristics. Behind the scenes Excel will store those Olympic lap times with resolutions that meet the requirements for the Olympic Games namely “..accurate to at least 1/100th of a second—but preferably to 1/1000th of a second or more.”
However, date math just requires a little patience.
Payments processed by
