Calculating elapsed TIME difference

[Guest]

I need to calculate the elapsed time difference between two fields. The end
result should be in the HH:MM:SS format.

The fields, I'll call them DATE_TIME1 and DATE_TIME2, are currently masked
to be displayed as 2005/11/30 08:15:30.25, but the actual unmasked format
looks like 2008113008153025.

I have not been successful in getting the desired results, outside of using
Excel. In Excel, I can get the exact results needed, but the volume of
records I need to calculate this for is in the millions, so Excel is not an
option.

Any help would be greatly appreciated!

 

[Allen Browne]

Presumably this is a Text field, that just contains a string of numbers.

You will need to use Left(), Mid(), and Right() to parse the various parts
of the text and generate a suitable string. The use CDate() to convert that
into a date.

Having done that for both fields, you will the use DateDiff() to get the
difference in seconds, and then parse the result to show in hh:nn:ss.

It would be much easier to use a pair of date/time fields of course.