Solved: Insert time

[mdmackillop]

I cobbled this together to allow simplified entry of times eg 615 for 06:15 but it seems clumsy. Anyone got a better solution?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target = "" Then GoTo Exits
If InStr(1, Target, ":") = 0 Then FixTime Target
End If
Exits:
Application.EnableEvents = True
End Sub

Sub FixTime(Target As Range)
Dim mn As Long, hr As Long
hr = 0
mn = Right(Target, 2)
On Error Resume Next
hr = Left(Target, Len(Target) - 2)
Target = hr & ":" & mn
End Sub

[Simon Lloyd]

Malcom i came across this a while ago its Military Time on Chip Pearsons site http://www.cpearson.com/excel/DateTimeVBA.htm because i need something like that, hope it helps!


Military To Time
This function converts military time, supplied as an integer, to a standard serial time number. For example, it converts 1130 to 11:30, and 1650 to 16:50.
Public Function MilitaryToTime(T1 As Integer)
'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim TT1 As Double

TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilitaryToTime = TT1

End Function
Regards,
Simon

P.S his actual w/s change event for changing the time is very large and complicated....yours is quite neat and easy to follow!

[mdmackillop]

Thanks Simon,
A bit better. I need to add number formatting and that gives

Sub FixTime2(target As Range)
Dim TT1 As Double
TT1 = Int(target / 100) + (((target / 100) - Int(target / 100)) / 0.6) '23.50
TT1 = TT1 / 24
target = Format(TT1, "h:mm")
End Sub

[mdmackillop]

I've found a single line to do this

Target = Int(Target / 100) & ":" & Target Mod 100

[Bob Phillips]

target = Application.Text(target.Value, "00\:00")

[mdmackillop]

:bow: Nice one Bob!

[Aussiebear]

target = Application.Text(target.Value, "00\:00")


Could either one of you two fess up to what this line is? There's a few of us here that didn't follow the pruning of Malcolm's code.

Okay so I lied, but I, me, and myself.... well we don't follow, and an explanation would be good for the soul.

[Bob Phillips]

Could either one of you two fess up to what this line is? There's a few of us here that didn't follow the pruning of Malcolm's code.

Okay so I lied, but I, me, and myself.... well we don't follow, and an explanation would be good for the soul.

Sure, it is not difficult.

In essence, all it does is splitting the miltary time into its compoinents, hours and minutes, within the worksheet Text function, and formatting it to look like standard time (note the like, it isn't time as Excel knows it, it is a string).

[mdmackillop]

Hi Bob.
Using this code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = Application.Text(Target.Value, "00\:00")
Application.EnableEvents = True
End Sub

I'm finding that Exel is recognising it as Time not Text. The formula bar shows data entered as 615 to be 06:15:00 after the code has run.

...and Ted, I'm scratching my head as well over exactly how Excel is coming up with this solution!

[Bob Phillips]

You are absolutely right.

What I was explaining was if you did something like

=TEXT(A1,"00\:00")

in a cell, which is where I got my technique from, does format as General, unless you set a format.

In VBA, because we are writing the value to a cell, it is another example of Excel intelligently (usually :yes) determining the data type, and formatting accordingly.

Sorry for the mis-respresentation.

[Aussiebear]

But we still need to set a target cell or range with an intersect call don't we?

[mdmackillop]

But we still need to set a target cell or range with an intersect call don't we?
We do indeed, or things will get very confusing very quickly!

[Bob Phillips]

But we still need to set a target cell or range with an intersect call don't we?

Only if we want to write to the worksheet, and only if it is within event code.

[mdmackillop]

... an explanation would be good for the soul.
Hi Ted,
I finally figured it out.
Because of the way I was thinking, I assumed \ had something to do with integer division, as in

Target = Target \ 100 & ":" & Target MOD 100

Digging into help for formatting, I finally got hold of an alternative use for \. Basically the entered number gets formatted as 0000 and \: forces a colon in the middle. eg 615 becomes 06:15 and clever Excel does the rest.
Thanks for the mental work-out Bob!:coffee:
Regards
MD

[Bob Phillips]

Because of the way I was thinking, I assumed \ had something to do with integer division, as in

Target = Target \ 100 & ":" & Target MOD 100

Malcolm,

Never occurred to me that someone might have thought that :).

