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 auto-convert 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 super-grateful.

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

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

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 auto-convert 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

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

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

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.

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.

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

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
auto-convert 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 super-grateful.

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.

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.

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.

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

Hi Claus

Excellent workaround, works perfectly, was never in doubt.

Thank you.

Warm regards
Mark.

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