Entering times in 24 hour format

[Guest]

I am creating a workbook for many users, so I would like to make it as "idiot
proof" as possible. In several of the cells, I want users to enter time in a
24 hr format. If I format the cell to accept "hh:mm" times, then the users
have to enter it exactly that way for it to be accepted. Is there a way to
set up the cell so that if they enter "hhmm", or "hh.mm", (or even "hmm" if
the time being entered is before 0959), the program will convert it to
"hh:mm" format when they tab out of the cell?
For example, user wants to enter 08:00 hrs; so if they type 08:00, 08.00,
0800, or 800 in the cell, when they tab out, it will convert to 08:00.
I also want to do something similar with a date field. MS Word does
something like this with text form fields; I can set up a text field such
that if someone enters "sep 7 07" then tabs out of the field, it will be
changed to "Friday September 7, 2007".
Can I get Excel to do what I want?

 

[Roger Govier]

Hi

One way would be to use some event code on the sheet where the times are
being entered.

Change the Target column number to suit the column number where you are
entering the time data.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column <> 10 Then Exit Sub <--- Change column number to suit
tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen < 3 Or tlen > 4 Then
MsgBox ("Invalid Entry")
GoTo endtime
End If

If Target.Value > 2359 Then
MsgBox ("Can't enter a time past 23:59")
GoTo endtime
End If

If tlen = 3 Then
result = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
result = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If
Target.Value = Format(result, "hh:mm")
endtime:
Application.EnableEvents = True
End Sub

 

[Niek Otten]

Look here:

http://xldynamic.com/source/xld.QDEDownload.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am creating a workbook for many users, so I would like to make it as "idiot
| proof" as possible. In several of the cells, I want users to enter time in a
| 24 hr format. If I format the cell to accept "hh:mm" times, then the users
| have to enter it exactly that way for it to be accepted. Is there a way to
| set up the cell so that if they enter "hhmm", or "hh.mm", (or even "hmm" if
| the time being entered is before 0959), the program will convert it to
| "hh:mm" format when they tab out of the cell?
| For example, user wants to enter 08:00 hrs; so if they type 08:00, 08.00,
| 0800, or 800 in the cell, when they tab out, it will convert to 08:00.
| I also want to do something similar with a date field. MS Word does
| something like this with text form fields; I can set up a text field such
| that if someone enters "sep 7 07" then tabs out of the field, it will be
| changed to "Friday September 7, 2007".
| Can I get Excel to do what I want?

 

[JE McGimpsey]

First, how you format a cell doesn't affect how XL "accepts" a
subsequent entry (except if you set it to Text, which bypasses the
parser).

See

http://cpearson.com/excel/DateTimeEntry.htm

for one technique that you could modify (e.g., by removing punctuation
first).

 

[Guest]

Sorry, I guess I wasn't clear about my own level of expertise (i.e. very
little). I have no idea how to implement your suggestion. Would you mind
more of a step-by-step answer?

Thanks.

 

[Guest]

Thanks for trying, but I am trying to do this in an environment where I can't
install a program on my own desktop, never mind on the dozens that I would
need to install this plug-in for it to work for all those who would need to
have it. Users here don't have admin rights.

 

[Roger Govier]

Hi

Right click on the sheet tab where you enter data and choose View code.
Copy the routine into white pane that appears.
Click back onto your Excel Sheet and as you enter data into the column you
set for the code to be activated, the values entered as say 845 will be
converted to 08:45

 

[Guest]

Roger,
I think we're getting closer; I got the View Code Visual Basic to display,
and I just copied and pasted your instructions into the pane at the right. I
then changed the line:
"If Target.Column <> 10 Then Exit Sub" to
"If Target.Column < 3 Or Target.Column > 15 Then Exit Sub"
since the multiple cells I want this rule to apply in are in columns 3-6 and
12-15, rows 8 through 15.

