round time in column to nearest half hour

[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

 

[Bob Phillips]

=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)

 

[Rick Rothstein \(MVP - VB\)]

I think this will do what you want....

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[J.W. Aldridge]

how would i do this in vba...?

Want to implement this in the code after it downloads.


thanx

 

[Rick Rothstein \(MVP - VB\)]

I didn't mean to post this to your message... sorry.

Rick

 

[Dave D-C]

A suggestion:
=CONCATENATE(LEFT(A1,2),":",MID(A1,4,2))
D-C Dave

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

how would i do this in vba...?

Want to implement this in the code after it downloads.

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

 

[Ron Rosenfeld]

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

=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