Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 17th 20, 11:34 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 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  
Old November 17th 20, 11:37 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


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  
Old November 17th 20, 11:44 AM 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 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  
Old November 17th 20, 12:04 PM 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

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  
Old November 17th 20, 12:30 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 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  
Old November 17th 20, 01:03 PM 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

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  
Old November 17th 20, 01:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
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  
Old November 17th 20, 01:15 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 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  
Old November 17th 20, 11:02 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:

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  
Old November 19th 20, 05:38 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 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.


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:15 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