calculating with timestamps in format hh:mm:ss.000 in EXCEL VBA

  • Thread starter Thread starter Crazy_Rick
  • Start date Start date
C

Crazy_Rick

I'm trying to read a variable from an excel sheet in the format
hh:mm:ss.000 and subtract it with a value in the same format.
This works fine if i do it in the worksheet,


A1 = 09:03:00.296
A2 = 09:03:00.203

A2 - A1 = 00:00:00.093


however if i do the same in VBA im getting a "type mismatch" error 13

Dim A1
Dim A2

A1 = Worksheets("Sheet 1").Range("A1")
A2 = Worksheets("Sheet 1").Range("A2")

Worksheets("Sheet 1").Range("A3") = A1 - A2

I appreciate any help on this.

Thomas Meixner
 
-----Original Message-----
I'm trying to read a variable from an excel sheet in the format
hh:mm:ss.000 and subtract it with a value in the same format.
This works fine if i do it in the worksheet,


A1 = 09:03:00.296
A2 = 09:03:00.203

A2 - A1 = 00:00:00.093


however if i do the same in VBA im getting a "type mismatch" error 13

Dim A1
Dim A2

A1 = Worksheets("Sheet 1").Range("A1")
A2 = Worksheets("Sheet 1").Range("A2")

Worksheets("Sheet 1").Range("A3") = A1 - A2

I appreciate any help on this.

Thomas Meixner
.
Click to expand...
You need to explicitly tell VBA what type of variable you
are working with - in this case, Date types - or else your
formula is ambiguous. So replace your Dim statements with:

Dim A1 as Date, A2 as Date

I think this should resolve the problem
 
K Dales said:
You need to explicitly tell VBA what type of variable you
are working with - in this case, Date types - or else your
formula is ambiguous. So replace your Dim statements with:

Dim A1 as Date, A2 as Date

I think this should resolve the problem
Click to expand...


Thx for the reply

As soon as i specify the wariable as Date, the program terminates as
soon as i try to read the cell. Variable type "Date" seems not
understand the format hh:mm:ss.000.

Is there maybe way to read the value into a decimal format, calculate
it and format it back to hh:mm:ss.000 ?
 
-----Original Message-----
"K Dales" <[email protected]> wrote in
Click to expand...
message news: said:
Thx for the reply

As soon as i specify the wariable as Date, the program terminates as
soon as i try to read the cell. Variable type "Date" seems not
understand the format hh:mm:ss.000.

Is there maybe way to read the value into a decimal format, calculate
it and format it back to hh:mm:ss.000 ?
.
Click to expand...

It is the decimal points after the seconds. Excel cannot
understand them as a date format (and cannot handle it -
there would be an overflow in the date variable if it
tried to handle subdivisions of seconds that small). This
will make it tricky. I think you need to parse the format
yourself, as in the example below:

Dim A1 as Variant, A2 as Variant, ADiff as Variant
Dim T1 as Date, T2 as Date, TDiff as Variant
Dim S1 as Single, S2 as Single, SDiff as Variant

A1 = Worksheets("Sheet 1").Range("A1")
T1=TimeValue(Left(A1,6) & "00")
S1 = CSng(Right(A1,6))

A2 = Worksheets("Sheet 1").Range("A2")
T2=TimeValue(Left(A2,6) & "00")
S2 = CSng(Right(A2,6))

Now you can subtract the seconds part (S2 from S1) and the
hours/minutes part (T2 from T1) then reassemble the result
into a string - but you would also need to have
a "carrying" procedure in the case S2>S1. More coding
than I can afford the time to do at the moment, but I hope
it gives you a clue.

Good luck!
 
Back
Top