how do I round up time to next half hour

Since the cells are numeric and not time, it becomes a little more convoluted.  The formula below will do what you want.  Just cut and paste into cell A2.  Then you can copy it to any cell to the right of the value you want to adjust.  For example if 10:29 a is in cell c5, paste the copy to C6.

Let me know if you have questions.  There may be a cleaner way, but this is what I came up with on a quick shot.  We could also right a macro to do this, but a formula works.

- Bear

=IF((INT(MID(A1;SEARCH(":";A1;1)+1;2))<15) OR (INT(MID(A1;SEARCH(":";A1;1)+1;2))>45);LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2))

Select allOpen in new window

This seems to work excellent, but I notice if the time is 6:59 a, the formula rounds to 6:00 a, when it should be 7:00 a, or if the time is 11:58 p, it returns 11:00 p, but should be 12:00 a.  Might this be possible?  

Its no problem if this is not possible, since there are not too many occurrences of 58 or 59. Otherwise your formula is like a symphony!

I;ll wait for your reply before grading and accepting your answer.

Sorry forgot about that scenario.  To Incorporate that I will want to move to a macro. Just let me know if you want me to pursue that option and I can work on it tomorrow evening.

-Bear

Yes I would appreciate your assistance.  
Thank you very much.

I came up with a formula that will work and not be more convoluted than the original (The subconscious is a great tool).  Try the one below.  (Again cut and paste from here into cell A2 and then copy to other cells)

Let me know how it goes

-Bear

=CHOOSE(ROUND(MID(A1;SEARCH(":";A1;1)+1;2)/30;0)+1;LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))+1&"00"&RIGHT(A1;2))

Select allOpen in new window

Good Morning,I did attempt the revised formula and entered the results below.  Column A representing the times to be rounded, and column B representing the returned values.  How intriguing!  It seems as though the error occurs when minutes exceed 45.

    A            B
01:31 p      01:30 p
01:31 p      01:30 p
01:32 p      01:30 p
07:59 a      1.2916666666666700 a
05:46 a      1.2083333333333300 a
06:29 a      06:30 a
08:45 a      1.3333333333333300 a
06:44 p      06:30 p
11:59 p      1.4583333333333300 p
09:59 a      1.37500 a

Brian

Sorry copied the wrong line.  Try this . .  

=CHOOSE(ROUND(MID(A1;SEARCH(":";A1;1)+1;2)/30;0)+1;LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1)-1))+1&":00"&RIGHT(A1;2)

Select allOpen in new window

Good Evening Master Bear!

I have tested this formula, and here are the results.

A              B
01:31 p      Err:502
01:31 p      Err:502
01:31 p      Err:502
01:32 p      Err:502
07:59 a      8:00 a
05:59 a      6:00 a
06:59 a      7:00 a
08:59 a      9:00 a
06:59 p      7:00 p
06:59 p      7:00 p
11:59 p      12:00 p

So the formula did correct the issue when minutes exceed 45, but it returns an error, on the others.  If you have time to do a macro that would be great.  However, if not, I can use the first formula with complete confidence on cells where minutes are less than 45, and I will use this most recent formula when the minutes exceed 45.  

If the macro is not an option, I will certainly accept this as the solution and award the points and the highest grade.

I will await your response.

Thank you

Brian

We can conquer this with a formula.  I just need to stop making simple mistakes.  Missed a parenthesis.  Run it against your sample data as I had a little more time this time.  I think it works finally.  Sorry for the delay, but don't close this until I have it right.

- Bear

=CHOOSE(ROUND(MID(A1;SEARCH(":";A1;1)+1;2)/30;0)+1;LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1)-1)+1&":00"&RIGHT(A1;2))

Select allOpen in new window

Good evening,

Sorry for my delay in responding.

I ran the sample against the formula, and it is 99.9%.  You are good indeed!
The other .1% lies in converting the "a" to the "p" in the example of 11:59 a, which should return
12:00 p, and 11:59 p which should return 12:00 a.

