I'm trying to increace the accuracy with which EXCEL reports the time. I'm
using the NOW function to write the time that an entry is entered into a
spreadsheet. I would like to know the time to better than 1 second.

For example, instead of getting 13:45:45, I would like to see 13:45:24.56785.

Thanks

Don't think that kind of accuracy is even available much less displayable.

Alex Rauket wrote:

I'm trying to increace the accuracy with which EXCEL reports the time. I'm
using the NOW function to write the time that an entry is entered into a
spreadsheet. I would like to know the time to better than 1 second.

For example, instead of getting 13:45:45, I would like to see 13:45:24.56785.

Thanks

Alex Rauket wrote:
I'm trying to increace the accuracy with which EXCEL reports the time. I'm
using the NOW function to write the time that an entry is entered into a
spreadsheet. I would like to know the time to better than 1 second.

For example, instead of getting 13:45:45, I would like to see 13:45:24.56785.

Thanks

You might be able to set up a custom time format that would show this
many digits, but I doubt that it would be accurate much beyond the
tenths of a second, i.e the last four digits would be questionable.

Chip Pearson has an explanation of how Excel stores dates and times at
http://www.cpearson.com/excel/datetime.htm. There are six digits set
aside to store 24 hours of time. Do the math and you'll see that you
won't get any closer accuracy than tenths of a second.

Geez, I'm happy to know whether it's time for breakfast, lunch, or dinner.

Bill

On Mar 16, 1:30*pm, Bill Sharpe wrote:
Chip Pearson has an explanation of how Excel stores
dates and times at
http://www.cpearson.com/excel/datetime.htm.
There are six digits set aside to store 24 hours of time.
Do the math and you'll see that you won't get any closer
accuracy than tenths of a second.

Exactly how did you "do the math"?

First, Chip's description of the internal format is over-simplified
and frankly incorrect to the degree that you interpreted it (literally
6 digits "set aside" for time).

Second, even if we assume there are 6 digits for time, I do not see
how to "do the math" to reach the conclusion that you did. (Hint:
Try entering =0.01/86400 and format it as Number. How many decimal
places do you need?)

Nonetheless, your conclusion is correct.

On Mar 16, 2:25 pm, I wrote:
On Mar 16, 1:30 pm, Bill Sharpe wrote:
Do the math and you'll see that you won't get any
closer accuracy than tenths of a second.
[....]
Nonetheless, your conclusion is correct.

Sorry, I misread. Your conclusion is incorrect. The Excel NOW()
function is accurate to 10 milliseconds. That is hundredths of a
second.


----- original posting -----

On Mar 16, 2:25*pm, joeu2004 wrote:
On Mar 16, 1:30*pm, Bill Sharpe wrote:

Chip Pearson has an explanation of how Excel stores
dates and times at
http://www.cpearson.com/excel/datetime.htm.
There are six digits set aside to store 24 hours of time.
Do the math and you'll see that you won't get any closer
accuracy than tenths of a second.

Exactly how did you "do the math"?

First, Chip's description of the internal format is over-simplified
and frankly incorrect to the degree that you interpreted it (literally
6 digits "set aside" for time).

Second, even if we assume there are 6 digits for time, I do not see
how to "do the math" to reach the conclusion that you did. *(Hint:
Try entering =0.01/86400 and format it as Number. *How many decimal
places do you need?)

Nonetheless, your conclusion is correct.

Errata....

On Mar 16, 2:25*pm, I wrote:
On Mar 16, 1:30*pm, Bill Sharpe wrote:
Do the math and you'll see that you won't get any closer
accuracy than tenths of a second.
[....]
Second, even if we assume there are 6 digits for time,
I do not see how to "do the math" to reach the conclusion
that you did. *(Hint: Try entering =0.01/86400 and format
it as Number. *How many decimal places do you need?)

Okay, I see now. I kept misreading your "tenths of seconds" as
hundredths of seconds, which is the correct resolution of the Excel NOW
() function.

