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