I'll wait for your response.

Brian

No Problem on the delay.  We will get this right somehow.  I decide to go ahead with a two prong approach.  First I wanted to see if I can conquer this beast of a formula.  The result is definitely a beast.  However, I believe it will now work.  It is below.

My second approach was to go ahead with a Macro, in case you decided the formula was too complex.  I will post that shortly.
- Bear

=CHOOSE(ROUND(MID(A1;SEARCH(":";A1;1)+1;2)/30;0)+1;LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2);IF(LEFT(A1;SEARCH(":";A1;1)-1)="11";IF(RIGHT(A1;1)="a";LEFT(A1;SEARCH(":";A1;1)-1)+1&":00 p";LEFT(A1;SEARCH(":";A1;1)-1)+1&":00 a");LEFT(A1;SEARCH(":";A1;1)-1)+1&":00"&RIGHT(A1;2)))

Select allOpen in new window

Here is the macro.  To use this macro you need to add it to you document.
Go to Tools >> Macros >> Organize Macros >> OpenOffice.org Basic
In Macro From, Click on your document
Click the New button
Click OK at the new Module Window
Paste the code below over everything in that window
Close the Macro Window
Save your document
Now you have a new formula titled adjtime.  To use, in any cell put
=adjtime(cellrefwithdata)

For example if 01:15 a is in A1 and you want the new value in b1 put
=adjtime(a1)
and you should get a good result - I hope.

Make sure your macro settings allow macros (Tools >> Options >> OpenOffice.org >> Security  >> Macro Security)

It is now up to you which method you pick.  Let me know
-Bear

Function AdjTime(CellRef)
	' Adjusts Time in CellRef that is in a string format
	' of hh:mm a
	' Rounds the minutes to nearest 1/2 hour
	Dim sHr as string	' Hour
	Dim sMin as string	' Minutes
	Dim bAM as Boolean  ' AM Flag
	Dim iCPos as integer	' Position of Colong
	Dim sTime as string	' Resulting String
 
	' Find Positin of Colon
	iCPos = strSearch(CellRef, ":")
 
	' Find Hour
	sHr = Left(CellRef, iCPos-1)
	
	' Find Minute
	sMin = Mid(CellRef, iCPos+1, 2)
	
	' See if AM
	If Right(CellRef, 1) = "a" Then bAM = True
	
	' Adjust Time
	Select Case True
		Case Cint(sMin) < 15
			sMin = "00"
		Case Cint(sMin) < 45
			sMin = "30"
		Case Else
			sMin = "00"
			' Add one to hour
			sHr = Cstr(Cint(sHr) + 1)
			If sHr = "12" Then bAM = not bAM
	End Select
	
	' Set First part of return hh:mm
	sTime = sHr & ":" & sMin & " "
			
	' set AM/PM section
	If bAM Then
		sTime = sTime & "a"
	Else
		sTime = sTime & "p"
	End If
	
	' Return Result to Cell
	AdjTime() = sTime
	
End Function
	
Private Function StrSearch(SearchString as string, Lookup as string) as integer
	' Search String for Lookup and returns position in String
	Dim i as integer	' Counter
	Dim bFound as Boolean	' Found Flag
	
	' Loop through string
	Do Until (bFound) or (i > len(SearchString))
		i = i + 1
		If Mid(SearchString, i, 1) = LookUp then bFound = True
	Loop
	
	If bFound then 
		' Found Lookup Value - return position
		StrSearch = i
	Else
		' Did not find Lookup Value, return zero
		StrSearch = 0
	End IF
		
End Function

Select allOpen in new window

Master Bear,
Here are the results of the revised formula;  notice the 12:58 and 12:46 times.

A      B
06:59 p      7:00 p
05:59 a      6:00 a
11:59 a      12:00 p
11:59 p      12:00 a
11:55 a      12:00 p
11:46 p      12:00 a
10:59 a      11:00 a
12:58 a      13:00 a
12:46 a      13:00 a