It is true that 0.1 is the smallest fraction of a second that can be
represented in 6 decimal fractions. (Actually, about 0.0432 is. But
let's not split hairs ;-.)

The important "take aways" a (a) I believe Chip's description is
incorrect; and (b) the Excel NOW() function is accurate to 10
milliseconds -- hundredths of a second.

joeu2004 wrote:
On Mar 16, 1:30 pm, Bill Sharpe wrote:
Chip Pearson has an explanation of how Excel stores
dates and times at
http://www.cpearson.com/excel/datetime.htm.
There are six digits set aside to store 24 hours of time.
Do the math and you'll see that you won't get any closer
accuracy than tenths of a second.

Exactly how did you "do the math"?

First, Chip's description of the internal format is over-simplified
and frankly incorrect to the degree that you interpreted it (literally
6 digits "set aside" for time).

Second, even if we assume there are 6 digits for time, I do not see
how to "do the math" to reach the conclusion that you did. (Hint:
Try entering =0.01/86400 and format it as Number. How many decimal
places do you need?)

Nonetheless, your conclusion is correct.
My math was based on Chip's information that date/time information is
stored as an integer and decimal combination with six digits set aside
for the time component. That means you can slice 24 hours into 1,000,000
equal pieces. There are 24x60x60x10 = 860,000 tenths of a second in a
day. Therefore Excel can potentially track only to the tenths of a
second. The hundredths of a second and beyond will result in unreliable
numbers.

I agree with your questioning as to why in the world the OP wants to be
that accurate.

Got to go now. It's time for breakfast...

Bill

On Mar 17, 6:38*am, Bill Sharpe wrote:
My math was based on Chip's information that date/time information is
stored as an integer and decimal combination with six digits set aside
for the time component. That means you can slice 24 hours into 1,000,000
equal pieces. There are 24x60x60x10 = 860,000 tenths of a second in a
day. Therefore Excel can potentially track only to the tenths of a
second. The hundredths of a second and beyond will result in unreliable
numbers.

Your reasoning would be sound, if the information were correct. But
Chip's information is incorrect, at least in a literal sense.

First, I hasten to note that according to http://support.microsoft.com/kb/214094,
the format of the time component is just 5 decimal fractions. By your
reasoning, that would slice 24 hours into 100,000 equal pieces, and
the smallest increment of time would be 24x60x60 = 86400 seconds(!).
Who ya' gonna believe?

The answer is: neither is correct.

You can easily verify this by entering the time 0:0:0.001 and
formatting the cell as Number with at least 8 decimal places. (13 dp
is more representative.)

Although h:mm:ss.000 is the most precise custom format supported and
recognized for data entry, we can actually input any fractional time.
As always, the displayed value is subject to Excel's rounding rules.
For example, the largest number displayed as 23:59:59.999 is about
0.999499999999999/86400.

(FYI, KB 214094 asserts that 0.99999 is 23:59:59. It is true that
that value is displayed that way. But if we compare it with time
entered as 23:59:59, the comparison is false(!).)


The hundredths of a second and beyond will result in
unreliable numbers.

I am not sure what you mean by that.

All decimal fractions and non-integer expressions are subject to the
oddities of IEEE floating point representation. But I would say that
the internal representation of 0.1 seconds is no less "reliable" than
the representations of 0.01, 0.001 and even 1 second expressed as
fractions of a day (i.e. divided by 86400), at least within 15
significant digits. In fact, I believe the internal representation of
0.01 seconds is closer to its infinite representation than 0.01
seconds, and both are closer than 1 second is(!) [1].

But all of that is about representation of time. The original posting
raised issues about the resolution of the Excel NOW() function.

I believe the following macro demonstrates that the smallest time
increment reflected by NOW() is 10 milliseconds. (Sometimes we see a
20-msec difference. There are for various possible reasons.)

CAUTION: This macro overwrites A1:K3 of the current worksheet, and it
changes the column width of A:K.

Private Sub measXLNow()
Dim n As Long
Dim st As Double, et As Double
Dim c As Integer
Dim oldCalc

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Range("A1") = "start Now()"
Range("A2") = "end Now()"
Range("A3") = "#loops"

For c = 1 To 10
Range("B1").Cells(1, c).Formula = "=Now()"
st = Range("B1").Cells(1, c)
n = 0
Do
n = n + 1
Range("B2").Cells(1, c).Formula = "=Now()"
et = Range("B2").Cells(1, c)
Loop Until et < st

Range("B1").Cells(1, c) = st
Range("B1").Cells(1, c).NumberFormat = "h:mm:ss.000"
Range("B2").Cells(1, c) = et
Range("B2").Cells(1, c).NumberFormat = "h.mm:ss.000"
Range("B3").Cells(1, c) = n
Range("B3").Cells(1, c).NumberFormat = "0"
Next c

Range("A1", Range("B3").Cells(1, c)).Columns.AutoFit
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub


Endnotes:

[1] 0.1/86400 =
0.00000115740740740740,739228630071938619394700253 9061941206455230712890625
0.01/86400 =
0.000000115740740740740,74187560803210830795428876 17208180017769336700439453125
0.001/86400 =
0.0000000115740740740740,7385668855818961972548031 26952538150362670421600341796875
1/86400 =
0.0000115740740740740,7349934653356671176993586414 0279591083526611328125

(The comma demarcates 15 significant digits to the left.)

[2] All of my examples were derived on MS Win XP using Excel 2003 and
VB 6.3.

Typos....

On Mar 17, 9:58 am, I wrote:
I would say that the internal representation of 0.1 seconds
is no less "reliable" than the representations of 0.01

"No __more__ reliable".

I believe the internal representation of 0.01 seconds is
closer to its infinite representation than 0.01 seconds

"Than 0.1 seconds".



----- original posting -----

On Mar 17, 9:58*am, joeu2004 wrote:
On Mar 17, 6:38*am, Bill Sharpe wrote:

My math was based on Chip's information that date/time information is
stored as an integer and decimal combination with six digits set aside
for the time component. That means you can slice 24 hours into 1,000,000
equal pieces. There are 24x60x60x10 = 860,000 tenths of a second in a
day. Therefore Excel can potentially track only to the tenths of a
second. The hundredths of a second and beyond will result in unreliable
numbers.

Your reasoning would be sound, if the information were correct. *But
Chip's information is incorrect, at least in a literal sense.

First, I hasten to note that according tohttp://support.microsoft.com/kb/214094,
the format of the time component is just 5 decimal fractions. *By your
reasoning, that would slice 24 hours into 100,000 equal pieces, and
the smallest increment of time would be 24x60x60 = 86400 seconds(!).
Who ya' gonna believe?

The answer is: *neither is correct.

You can easily verify this by entering the time 0:0:0.001 and
formatting the cell as Number with at least 8 decimal places. *(13 dp
is more representative.)

Although h:mm:ss.000 is the most precise custom format supported and
recognized for data entry, we can actually input any fractional time.
As always, the displayed value is subject to Excel's rounding rules.
For example, the largest number displayed as 23:59:59.999 is about
0.999499999999999/86400.

(FYI, KB 214094 asserts that 0.99999 is 23:59:59. *It is true that
that value is displayed that way. *But if we compare it with time
entered as 23:59:59, the comparison is false(!).)

The hundredths of a second and beyond will result in
unreliable numbers.

I am not sure what you mean by that.

All decimal fractions and non-integer expressions are subject to the
oddities of IEEE floating point representation. *But I would say that
the internal representation of 0.1 seconds is no less "reliable" than
the representations of 0.01, 0.001 and even 1 second expressed as
fractions of a day (i.e. divided by 86400), at least within 15
significant digits. *In fact, I believe the internal representation of
0.01 seconds is closer to its infinite representation than 0.01
seconds, and both are closer than 1 second is(!) [1].

But all of that is about representation of time. *The original posting
raised issues about the resolution of the Excel NOW() function.

I believe the following macro demonstrates that the smallest time
increment reflected by NOW() is 10 milliseconds. *(Sometimes we see a
20-msec difference. *There are for various possible reasons.)

CAUTION: This macro overwrites A1:K3 of the current worksheet, and it
changes the column width of A:K.

Private Sub measXLNow()
Dim n As Long
Dim st As Double, et As Double
Dim c As Integer
Dim oldCalc

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Range("A1") = "start Now()"
Range("A2") = "end Now()"
Range("A3") = "#loops"

For c = 1 To 10
* *Range("B1").Cells(1, c).Formula = "=Now()"
* *st = Range("B1").Cells(1, c)
* *n = 0
* *Do
* * * n = n + 1
* * * Range("B2").Cells(1, c).Formula = "=Now()"
* * * et = Range("B2").Cells(1, c)
* *Loop Until et < st

* *Range("B1").Cells(1, c) = st
* *Range("B1").Cells(1, c).NumberFormat = "h:mm:ss.000"
* *Range("B2").Cells(1, c) = et
* *Range("B2").Cells(1, c).NumberFormat = "h.mm:ss.000"
* *Range("B3").Cells(1, c) = n
* *Range("B3").Cells(1, c).NumberFormat = "0"
Next c

Range("A1", Range("B3").Cells(1, c)).Columns.AutoFit
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub

Endnotes:

[1] 0.1/86400 =
0.00000115740740740740,739228630071938619394700253 9061941206455230712890625
* * 0.01/86400 =
0.000000115740740740740,74187560803210830795428876 1720818001776933670043945*3125
* * 0.001/86400 =
0.0000000115740740740740,7385668855818961972548031 2695253815036267042160034*1796875
* * 1/86400 =
0.0000115740740740740,7349934653356671176993586414 0279591083526611328125

* * (The comma demarcates 15 significant digits to the left.)

[2] All of my examples were derived on MS Win XP using Excel 2003 and
VB 6.3.

See inline comments

joeu2004 wrote:
On Mar 17, 6:38 am, Bill Sharpe wrote:
My math was based on Chip's information that date/time information is
stored as an integer and decimal combination with six digits set aside
for the time component. That means you can slice 24 hours into 1,000,000
equal pieces. There are 24x60x60x10 = 860,000 tenths of a second in a
day. Therefore Excel can potentially track only to the tenths of a
second. The hundredths of a second and beyond will result in unreliable
numbers.

Your reasoning would be sound, if the information were correct. But
Chip's information is incorrect, at least in a literal sense.

First, I hasten to note that according to http://support.microsoft.com/kb/214094,
the format of the time component is just 5 decimal fractions.

And the reason KB214094 uses a five-digit representation for an elapsed
time of 12 hours 45 minutes is because 0.53125 is the exact decimal
value for that time period. 12.75/24 = 0.53125.

By your
reasoning, that would slice 24 hours into 100,000 equal pieces, and
the smallest increment of time would be 24x60x60 = 86400 seconds(!).
Who ya' gonna believe?

My reasoning was based on 1,000,000 slices of time each 24 hours per
Chip's site. That means the 864,000 tenths of a second in one day can
theoretically be handled by the available decimals. Anything beyond that
(hundredths of a second, thousandths of a second is unreliable. If Chip
is wrong then obviously my conclusion is wrong, but see below.

The answer is: neither is correct.

You can easily verify this by entering the time 0:0:0.001 and
formatting the cell as Number with at least 8 decimal places. (13 dp
is more representative.)

Although h:mm:ss.000 is the most precise custom format supported and
recognized for data entry, we can actually input any fractional time.
As always, the displayed value is subject to Excel's rounding rules.
For example, the largest number displayed as 23:59:59.999 is about
0.999499999999999/86400.

(FYI, KB 214094 asserts that 0.99999 is 23:59:59. It is true that
that value is displayed that way. But if we compare it with time
entered as 23:59:59, the comparison is false(!).)


The hundredths of a second and beyond will result in
unreliable numbers.

I am not sure what you mean by that.

See my comment above. Perhaps I should have said unreliable digits
rather than unreliable numbers.

All decimal fractions and non-integer expressions are subject to the
oddities of IEEE floating point representation. But I would say that
the internal representation of 0.1 seconds is no less "reliable" than
the representations of 0.01, 0.001 and even 1 second expressed as
fractions of a day (i.e. divided by 86400), at least within 15
significant digits. In fact, I believe the internal representation of
0.01 seconds is closer to its infinite representation than 0.01
seconds, and both are closer than 1 second is(!) [1].

I'm not getting into the decimal system vs. the binary system and the
loss of accuracy that entails at some point in working with very small
numbers. Some fractions are exactly equal in decimal and binary. 1.1
binary exactly equals 1.5 decimal. 1.11 exactly equals 1.75 decimal.
Most such conversions are approximate, although close enough for most
practical purposes.


But all of that is about representation of time. The original posting
raised issues about the resolution of the Excel NOW() function.

I believe the following macro demonstrates that the smallest time
increment reflected by NOW() is 10 milliseconds. (Sometimes we see a
20-msec difference. There are for various possible reasons.)

Ten milliseconds is 0.01 seconds. And I noted above that the numbers
beyond the tenths column are unreliable. That could explain the
occasional 20 millisecond difference.

CAUTION: This macro overwrites A1:K3 of the current worksheet, and it
changes the column width of A:K.

Private Sub measXLNow()
Dim n As Long
Dim st As Double, et As Double
Dim c As Integer
Dim oldCalc

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Range("A1") = "start Now()"
Range("A2") = "end Now()"
Range("A3") = "#loops"

For c = 1 To 10
Range("B1").Cells(1, c).Formula = "=Now()"
st = Range("B1").Cells(1, c)
n = 0
Do
n = n + 1
Range("B2").Cells(1, c).Formula = "=Now()"
et = Range("B2").Cells(1, c)
Loop Until et < st

Range("B1").Cells(1, c) = st
Range("B1").Cells(1, c).NumberFormat = "h:mm:ss.000"
Range("B2").Cells(1, c) = et
Range("B2").Cells(1, c).NumberFormat = "h.mm:ss.000"
Range("B3").Cells(1, c) = n
Range("B3").Cells(1, c).NumberFormat = "0"
Next c

Range("A1", Range("B3").Cells(1, c)).Columns.AutoFit
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub


Endnotes:

[1] 0.1/86400 =
0.00000115740740740740,739228630071938619394700253 9061941206455230712890625
0.01/86400 =
0.000000115740740740740,74187560803210830795428876 17208180017769336700439453125
0.001/86400 =
0.0000000115740740740740,7385668855818961972548031 26952538150362670421600341796875
1/86400 =
0.0000115740740740740,7349934653356671176993586414 0279591083526611328125

(The comma demarcates 15 significant digits to the left.)

[2] All of my examples were derived on MS Win XP using Excel 2003 and
VB 6.3.

Neat macro!

The repetition of the digits "740" four times before the comma in the
examples above( and almost five times as the next sequence after the
comma is 739, 741, 738, and 734) is suspect, although I guess no more so
than the endless threes in the decimal representation of 1/3. I'd be
less suspicious if the 740's kept on repeating.

I just had an "aha" moment. If I store just the time in a cell, e.g.
12:01:01 PM, Excel displays the time but stores the information as
0.500011574074074 -- fifteen digits and there's those suspicious 740
digits repeating again in there. If I add today's date and store the
cell formatted as date and time, Excel stores the information as
39889.5000115741 -- five integer digits and ten decimal digits.
Apparently Excel does keep track of time to more than six decimal digits.

I'm still curious to know why the OP wants to be so precise in Excel.

And it's not nearly close enough to dinner time -- corned beef tonight!

Bill

"Alex Rauket" wrote:
I'm trying to increace the accuracy with which EXCEL reports the time.
I'm using the NOW function to write the time that an entry is entered
into a spreadsheet. I would like to know the time to better than 1 second.
For example, instead of getting 13:45:45, I would like to see
13:45:24.56785.

With custom format, the best you can do is "h:mm:ss.000" -- seconds to 3
decimal places. Arguably, you could do the math yourself. But I don't think
it is worth it because Excel NOW() is accurate to only 10 milliseconds, at
least on my MS Win XP system with Excel 2003. And by the way, the VBA Now()
function is accurate to only 1 second.

Why do you want to determine the data entry time to less than a second, in
the first place?

If you are really trying to compute the time between cell changes, there are
more accurate methods for measuring time intervals using VBA.