VBA - Time differences with TEXT 1

[AnotherAlan]

Hi all,

It's been a while since I played with VBA and need a little nudge in the right direction.

I wrote a script ages ago that pulls info from a logfile.

The below section shows a portion that I now need to update;

ElseIf InStr(1, data, starttime) Then
Me.Cells(r, "c") = Mid(data, 14, 15)
Me.Cells(r, "d") = Right(data, 8)

Essentialy I am pasting the time but in string format, hh:mm:ss, as printed on the log.
I would prefer to now have this as a custom format showing hh:mm only. The reason is to make use of the text function to get the difference in times between job start and job end.
I've tried reformatting the cells containing this data on the sheet to hh:mm, but my text is still showing as #VALUE.

Any help / ideas appreciated.
Thanks

 

[SkipVought]

Hi,

I'd parse the strings and convert to a Time Value using the TimeValue function. Then is the arithmetic difference between the start and end time values.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_[red][/red]

 

[AnotherAlan]

Fantastic, good info and quick reply as well.

Cheers Skip.