I tried the macro, and it returns no values.  I could be doing something wrong, and will try it again.  when I enter #name? in cell b1, it does show me   =adjtime(cellrefwithdata) in the formula bar.

I am perfectly content with the formula and can forego the macro, but I will wait for your response and reply immediately.

Maybe someday we will cover all the possibilities.  Here is a new formula to try.  Now this formula will not add the zero back in in front of a single digit hour (this thing is a monster already).  I was able to fix that in the macro and will post it shortly.

=CHOOSE(ROUND(MID(A1;SEARCH(":";A1;1)+1;2)/30;0)+1;LEFT(A1;SEARCH(":";A1;1))&"00"&RIGHT(A1;2);LEFT(A1;SEARCH(":";A1;1))&"30"&RIGHT(A1;2);IF(LEFT(A1;SEARCH(":";A1;1)-1)="11";IF(RIGHT(A1;1)="a";LEFT(A1;SEARCH(":";A1;1)-1)+1&":00 p";LEFT(A1;SEARCH(":";A1;1)-1)+1&":00 a");LEFT(A1;SEARCH(":";A1;1)-1)+1&":00"&RIGHT(A1;2)))

Select allOpen in new window

On the macro, In cell B1 it should have
=adjtime(A1)
In the formula bar.

Here is an adjusted macro to fix a few problems.  Just cut and paste over the current code.  If you want, you can change the extension on your spreadsheet to .txt, attach to your next post and I can work straight in your spreadsheet and post fixes back.

- Bear

Function AdjTime(CellRef)
	' Adjusts Time in CellRef that is in a string format
	' of hh:mm a
	' Rounds the minutes to nearest 1/2 hour
	Dim sHr as string	' Hour
	Dim sMin as string	' Minutes
	Dim bAM as Boolean  ' AM Flag
	Dim iCPos as integer	' Position of Colong
	Dim sTime as string	' Resulting String
 
	' Find Positin of Colon
	iCPos = strSearch(CellRef, ":")
 
	' Find Hour
	sHr = Left(CellRef, iCPos-1)
	
	' Find Minute
	sMin = Mid(CellRef, iCPos+1, 2)
	
	' See if AM
	If Right(CellRef, 1) = "a" Then bAM = True
	
	' Adjust Time
	Select Case True
		Case Cint(sMin) < 15
			sMin = "00"
		Case Cint(sMin) < 45
			sMin = "30"
		Case Else
			sMin = "00"
			' Add one to hour
			sHr = Cstr(Cint(sHr) + 1)
			Select Case True
				Case Len(sHr)=1
					sHr = "0" & sHr
				Case sHr = "12"
					bAM = not bAM
				Case sHr = "13"
					sHr = "01"
			End Select
	End Select
	
	' Set First part of return hh:mm
	sTime = sHr & ":" & sMin & " "
			
	' set AM/PM section
	If bAM Then
		sTime = sTime & "a"
	Else
		sTime = sTime & "p"
	End If
	
	' Return Result to Cell
	AdjTime() = sTime
	
End Function
	
Private Function StrSearch(SearchString as string, Lookup as string) as integer
	' Search String for Lookup and returns position in String
	Dim i as integer	' Counter
	Dim bFound as Boolean	' Found Flag
	
	' Loop through string
	Do Until (bFound) or (i > len(SearchString))
		i = i + 1
		If Mid(SearchString, i, 1) = LookUp then bFound = True
	Loop
	
	If bFound then 
		' Found Lookup Value - return position
		StrSearch = i
	Else
		' Did not find Lookup Value, return zero
		StrSearch = 0
	End IF
		
End Function

Select allOpen in new window

The macro works perfectly!! It is flawless!!

Can I go back and award more than the 500 points I originally entered?  After your reply I will accept the solution..

Thank you

That solution you provided is worth 5000 points!!!  Love working with you.. I'll have more questions for you soon.

Thanks a million