Trouble with date/time formatted as text...

[fatboymedic]

I have to generate a report, that among other things finds the AVERAGE of times listed in a field.. the problem is that the data that I have available comes from an excell sheet, AND a separate access table, where BOTH generate the times as a "general" or text field... ie: the cell or field shows "00:03:29" (HHMMSS) but its NOT a date/time format, its the aforementioned general, or text... I have tried changing the format on the original sheet/table without success. Is there a way to convert the text to date/time????



example:

date received response at customer
1/1/12 00:01:12 00:01:30 00:01:47
1/1/12 03:15:01 03:30:59 03:51:12

-> I need to calculate the average response, but cant create the expression in a query OR report because the times are formatted as text, and simply changing the field format doesnt work....

[alansidman]

I think that you may have some luck using the CDATE and TimeValue functions.

http://www.techonthenet.com/access/f...type/cdate.php

http://www.techonthenet.com/access/f.../timevalue.php

[fatboymedic]

Thank You!!