The clue was in the fact that it was inside quotation marks. The \ escapes a single text character, multiples have to be individually escaped, or a block within quotation marks. Formatting is an under-used Excel trick IMO.

[Aussiebear]

So if I was to write
Target = Target \ 100 & ":" & ":" & Target Mod 100

This would possibly give me Hrs: Mins: Secs then?

[Bob Phillips]

So if I was to write
Target = Target \ 100 & ":" & ":" & Target Mod 100

This would possibly give me Hrs: Mins: Secs then?

No, use



Target = Application.text(Target.Value, "00\:00\:00")

[mdmackillop]

As Bob says, but I'd be a bit wary of that. Entering time as 0615 seems "natural", 061500 not so. I'd build in a check for 5/6 digits to help avoid errors in data entry.

[Bob Phillips]

Totally agree with Malcolm's last point.

[Aussiebear]

Just an after thought ( Yes I know I'm s l o w)




Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = Application.Text(Target.Value, "00\:00")
'Insert lines here....
Application.EnableEvents = True
End Sub


Intersect line.
If Not Intersect (Target, Range("A1:A25")) Is Nothing Then
If Target = "" Then GoTo Exits
End If
End If
Exits:

Am I getting close to the right type of code by thinking along these lines

[mdmackillop]

Hi Ted,
Here's the code with comments

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits 'make sure events gets re-enabled
Application.EnableEvents = False 'stop any change below triggering this code
If Not Intersect(Target, Range("A1:A25")) Is Nothing Then
If Target = "" Then GoTo Exits 'if cell is being cleared then don't run
If InStr(1, Target, ":") = 0 Then 'if cell contains : then don't run
Target = Application.Text(Target.Value, "00\:00") 'reformat entry
End If
End If
Exits:
Application.EnableEvents = True 'reset events to true
End Sub
One other check line to avoid possible errors, but not really needed in this case is

If Target.Cells.Count > 1 Then Exit Sub

[Aussiebear]

Yes I was wondering if in the case of a range if the first cell was a time value and the next cell was blank would it exit.

At work we use a spreadsheet to enter the values Time. Mill Roll hours, grain type, throughtput tonnage per hour and I constantly see the mill operators struggle with entering the Shift Colon key combo when it would be far simpler to enter the datavalues from the right hand numerical keypad.

They enter these values on an hourly basis with each daus production recorded on an individual sheet. If was confident I would add the code to ease their day.

Ted

[Aussiebear]

Just as a quick ( yep should have occurred earlier I know) after thought.

The line If Target.Cells.Count > 1 Then Exit Sub, how is this effected by a range?

Ted

[Bob Phillips]

If a user tries to change many cells at once (Ctrl-Enter) then it needs to be handled. Malcolm's code checks that only one is being checked, else it exits.

Have you looked at http://www.cpearson.com/excel/DateTimeEntry.htm

[mdmackillop]

If was confident I would add the code to ease their day.
Giving code for others to use is a BIG step. That's where you need to check for possible input errors and get your error handling in place. Leaving EventsEnable = False can cause lots of problems. It's usually easy enough to sort your own, but if the code bombs out, you spend all day trying to correct/change the code elsewhere.
Good luck!

[Aussiebear]

Suicide Code Jockey.... It sort of fits me... don't you think?

[Bob Phillips]

Suicide Code Jockey.... It sort of fits me... don't you think?

Excel fundamentalist!

[Aussiebear]

One last question, What happens if you have more than one range on the sheet? Is this a simple case of ;

If Not Intersect ( Target, Range( "A1: A25"", "D1:25"")) Is Nothing Then

[mdmackillop]

To be honest Ted, the event code I posted is I think "overcomplicated" for inclusion in the event sub. I would probably put all the code in a separate sub and pass the Target as a parameter, something like

If Target.Cells.Count = 1 then SortTime Target

I'd then sort out the ranges etc. within that macro. With regard to multiple ranges, join them into one using Union, then check the intersect of Target with the union range.

[Bob Phillips]

One last question, What happens if you have more than one range on the sheet? Is this a simple case of ;

If Not Intersect ( Target, Range( "A1: A25"", "D1:25"")) Is Nothing Then



If Not Intersect(Target, Me.Range("A1:A25,D1:D25")) Is Nothing Then

[Aussiebear]

Thanks Bob.