Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Team
Was hoping someone was able to point me in the right direction please: I am trying to speed up data entry by just entering a number and have in autoconvert to time, eg 515 = 5:15 or 1415 = 14:15, reducing keystrokes will significantly speed up this process in Columns( 10 & 11 ). I tried inserting it into my Worksheet_Change event, but it will not fire ( although it fires if I run the sub on it's own ). I have this so far, so if anyone can point me in the right direction, I will be supergrateful. As always, much appreciation TIA Mark. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C3:C43")) Is Nothing Then If Target.Cells.Count 1 Then Exit Sub Select Case Target.Column Case 3 If Target.Value = "YARD" Or Target.Value = "C0654" Then Target.Offset(0, 7).Select Else Target.Offset(0, 1).Select End If Case 8 Target.Offset(0, 2).Select Case 10 NumberToTime Target.Offset(0, 1).Select Case 11 NumberToTime End Select End If End Sub Private Sub NumberToTime() Dim rCell As Range Dim iHours As Integer Dim iMins As Integer For Each rCell In Selection If IsNumeric(rCell.Value) And Len(rCell.Value) 0 Then iHours = rCell.Value \ 100 iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins / 60) / 24 rCell.NumberFormat = "[h]:mm" End If Next End Sub 
#2
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Geez, I really miss the good old days when you had a small window of opportunity to edit out any errors or grammar issues in your posts. Rather than having the same line in a post twice... lol 
#3
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Mark,
Am Tue, 17 Nov 2020 02:34:11 0800 (PST) schrieb NoodNutt: I am trying to speed up data entry by just entering a number and have in autoconvert to time, eg 515 = 5:15 or 1415 = 14:15, reducing keystrokes will significantly speed up this process in Columns( 10 & 11 ). I tried inserting it into my Worksheet_Change event, but it will not fire ( although it fires if I run the sub on it's own ). try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C9:C42,H9:H43,J9:K42")) Is Nothing _ Or Target.Count 1 Then Exit Sub Select Case Target.Column Case 3 If Target.Value = "YARD" Then Target.Offset(, 7).Select Else Target.Offset(, 1).Select End If Case 8 Target.Offset(, 2).Select Case 10, 11 If IsNumeric(Target) And Target <= 2359 Then Select Case Len(Target) Case 3 Target = TimeValue(Left(Target, 1) & ":" & Right(Target, 2)) Case 4 Target = TimeValue(Left(Target, 2) & ":" & Right(Target, 2)) End Select Target.NumberFormat = "hh:mm" End If End Select End Sub Regards Claus B.  Windows10 Office 2016 
#4
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Claus
Much appreciation for this. Alas it did not work though. I actually used something similar to this earlier in the day, which garnered the same outcome. It's not triggering the cell change. Even with the cells formatted as numbers, and entering say, 515, it stays as 515. I also noticed you copied the Case section from earlier as it contained Case 3 & 4 which should have reflected Case 10 & 11. So this is what I ended up with. Also, the NumberFormat is [h]:mm as I am directly calculating the cells as numbers. If Not Intersect(Target, Range("C3:C43")) Is Nothing Then If Target.Cells.Count 1 Then Exit Sub Select Case Target.Column Case 3 If Target.Value = "YARD" Or Target.Value = "C0654" Then Target.Offset(0, 7).Select Else Target.Offset(0, 1).Select End If Case 8 Target.Offset(0, 2).Select Case 10, 11 If IsNumeric(Target) And Target <= 2800 Then Select Case Len(Target) Case 10 Target = TimeValue(Left(Target, 1) & ":" & Right(Target, 2)) Target.Offset(0, 1).Select Case 11 Target = TimeValue(Left(Target, 2) & ":" & Right(Target, 2)) End Select Target.NumberFormat = "[h]:mm" End If End Select End If 
#5
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Mark,
Am Tue, 17 Nov 2020 03:04:17 0800 (PST) schrieb NoodNutt: Much appreciation for this. Alas it did not work though. I actually used something similar to this earlier in the day, which garnered the same outcome. It's not triggering the cell change. Even with the cells formatted as numbers, and entering say, 515, it stays as 515. I also noticed you copied the Case section from earlier as it contained Case 3 & 4 which should have reflected Case 10 & 11. So this is what I ended up with. Len(Target) can be 3 or 4 e.g. 515 or 1425 Try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C3:C43,H3:H43,J3:K43")) Is Nothing _ Or Target.Count 1 Then Exit Sub Select Case Target.Column Case 3 If Target.Value = "YARD" Or Target.Value = "C0654" Then Target.Offset(0, 7).Select Else Target.Offset(0, 1).Select End If Case 8 Target.Offset(0, 2).Select Case 10, 11 If IsNumeric(Target) And Target <= 2359 Then Select Case Len(Target) Case 3 Target = TimeValue(Left(Target, 1) & ":" & Right(Target, 2)) Target.NumberFormat = "[h]:mm" Target.Offset(0, 1).Select Case 4 Target = TimeValue(Left(Target, 2) & ":" & Right(Target, 2)) Target.NumberFormat = "[h]:mm" End Select End If End Select End Sub Regards Claus B.  Windows10 Office 2016 
#6
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Claus
I just rang you. It went to message. I just noticed an alteration you made earlier and made the same change. I converts the number to time, but it is not the correct time. LOL Entering 515 returns: 12360:00 ( the formula bar has this: 29/05/1901 12:00:00 AM ) This is exactly what was happening to me prior: When I used the following in helper cells I got the this: Cell B2: 515 Cell B3: I use: =B2/100 This gives me: (5.15) Cell B4: I use: =LEFT(B3,FIND(".",B3)1) This gives me: (5___) Cell B5: I use: =MOD(B3,100) This gives me: (___15) Cell B6: I use: = B4&":"&B5 to arrive at this: (5:15) So,. I tried the following which, of course failed to fire.. lol Case 10 Target.Offset(0, 20).FormulaR1C1 = "RC10/100" Target.Offset(0, 21).FormulaR1C1 = "Left(RC10, Find(""."", RC10)  1)" Target.Offset(0, 22).FormulaR1C1 = "=MOD(RC10,100)" Target = Target.Offset(0, 21).Value & ":" & Target.Offset(0, 22).Value Target.NumberFormat = "[h]:mm" Target.Offset(0, 1).Select Case 11 Target.Offset(0, 23).FormulaR1C1 = "RC10/100" Target.Offset(0, 24).FormulaR1C1 = "Left(RC10, Find(""."", RC10)  1)" Target.Offset(0, 25).FormulaR1C1 = "=MOD(RC10,100)" Target = Target.Offset(0, 24).Value & ":" & Target.Offset(0, 25).Value Target.NumberFormat = "[h]:mm" Cheers Mark. 
#7
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
One of the issues with the time is that some of the employees work past midnight, and given there is no Date column for each Start & Finish, I shortcut it by extending the time past 24.00, so if someone Started @ 14:00 and Finished @ around 2:00, I would enter the Finish time as 26:00; it still calculates to 10.00 hours.
This means this: "If IsNumeric(Target) And Target <= 2359 Then" needs to be more like: "If IsNumeric(Target) And Target <= 2800 Then" to cater for anyone working to 4.00am. I sent you another message outlining my Helper Cells workaround, which may help. I can also ring you again if you're near your phone. Cheers Mark. 
#8
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Mark,
Am Tue, 17 Nov 2020 04:10:45 0800 (PST) schrieb NoodNutt: One of the issues with the time is that some of the employees work past midnight, and given there is no Date column for each Start & Finish, I shortcut it by extending the time past 24.00, so if someone Started @ 14:00 and Finished @ around 2:00, I would enter the Finish time as 26:00; it still calculates to 10.00 hours. This means this: "If IsNumeric(Target) And Target <= 2359 Then" needs to be more like: "If IsNumeric(Target) And Target <= 2800 Then" to cater for anyone working to 4.00am. I sent you an email Regards Claus B.  Windows10 Office 2016 
#9
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
NoodNutt wrote:
Was hoping someone was able to point me in the right direction please: I am trying to speed up data entry by just entering a number and have in autoconvert to time, eg 515 = 5:15 or 1415 = 14:15, reducing keystrokes will significantly speed up this process in Columns( 10 & 11 ). I tried inserting it into my Worksheet_Change event, but it will not fire ( although it fires if I run the sub on it's own ). I have this so far, so if anyone can point me in the right direction, I will be supergrateful. Just to add what's already been said, here's what Claus basically wrote for me several years ago: Sub fixTimes(what As Range) Dim cell As Range For Each cell In what 'fixed by Claus Busch If (InStr(cell.Value, ".") < 1) And (IsNumeric(cell.Formula)) Then Select Case cell.Formula Case 0 To 2359 cell.Value = (CLng(cell.Formula) \ 100) & ":" & _ (CLng(cell.Formula) Mod 100) End Select End If Next End Sub The only thing is, it assumes that you're entering valid times, not something like "199".  "What do you fear, lady?" he asked. "A cage," she said. 
#10
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Auric
It's an awesome little tidbit of code, and Claus was very generous in his time in altering it to suit my needs also. The original code didn't quite fit how I entered time after midnight using [h]:mm format, so if someone finished @ "2:30am" I would enter "26:30" instead, this way I don't need to worry about adding a date to bridge times or those that work afternoon/night shift. I am wondering if you happened to experience this small anomaly. All but one of the values I have entered have converted, except "600". I have all the cells formatted as General, and every other time entered has zero issue converting, so I'm stumped as to why "600" is converting to "0:25". I know "0.25" is the represented value for "6:00" when it is converted, so this is an interesting twist. Claus, if by chance you're reading this, I also found this issue in the example file you sent me. Cheers Mark. 
#11
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
NoodNutt wrote:
It's an awesome little tidbit of code, and Claus was very generous in his time in altering it to suit my needs also. The man is an absolute machine when it comes to this stuff. The original code didn't quite fit how I entered time after midnight using [h]:mm format, so if someone finished @ "2:30am" I would enter "26:30" instead, this way I don't need to worry about adding a date to bridge times or those that work afternoon/night shift. That's another problem that Claus solved for me, around the same time he wrote the other code. See the thread "Formula than understands midnight?" (yes, I typo'd "that", and I never even noticed until just now...) over in mpew.functions: https://tinyurl.com/y3cve9jx Redirects to (watch the wordwrap): https://groups.google.com/forum/#! topic/microsoft.public.excel.worksheet.functions/aDkj8ab8iwY It uses a worksheet formula, but it could be adapted to VBA. I am wondering if you happened to experience this small anomaly. All but one of the values I have entered have converted, except "600". I have all the cells formatted as General, and every other time entered has zero issue converting, so I'm stumped as to why "600" is converting to "0:25". I know "0.25" is the represented value for "6:00" when it is converted, so this is an interesting twist. Claus, if by chance you're reading this, I also found this issue in the example file you sent me. The code I posted doesn't do this. (I just checked.) Post the code you're using.  A blazing sun that never sets. 
#12
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Mark,
Am Wed, 18 Nov 2020 20:38:30 0800 (PST) schrieb NoodNutt: Claus, if by chance you're reading this, I also found this issue in the example file you sent me. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C3:C43,H3:H43,J3:K43")) Is Nothing _ Or Target.Cells.Count 1 Then Exit Sub Dim iHrs As Integer, iMins As Integer Select Case Target.Column Case 3 If Target.Value = "YARD" Or Target.Value = "C0654" Then Target.Offset(0, 7).Select Else Target.Offset(0, 1).Select End If Case 8 Target.Offset(0, 2).Select Case 10, 11 If IsNumeric(Target) And Target 2 Then iHrs = Target.Value \ 100 iMins = Target.Value Mod 100 Target = TimeSerial(iHrs, iMins, 0) Target.NumberFormat = "[h]:mm" Target.Offset(0, 1).Select End If End Select End Sub Regards Claus B.  Windows10 Office 2016 
#13
Posted to microsoft.public.excel.programming




Converting Numeral to Time equivalent
Hi Claus
Excellent workaround, works perfectly, was never in doubt. Thank you. Warm regards Mark. 
#14




Try this:
Steps
Begin by typing in =TEXT( Select or type in the range reference that contains the numeric value you want to convert B3, Divide the numeric value by 24. Type in the format code that includes h, m and s placeholders “[h] “”hours,”” m “”minutes, “” s “”seconds””” Close the formula with ) and press Enter to complete it. Hope this helps. You can get more tips about excel on O365CloudExperts Regards, Jerry 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Converting annual rate of return to quarterly equivalent?  Excel Worksheet Functions  
Converting Standard Time to Military Time in Excel  Excel Discussion (Misc queries)  
Changing Arbic numeral to Hindi numeral  Excel Discussion (Misc queries)  
Converting date/time serial values to cumulative time totals...  Excel Discussion (Misc queries)  
How do I Change text (0130PM) into military time equivalent?  Excel Discussion (Misc queries) 