Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA Now
function only has hh:mm:ss. I need the accuracy of the excel now function in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use application.calculate
so it updates. Is there a better way to do this?

Hi,

You could use the 'timer' function in vb which measures elapsed time and
providing you not using a Mac it will return the fractional part of a second.

Start = Timer
For x = 1 To 10000000: Next
elapsedtime = Timer - Start
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"zip22" wrote:

Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA Now
function only has hh:mm:ss. I need the accuracy of the excel now function in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use application.calculate
so it updates. Is there a better way to do this?

Using the Timer function can present problems every now and then. Here is a
link in which someone pointed out what they thought was a bug in using the
Timer function and, if you scroll down, you will see a reply which explains
some of the problems in using the Timer function and offers a much more
reliable, although not completely perfect (note the 49.7 day roll-over),
alternative method.

http://us.generation-nt.com/answer/p...11.html?page=2

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
Hi,

You could use the 'timer' function in vb which measures elapsed time and
providing you not using a Mac it will return the fractional part of a
second.

Start = Timer
For x = 1 To 10000000: Next
elapsedtime = Timer - Start
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"zip22" wrote:

Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now
function in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?

Rick,

Thanks for the link, I wasn't aware of any issues with it until now.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Rick Rothstein" wrote:

Using the Timer function can present problems every now and then. Here is a
link in which someone pointed out what they thought was a bug in using the
Timer function and, if you scroll down, you will see a reply which explains
some of the problems in using the Timer function and offers a much more
reliable, although not completely perfect (note the 49.7 day roll-over),
alternative method.

http://us.generation-nt.com/answer/p...11.html?page=2

--
Rick (MVP - Excel)



"Mike H" wrote in message
...
Hi,

You could use the 'timer' function in vb which measures elapsed time and
providing you not using a Mac it will return the fractional part of a
second.

Start = Timer
For x = 1 To 10000000: Next
elapsedtime = Timer - Start
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"zip22" wrote:

Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now
function in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?

.

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it. So
for quick testing where a resolution of about 1/20 sec is enough I use VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?

[now()] did what I was looking for. I don't think it was a data type issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths of a
second)



"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it. So
for quick testing where a resolution of about 1/20 sec is enough I use VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.

Generally it's best to avoid square brackets. Difficult to answer your main
question though until you give some information about what I asked you
previously.

Regards,
Peter T

"zip22" wrote in message
...
[now()] did what I was looking for. I don't think it was a data type
issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths of
a
second)



"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date
type
conversion. However it's actual resolution is to 1/100 sec (at least in
my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it.
So
for quick testing where a resolution of about 1/20 sec is enough I use
VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the
VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now
function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.

I am putting together a sort of split timer, but seeing the previous value
where the timer was last stopped is useful. Simplified, I have a value in
cell a1 that is the time the stopwatch was last stopped. When I press a
button (run the macro), the split time between a1 and now is entered into b1.
A1 is reset to now. Press the button again and the split time time is
entered into b2. Press again, and b3 is filled in, etc

Comparing the current time to cell a1 lets the person know the approximate
running split time. It is better to keep this slightly inaccurate, and not
use a running timer. A running timer may enourage trying to match the
previous split time instead of accurately watching the event. The rough idea
that the previous end time and the current system time gives is a good enough
measure.

Accuracy to one hundredth of a second is acceptable.

looking at "timer" in VBA, shouldn't
now and timer match? (For the time portion anyways)

On my system, they currently differ by 0:20:12 and it is drifting higher


"Peter T" wrote:

Generally it's best to avoid square brackets. Difficult to answer your main
question though until you give some information about what I asked you
previously.

Regards,
Peter T

"zip22" wrote in message
...
[now()] did what I was looking for. I don't think it was a data type
issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths of
a
second)



"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date
type
conversion. However it's actual resolution is to 1/100 sec (at least in
my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it.
So
for quick testing where a resolution of about 1/20 sec is enough I use
VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the
VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now
function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.



.

Maybe something like this then -

Sub SplitTime()

With Range("A1")
Range("B1") = .Value
.Formula = "=NOW()"
.Value = .Value
End With

End Sub

Sub NumFormat()
Range("A1:B1").NumberFormat = "hh:mm:ss.00"
End Sub

Regards,
Peter T

"zip22" wrote in message
...
I am putting together a sort of split timer, but seeing the previous value
where the timer was last stopped is useful. Simplified, I have a value in
cell a1 that is the time the stopwatch was last stopped. When I press a
button (run the macro), the split time between a1 and now is entered into
b1.
A1 is reset to now. Press the button again and the split time time is
entered into b2. Press again, and b3 is filled in, etc

