=TIME(LEFT(A1,2),ROUND(MID(A1,4,2)/30,0)*30,0)

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"J.W. Aldridge" wrote in message
...
I am uploading data, and in column A it post the times like this:

01.31.06
02.00.06
02.31.08
03.00.05
03.31.06
04.00.23
04.31.15
05.00.11
05.31.12
06.00.07

I would like to insert code that would round each time DOWN to the
half hour and then remove the seconds.

01:30
02:00
02:30
03:00
03:30
04:00
04:30
05:00
05:30
06:00

any suggestions?
thanx