briant22
asked on
how do I round up time to next half hour
I work for a TV station and track all the various start times for TV programs. All the shows start at either the top of the hour ie 12:00 or the bottom of the hour ie 12:30. At times the 12:00 show will begin earlier than scheduled such as11:59, or the 12:30 may start at 12:29 or maybe even 12:31.
My times are currently formatted as numeric (not time), and look like this
10:29 a,
11:59 p,
12:31 p,
5:58 a
I need to round them to look like these
10:29 a should be 10:30 a
11:59 p should be 12:00 a
12:31 p should be 12:30p
5:58 a should be 6:00 a
how can I do this?
My times are currently formatted as numeric (not time), and look like this
10:29 a,
11:59 p,
12:31 p,
5:58 a
I need to round them to look like these
10:29 a should be 10:30 a
11:59 p should be 12:00 a
12:31 p should be 12:30p
5:58 a should be 6:00 a
how can I do this?
ASKER
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.
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
-Bear
ASKER
Yes I would appreciate your assistance.
Thank you very much.
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
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))
ASKER
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
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)
ASKER
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
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
- 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))
ASKER
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
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
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)))
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
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
ASKER
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.
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)))
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
=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
ASKER
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
Can I go back and award more than the 500 points I originally entered? After your reply I will accept the solution..
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That solution you provided is worth 5000 points!!! Love working with you.. I'll have more questions for you soon.
Thanks a million
Thanks a million
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
Open in new window