round time in column to nearest half hour
 

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

round time in column to nearest half hour
 

=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

round time in column to nearest half hour
 

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

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

Rick


"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

round time in column to nearest half hour
 

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

Rick

"Bob Phillips" wrote in message
...
=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

round time in column to nearest half hour
 

how would i do this in vba...?

Want to implement this in the code after it downloads.


thanx

round time in column to nearest half hour
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Sorry, I just found out my formula doesn't work in the general case.

Rick

"Bob Phillips" wrote in message
...
=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

round time in column to nearest half hour
 

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

"J.W. Aldridge" wrote:
I am uploading data, and in column A it post the times like this:
01.31.06
02.00.06
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
any suggestions?
thanx

round time in column to nearest half hour
 

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


"Bob Phillips" wrote in message
...
=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

round time in column to nearest half hour
 

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think this will do what you want....

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

Rick


"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

round time in column to nearest half hour
 

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

round time in column to nearest half hour
 

On Sat, 24 Nov 2007 09:42:23 -0800 (PST), "J.W. Aldridge"
wrote:

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