Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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
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.
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default Converting Numeral to Time equivalent

Hi Claus

Excellent workaround, works perfectly, was never in doubt.

Thank you.

Warm regards
Mark.
  #14   Report Post  
Junior Member
 
Posts: 25
Default 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
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
Converting annual rate of return to quarterly equivalent? Carl LaFong Excel Worksheet Functions 4 May 20th 23 03:43 AM
Converting Standard Time to Military Time in Excel mtvschultz Excel Discussion (Misc queries) 3 May 5th 23 11:42 AM
Changing Arbic numeral to Hindi numeral Ebnlhaitham Excel Discussion (Misc queries) 0 April 2nd 10 11:30 AM
Converting date/time serial values to cumulative time totals... Kevin B Excel Discussion (Misc queries) 4 October 18th 07 05:05 PM
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 08:42 PM.

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

About Us

"It's about Microsoft Excel"