PDA

View Full Version : NumberFormat of Now



mdmackillop
06-24-2007, 04:59 AM
In relation to this post http://vbaexpress.com/forum/showthread.php?t=13509
A curiosity, why does Now not display fractions of a second when formatted?
Sub TestTime()
With Cells(1, 1)

.Offset(0, 1) = "Now(); General"
.Offset(0) = "=Now()"
.Offset(0).NumberFormat = "General"

.Offset(1, 1) = "'=now()"
.Offset(1) = "=now()"
.Offset(1).NumberFormat = "hh.mm.ss.00"

.Offset(2, 1) = "Timer/3600/24"
.Offset(2) = Timer / 3600 / 24
.Offset(2).NumberFormat = "hh.mm.ss.00"

.Offset(3, 1) = "Now; General"
.Offset(3) = Now
.Offset(3).NumberFormat = "General"

.Offset(4, 1) = "Now"
.Offset(4) = Now
.Offset(4).NumberFormat = "hh.mm.ss.00"
End With
End Sub

mvidas
06-25-2007, 10:14 AM
?typename(now)
Date
?typename(evaluate("=now()"))
Double


Returns a Variant (Date) specifying the current date and time according your computer's system date and time.
Date variables (javascript:hhobj_4.Click()) are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.
I think it's just that the Now() vba function returns the date, which doesn't include Milliseconds. Excel's NOW() function returns a double, which does include milliseconds.

Add this to your sub above: .Offset(5, 1) = "Date + Timer / 3600 / 24"
.Offset(5) = Date + Timer / 3600 / 24
.Offset(5).NumberFormat = "hh.mm.ss.00"

.Offset(6, 1) = "CDbl(Date) + Timer / 3600 / 24"
.Offset(6) = CDbl(Date) + Timer / 3600 / 24
.Offset(6).NumberFormat = "hh.mm.ss.00"Without that CDbl in there, adding Date to the Timer part converts the Timer results to Date type. Putting the CDbl in there keeps it all in a Double format, and is a good way of getting the full time in VBA

So in summary, its all about the data type :)

mdmackillop
06-25-2007, 10:31 AM
Thanks for the CDbl, that's a useful tip.
Re Now() and Now, both appear equal and can be shown to 10 decimal places by reformatting the cells after running the macro.

mvidas
06-25-2007, 11:27 AM
Things are not always as they appear unfortunately. Since Evaluate("=now()") in VBA is the same as using the worksheet formula on a worksheet, you can see the differences in the return values by running something like:Sub TestTime2()
Debug.Print Format(Now, "0.00000000000")
Debug.Print Format(Evaluate("=now()"), "0.00000000000")
Debug.Print Format(Now, "0.00000000000")
Debug.Print Format(Evaluate("=now()"), "0.00000000000")
End SubI extended it to 11 decimals (knowing it wouldn't show the last one) to ensure there wasn't any funny rounding going on or anything. After just running it, I got:
39258.60070601850
39258.60070798610
39258.60070601850
39258.60070810190You can see there is a higher value in the worksheet function's version, enough that even the value changed in the slight amount of time it took to run the 3rd line of the sub (no change in the VBA version)

mvidas
06-25-2007, 11:32 AM
Another way you can see it is to add the following 2 subs to your VBE:Sub TestTime3()
Debug.Print Format(Now, "0.00000000000")
End Sub
Sub TestTime4()
Debug.Print Format(Evaluate("=now()"), "0.00000000000")
End SubPut the cursor somewhere in TestTime3, open the immediate window, and hold down the F8 key (to continually step through and re-start the sub, at a less-frequent interval than using a For loop or something). Doing that for TestTime3, I got:
39258.60505787040
39258.60505787040
39258.60505787040
39258.60506944440
39258.60506944440
39258.60506944440
39258.60506944440
39258.60506944440
39258.60506944440
39258.60506944440
39258.60508101850
39258.60508101850
39258.60508101850
39258.60508101850
39258.60508101850
39258.60508101850
39258.60508101850
39258.60508101850
39258.60509259260
39258.60509259260
39258.60509259260
39258.60509259260

Doing the same for TestTime4 I got:
39258.60539340280
39258.60539502320
39258.60539641200
39258.60539791670
39258.60539953700
39258.60540092590
39258.60540243060
39258.60540405090
39258.60540555560
39258.60540694440
39258.60540856480
39258.60541006940
39258.60541145830
39258.60541296300
39258.60541458330
39258.60541597220

mdmackillop
06-25-2007, 02:10 PM
Thanks Matt,
I see it now.
Regards
Malcolm