All's well men, I posted thanks but it turned up in the middle of all your
posts and you may have missed it.

Thanks for all your help. I wasn't expecting you both to be still posting
this morning. It was too late for me to keep going last night.

I still don't fully understand why subtracting two dates takes you back
before 1900 but I believe the numbers give you the right answer if shown in
the right format.


"Joel" wrote:

Ho wdo you have time1 declared. It must be a single. the type conversion
has alway worked for me before. Delcaring variables as dates is the cause of
the problem because VBA/Excel makes errors in the calculation with negative
time. It is not a display time, it is a mathematical error. To get the date
of 1899 means excel came up with a negative number when it should of been
positive.

start = 6/8/2009 9:35:24 PM
now = 7/8/2009 10:04:45 PM

"now - start" should be postive and give a date after midnight Jan 1, 1900.
You are getting a result before midnight which means VBA got a negative
result. It is not a display problem. It is wrong math.

"OssieMac" wrote:

Joel's answer does not work in xl2007 or xl2002 either. Returns following
error.

Complile error:
Type-declaration character does not match decalred data.

Did you run the test that I posted? The problem is all with VBA's display of
the date/time. The correct underlying value is there. Trust me!!!!! (Just for
the record all code and answers that I post are tested unless I specifically
say it is untested.)

Modifying Joel's answer, the following will work and return the correct
double precision number.

Sub test()

Dim time1 As Date

Dim start As Date

Dim timeDiff As Double

Dim time1Double As Double

'Date and time in Range("B6")
'is 06 Aug 2009 09:35:24 PM

start = Sheets("Timing Sheet").Range("B6")

time1 = Now() - start

time1Double = time1

timeDiff = Now - start

MsgBox WorksheetFunction.Text(time1, "[hh]:mm:ss")

MsgBox WorksheetFunction.Text(time1, "00.000000")

Stop

End Sub


--
Regards,

OssieMac