Comparing the current time to cell a1 lets the person know the approximate
running split time. It is better to keep this slightly inaccurate, and
not
use a running timer. A running timer may enourage trying to match the
previous split time instead of accurately watching the event. The rough
idea
that the previous end time and the current system time gives is a good
enough
measure.

Accuracy to one hundredth of a second is acceptable.

looking at "timer" in VBA, shouldn't
now and timer match? (For the time portion anyways)

On my system, they currently differ by 0:20:12 and it is drifting higher


"Peter T" wrote:

Generally it's best to avoid square brackets. Difficult to answer your
main
question though until you give some information about what I asked you
previously.

Regards,
Peter T

"zip22" wrote in message
...
[now()] did what I was looking for. I don't think it was a data type
issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths
of
a
second)



"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the
Date
type
conversion. However it's actual resolution is to 1/100 sec (at least
in
my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with
it.
So
for quick testing where a resolution of about 1/20 sec is enough I use
VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where
the
VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now
function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell
with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.



.

The [] means that you're going back to excel to evaluate that expression.

It turns out to be a quicker trip if you actually use:
range("A1").value = application.evaluate("now()")

Personally, I think I'd use something like:

Dim myCell As Range
Set myCell = ActiveSheet.Range("d1") 'my test cell
With myCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss.000"
.Formula = Now
.Value2 = .Value2 'convert to values
End With

I think



zip22 wrote:

[now()] did what I was looking for. I don't think it was a data type issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths of a
second)

"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it. So
for quick testing where a resolution of about 1/20 sec is enough I use VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.


--

Dave Peterson

I think it's a bit better self-documentation.

Dave Peterson wrote:

The [] means that you're going back to excel to evaluate that expression.

It turns out to be a quicker trip if you actually use:
range("A1").value = application.evaluate("now()")

Personally, I think I'd use something like:

Dim myCell As Range
Set myCell = ActiveSheet.Range("d1") 'my test cell
With myCell
.NumberFormat = "mmm dd, yyyy hh:mm:ss.000"
.Formula = Now
.Value2 = .Value2 'convert to values
End With

I think

zip22 wrote:

[now()] did what I was looking for. I don't think it was a data type issue.
The cells are set to "mm:ss.00"

range("A1") = Now
always rounds down to the second

range("A1") = [Now()]
gives me hundredths of a second

after looking into the square brackets, it looks like i can also use
[A1]=[Now()]

This looks like it will be more straightforward to code. Is there any
downside to using this instead of timer? (if I am happy with hundredths of a
second)

"Peter T" wrote:

What's the purpose, IOW do you want a timer or do you want to know the
actual time, and in either case to what resolution.

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]

Despite the timer bug Rick mentioned, I've never had a problem with it. So
for quick testing where a resolution of about 1/20 sec is enough I use VBA's
Timer function. For higher resolution there are various APIs, eg
GetTickCount (that also has a rollover but it's never bit me!).

Regards,
Peter T



"zip22" wrote in message
...
Excel's Now() function has a resolution down to hh:mm:ss.00 where the VBA
Now
function only has hh:mm:ss. I need the accuracy of the excel now function
in
a macro but

application.worksheetfunction.now

does not work. My workaround at this point is referencing a cell with
"=now()" in it, but before I reference it I have to use
application.calculate
so it updates. Is there a better way to do this?


.


--

Dave Peterson

--

Dave Peterson

"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]



Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

"Helmut Meukel" wrote in message
...
"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...

FWIW Now() normally gets coerced to one second in cells due to the Date
type conversion. However it's actual resolution is to 1/100 sec (at least
in my light testing) so maybe simply -
dim x as double
x = [now()]



Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

sorry about the double post

"Helmut Meukel" wrote in message
...
"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...

FWIW Now() normally gets coerced to one second in cells due to the Date
type conversion. However it's actual resolution is to 1/100 sec (at least
in my light testing) so maybe simply -
dim x as double
x = [now()]



Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

What is it you think I said that is wrong. I didn't mention anything about
VBA's Now function, only that Excel's Now() has a resolution of 1/100sec in
my light testing (see below)

Sub test()
Dim b As Boolean, x#, y#
Const sec# = 1 / (24& * 60 * 60)

x = [Now()]
b = True
While b
y = [now()]
b = x = y
Wend
Debug.Print sec / (y - x) ' about 100

End Sub

