| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I round time down to the nearest half hour? | Excel Worksheet Functions | |||
| how do i round time to the nearest half a minute | Excel Discussion (Misc queries) | |||
| How do I round time to the nearest quarter of an hour | New Users to Excel | |||
| Need to round the time to the nearest quarter hour. Help | Excel Discussion (Misc queries) | |||
| To reformat a culculated date/time round-up to the nearest ½ hour | Excel Programming | |||
Linear Mode
