Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 19th 20, 07:07 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 532
Default 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   Report Post  
Old November 19th 20, 07:17 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default 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   Report Post  
Old November 20th 20, 01:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default Converting Numeral to Time equivalent

Hi Claus

Excellent workaround, works perfectly, was never in doubt.

Thank you.

Warm regards
Mark.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing Arbic numeral to Hindi numeral Ebnlhaitham Excel Discussion (Misc queries) 0 April 2nd 10 11:30 AM
Converting Standard Time to Military Time in Excel mtvschultz Excel Discussion (Misc queries) 2 February 15th 08 08:30 PM
Converting date/time serial values to cumulative time totals... Kevin B Excel Discussion (Misc queries) 4 October 18th 07 05:05 PM
Converting annual rate of return to quarterly equivalent? Carl LaFong Excel Worksheet Functions 3 June 1st 07 11:44 AM
How do I Change text (0130PM) into military time equivalent? sedoc Excel Discussion (Misc queries) 5 August 3rd 06 08:15 PM


All times are GMT +1. The time now is 09:11 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017