round time in column to nearest half hour

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

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
 
I think this will do what you want....

=ROUNDDOWN(24*SUBSTITUTE(A1,".",":"),1)/24

Rick
 
Sorry, I just found out my formula doesn't work in the general case.

Rick
 
how would i do this in vba...?

Want to implement this in the code after it downloads.


thanx
 
You need to change the ROUND function call to ROUNDDOWN... the OP asked for
the times to be rounded "down" to the half-hour.

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

Rick
 
Sorry, I just found out my formula doesn't work in the general case.

Rick
 
how would i do this in vba...?
Want to implement this in the code after it downloads.
Click to expand...

You should be able to use this function... simply pass in each time string
you downloaded and a rounded down time value will be returned...

Function RoundedTime(TimeIn As String) As Date
Dim DecimalTime As Double
DecimalTime = 24 * TimeSerial(Left(TimeIn, 2), _
Mid(TimeIn, 4, 2), Right(TimeIn, 2))
RoundedTime = (Int(DecimalTime) + Int(2 * (DecimalTime - _
Int(DecimalTime))) / 2) / 24
End Function


Rick
 
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
Click to expand...

=FLOOR(LEFT(SUBSTITUTE(A1,".",":",1),5),30/1440)

or, as a VBA function:

=============================
Function RoundDown30(t) As Date
RoundDown30 = Left(Replace(t, ".", ":", 1, 1), 5)
RoundDown30 = Application.WorksheetFunction.Floor(RoundDown30, 30 / 1440)
End Function
======================
--ron
 
Back
Top