Home |
Search |
Today's Posts |
#11
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Hi Claus
Excellent workaround, works perfectly, was never in doubt. Thank you. Warm regards Mark. |
#14
![]() |
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
Thread | Forum | |||
Changing Arbic numeral to Hindi numeral | Excel Discussion (Misc queries) | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
Converting annual rate of return to quarterly equivalent? | Excel Worksheet Functions | |||
How do I Change text (0130PM) into military time equivalent? | Excel Discussion (Misc queries) |