If you change [Now()] to Now I expect the debug will be about 1, ie VBA's
Now has a resolution of 1 second (not sure why you say you can return a
higher resolution VBA's Now)

FWIW I am well aware that the square brackets example I posted Evaluates
Excel's NOW() function.

Regards,
Peter T

"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...
sorry about the double post

"Helmut Meukel" wrote in message
...
"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...

FWIW Now() normally gets coerced to one second in cells due to the Date type
conversion. However it's actual resolution is to 1/100 sec (at least in my
light testing) so maybe simply -
dim x as double
x = [now()]



Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

What is it you think I said that is wrong. I didn't mention anything about
VBA's Now function, only that Excel's Now() has a resolution of 1/100sec in my
light testing (see below)

Sub test()
Dim b As Boolean, x#, y#
Const sec# = 1 / (24& * 60 * 60)

x = [Now()]
b = True
While b
y = [now()]
b = x = y
Wend
Debug.Print sec / (y - x) ' about 100

End Sub

If you change [Now()] to Now I expect the debug will be about 1, ie VBA's Now
has a resolution of 1 second (not sure why you say you can return a higher
resolution VBA's Now)

FWIW I am well aware that the square brackets example I posted Evaluates
Excel's NOW() function.

Regards,
Peter T



Peter,
the OP complained about the inaccurate Now() in VBA compared to
the high accuracy of NOW() when used in a formula in the spreadsheet.
He obviously thought they were the same because they have the
same name.

That inaccuracy is an issue of the VBA function, not the data type.
You can still declare your variable as Date and get the higher accuracy:
dim x as Date
x = [now()]
That's *if* you use the spreadsheet function. The VBA date data type
is internally a double and can therefore contain values with fractions of
seconds.

Helmut.

"Helmut Meukel" wrote in message
...
"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...
sorry about the double post

"Helmut Meukel" wrote in message
...
"Peter T" <peter_t@discussions schrieb im Newsbeitrag
...

FWIW Now() normally gets coerced to one second in cells due to the Date
type conversion. However it's actual resolution is to 1/100 sec (at
least in my light testing) so maybe simply -
dim x as double
x = [now()]



Wrong, dead wrong.

Try the same in VB6:
Time() and Now() don't return any fractions of a second.
Time() and Now() use identical code in VB6 and VBA because
it's in the very same DLL: MSVBVM60.DLL

The date data type is internally a double, where the integer part
is the day - starting with 12/30/1899 as day 0 -
and the fractional part is the time - starting at midnight with .0000
Thus .25 is 6:00 AM, .75 is 6:00 PM
Being internally a double, a date data type could hold fractions of
seconds.

The Excel spreadsheet function Now() has the same name as the
VBA function but is more accurate.
If you use the brackets VBA will use the excel function instead of
its own function.
Excel has it's own date/time functions because they were first there.
VBA was added to Excel with Excel 95.

Helmut.

What is it you think I said that is wrong. I didn't mention anything
about VBA's Now function, only that Excel's Now() has a resolution of
1/100sec in my light testing (see below)

Sub test()
Dim b As Boolean, x#, y#
Const sec# = 1 / (24& * 60 * 60)

x = [Now()]
b = True
While b
y = [now()]
b = x = y
Wend
Debug.Print sec / (y - x) ' about 100

End Sub

If you change [Now()] to Now I expect the debug will be about 1, ie VBA's
Now has a resolution of 1 second (not sure why you say you can return a
higher resolution VBA's Now)

FWIW I am well aware that the square brackets example I posted Evaluates
Excel's NOW() function.

Regards,
Peter T



Peter,
the OP complained about the inaccurate Now() in VBA compared to
the high accuracy of NOW() when used in a formula in the spreadsheet.
He obviously thought they were the same because they have the
same name.

I didn't read the OP obviously thought they were the same at all. Indeed he
appeared to have a very good understanding of the difference between the
respective Excel and VBA Now functions and their respective resolutions. He
wanted to use Excel's function in VBA. I confirmed Excel's Now was to 1/100
sec and showed him one way (of three alternatives to the way he was already
using) to call it in VBA.

I don't see that as a "wrong, very wrong" answer to the OP's question.

That inaccuracy is an issue of the VBA function, not the data type.
You can still declare your variable as Date and get the higher accuracy:
dim x as Date
x = [now()]
That's *if* you use the spreadsheet function. The VBA date data type
is internally a double and can therefore contain values with fractions of
seconds.

I didn't refer to VBA's function. I agree the Date data-type can handle a
much larger range of values than I implied, though it can't handle the range
of a Double or even a Long (with large +/- values)

Regards,
Peter T