Two problems arise:
1. If someone actually enters a time with the correct hh:mm form, they get
the error message "Invalid Entry".
2. There are cells in the above range that I don't want the rule to apply
in. For example, I've got some merged cells where I want to enter text, not
times. When I enter text in those, I get the "Can't Enter a Time Past 23:59"
error message. Can I make the rule apply to specific cells, not entire
columns?

 

[Roger Govier]

Hi

There may well be more efficient ways, but this seems to work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tlen As Long, result As String
If Target.Column < 3 Or Target.Column > 15 Then Exit Sub
If Target.Column > 6 And Target.Column < 12 Then Exit Sub
If Target.Row < 8 Or Target.Row > 15 Then Exit Sub

tlen = Len(Target.Value)
Application.EnableEvents = False
If tlen = 1 And Target.Value = 0 Then GoTo endtime

If Left(Target.Value, 1) = "-" Then
MsgBox ("Cannot have negative values")
Target.Value = ""
GoTo endtime
End If

If tlen < 3 Then
MsgBox ("Invalid Entry")
Target.Value = ""
GoTo endtime
End If

If Format(Target.Value, "hh:mm") <= "23:59" And _
Format(Target.Value, "hh:mm") > "00:00" Then GoTo endtime

If Target.Value > 2359 Then
MsgBox ("Can't enter a time past 23:59")
GoTo endtime
End If

If tlen = 3 Then
result = Left(Target.Value, 1) & ":" & Right(Target.Value, 2)
Else
result = Left(Target.Value, 2) & ":" & Right(Target.Value, 2)
End If
Target.Value = result
endtime:
Target.Value = Format(Target.Value, "hh:mm")

If Format(Target.Value, "hh:mm") = "00:00" Then
Select Case MsgBox("Did you really mean to enter " _
& vbCrLf & "a time of Midnight 00:00?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Query Time input")

Case vbYes

Case vbNo
Target.Value = ""
End Select

End If
Application.EnableEvents = True
End Sub

 

[Guest]

Getting really close...

It seems to work great, but if I try to clear the contents of a cell or
multiple cells, I get error messages.
If I select a single cell and hit "Clear Contents", I get the "Invalid
Entry" message box.
If I select several cells at once and hit "Clear Contents", I get:
Run-time error '13':
Type mismatch
and the options to End or Debug.
For either of these situations, if I hit "OK" (or "End"), then I can
continue, but I'd prefer it if these messages didn't appear.
If I hit "Debug" in the second example, then the V-Basic screen opens, and
the following line is highlighted:
tlen = Len(Target.Value)

Any ideas?

Thanks.

 

[Roger Govier]

Hi

You don't expect to be able to delete values as well do you? <vbg>
Sorry, I rushed it out to you and didn't think it through properly.

Rather than posting all the code again, just copy the following lines

If Target.Count > 1 Then Exit Sub
tlen = Len(Target.Value)
If tlen = 0 Then Exit Sub

and paste between

If Target.Row < 8 Or Target.Row > 15 Then Exit Sub

<------ Paste lines here

Application.EnableEvents = False

Hopefully this will sort things out for you.

 

[Guest]

By Jove, I think you've got it!!

Thanks for your help. I really wish I understood this stuff better than I
do. It's great having resources like you to help.

Do you know of any V-Basic resources I might be able to read in order to
better understand this type of thing, and do this myself in future? Remember
I'm coming at this from ground zero.

 

[Roger Govier]

Hi

Most of what I have learnt over the past xx years, has been through reading
the postings in the various groups, trying them out for myself, then
figuring out how they work.
For VBA, then the .programming NG is the place to hang out.

As far as books are concerned, I have
Excel 2003 Power Programming with VBA by John Walkenbach
Professional Excel Development by Bullen, Bovey and Green
Excel Programming Weekend Crash Course by Peter G Aitken.

amongst others, but only because I am too mean to spend the money I should
on other excellent books. Watching and trying techniques in the NG's is as
good a way of learning as any, IMO.

Debra Dalgleish has a much more extensive list at her site
http://www.contextures.com/xlPivot08.html