 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007round 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 =TIME(LEFT(A1,2),ROUND(MID(A1,4,2)/30,0)*30,0)  Signature--- 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) >I am uploading data, and in column A it post the times like this: > [quoted text clipped - 25 lines] > any suggestions? > thanx Sorry, I just found out my formula doesn't work in the general case. Rick > =TIME(LEFT(A1,2),ROUND(MID(A1,4,2)/30,0)*30,0) > [quoted text clipped - 27 lines] >> any suggestions? >> thanx I didn't mean to post this to your message... sorry. Rick > Sorry, I just found out my formula doesn't work in the general case. > [quoted text clipped - 31 lines] >>> any suggestions? >>> thanx how would i do this in vba...? Want to implement this in the code after it downloads. thanx > 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 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 > =TIME(LEFT(A1,2),ROUND(MID(A1,4,2)/30,0)*30,0) > [quoted text clipped - 27 lines] >> any suggestions? >> thanx I think this will do what you want.... =ROUNDDOWN(24*SUBSTITUTE(A1,".",":"),1)/24 Rick >I am uploading data, and in column A it post the times like this: > [quoted text clipped - 25 lines] > any suggestions? > thanx Sorry, I just found out my formula doesn't work in the general case. Rick >I think this will do what you want.... > [quoted text clipped - 31 lines] >> any suggestions? >> thanx A suggestion: =CONCATENATE(LEFT(A1,2),":",MID(A1,4,2)) D-C Dave >I am uploading data, and in column A it post the times like this: >01.31.06 [quoted text clipped - 5 lines] >any suggestions? >thanx >I am uploading data, and in column A it post the times like this: > [quoted text clipped - 25 lines] >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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.