Validating times

If your users are 10-key proficient, believe me, they'll *much prefer* the
double decimal entry, since it's accomplished with a single hand.

Every Monday, the timecard entry clerks at our plants swear by this method.

As for the validation:

Simply select the *entire* Column T, instead of selecting just T1, by
clicking on the "T" in the column header, and use the exact same formula:

=T1S1

When you select the entire column, you'll see that the focus cell is still
T1 (colored white) by default.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Les Isaacs" wrote in message
...
Hello RD

Many thanks for your help with this. I understand your suggestion about
using autocorrect, but in fact I think it will be as easy to instruct the
users to us the colon as it would be to instruct them that 2 dots will
also work!

I tried your suggestion about setting the validation for one cell, then
dragging that down to the other cells, and that worked great. The only
minor problem is how many cells should I drag down to - because I don't
know how many rows the user might enter data on. I guess I can just drag
down a huge number of rows, and that will work: but is there a way the
validation could be set for the whole column?

Thanks again for your help.
Les


"RagDyeR" wrote in message
...
You might try this:

Since XL *needs* the colon to recognize entries as time, use "Auto
Correct"
to help in entering the colon with keystrokes from the num keypad.


Tools AutoCorrect


And enter 2 periods (dots - decimals) [ .. ] to be replaced with a colon
[
: ].


Now, users can stick with using the num keypad to enter XL recognizable
times.

8..00

or

15..30

As for the "Data Validation issue:

Say your first start time is in S1.

Click in T1, then Data Validation

Under "Allow, click on "Custom", and enter this formula:

=T1S1

Then OK

Click in T1 and drag down the empty cell to copy the validation as
needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===








"Jack Isaacs" wrote in message
...
Hello Peo

Thanks for your reply.
I was just hoping that entering 8 would be accepted as 8:00 - lazy I know
by
the users would like this! If that's not possible I (they) can live with
it.
The harder problem is setting the constraint for 'end time' to be
'start
time' - for the whole column. Can this be done?

Thanks again
Les


"Peo Sjoblom" wrote in message
...
Why would you enter 8? 8 is 8*24 hours so it is 8 days. You need to
enter
it as 8:00,
formatting has nothing to do with that. Do datavalidationallow and
select time and set your constraints there


--


Regards,


Peo Sjoblom


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1
column
per field. One of the columns will be 'start time' (column S) and the
next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when
I
tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet
range
in a data validation formula.

Hope someone can help

Many thanks
Les

SWEATSHOPS ! ? ! ?

I'll have you know we pay top dollar to our help, that is, if they're pretty
....

ERrr, I meant, if they're pretty good typists!bg

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
The difference is that if you type numbers using the numpad you won't find
the colon there but you will find the period. I believe Ragdyer introduced
this in one of his sweatshops and they liked it a lot


--


Regards,


Peo Sjoblom


"Les Isaacs" wrote in message
...
Hello RD

Many thanks for your help with this. I understand your suggestion about
using autocorrect, but in fact I think it will be as easy to instruct the
users to us the colon as it would be to instruct them that 2 dots will
also work!

I tried your suggestion about setting the validation for one cell, then
dragging that down to the other cells, and that worked great. The only
minor problem is how many cells should I drag down to - because I don't
know how many rows the user might enter data on. I guess I can just drag
down a huge number of rows, and that will work: but is there a way the
validation could be set for the whole column?

Thanks again for your help.
Les


"RagDyeR" wrote in message
...
You might try this:

Since XL *needs* the colon to recognize entries as time, use "Auto
Correct"
to help in entering the colon with keystrokes from the num keypad.


Tools AutoCorrect


And enter 2 periods (dots - decimals) [ .. ] to be replaced with a colon
[
: ].


Now, users can stick with using the num keypad to enter XL recognizable
times.

8..00

or

15..30

As for the "Data Validation issue:

Say your first start time is in S1.

Click in T1, then Data Validation

Under "Allow, click on "Custom", and enter this formula:

=T1S1

Then OK

Click in T1 and drag down the empty cell to copy the validation as
needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===








"Jack Isaacs" wrote in message
...
Hello Peo

Thanks for your reply.
I was just hoping that entering 8 would be accepted as 8:00 - lazy I
know by
the users would like this! If that's not possible I (they) can live with
it.
The harder problem is setting the constraint for 'end time' to be
'start
time' - for the whole column. Can this be done?

Thanks again
Les


"Peo Sjoblom" wrote in message
...
Why would you enter 8? 8 is 8*24 hours so it is 8 days. You need to
enter
it as 8:00,
formatting has nothing to do with that. Do datavalidationallow and
select time and set your constraints there


--


Regards,


Peo Sjoblom


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1
column
per field. One of the columns will be 'start time' (column S) and the
next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and
when I
tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet
range
in a data validation formula.

Hope someone can help

Many thanks
Les

Hi Les,

I use Chip Pearson's Time entry for timesheets.
The users love it and don't seem to get confused
too often.

With Chips code
8:00 is entered as 800 on the numpad
8:35 is entered as 835 on the numpad
etc

Here is the link.
http://www.cpearson.com/excel/DateTimeEntry.htm

Don't be intimidated by the VBA code, I'm VBA
illiterate but I still managed to work it out from
Chips explanations in his more information link.

HTH
Martin


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that will
be used for simple data entry of name, address, etc etc. - 1 column per
field. One of the columns will be 'start time' (column S) and the next
column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when I
tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet range
in a data validation formula.

Hope someone can help

Many thanks
Les

That is very similar to what I offered, but Les thinks it is too complex for
his needs.

"MartinW" wrote in message
...
Hi Les,

I use Chip Pearson's Time entry for timesheets.
The users love it and don't seem to get confused
too often.

With Chips code
8:00 is entered as 800 on the numpad
8:35 is entered as 835 on the numpad
etc

Here is the link.
http://www.cpearson.com/excel/DateTimeEntry.htm

Don't be intimidated by the VBA code, I'm VBA
illiterate but I still managed to work it out from
Chips explanations in his more information link.

HTH
Martin


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1 column
per field. One of the columns will be 'start time' (column S) and the
next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when I
tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet range
in a data validation formula.

Hope someone can help

Many thanks
Les

Hi Bob,

My main point was that Les shouldn't be intimidated.
I'll explain the process to him one step at a time, beginner
to beginner, how I got it to work. I'll use Chip's code
as that is the one I know. Feel free to correct me on any
point and also offer comment as to which is the better
way to go.

Les,

Copy the second set of code on Chip's site from End Sub
back up to Private Sub Worksheet....... (there is an extraneous
.. just above the code, don't include that, typo I presume)

Open a fresh worksheet
Right click on the Sheet tab and select View Code
This opens the VBA editor.

A smaller window inside the editor will have two pulldown
boxes at the top of it showing (General) and (Declarations)
Change the first to Worksheet and the second to Change.

When you change these some starter code will be added to the
box below.
Delete this code and then right click and paste Chips code.
Close the VBA editor by clicking on the X in the top right corner.

Select cells A1:A10 and format them as custom h:mm

Now enter 100 in A1, 245 in A2 etc. you will see it working.

To change the range have a look at the code where it says
If Application.Intersect(Target, Range("A1:A10"))
change the range to something different and see how it
affects your worksheet.

Bob's code maybe more efficient, I'm sure he will let us know
on that score, but hopefully that will show you that there
is nothing to be intimidated by when *applying* VBA.
Of course *writing* the code is a different story and shouldn't
be attempted unless you intend to go the whole way and get a
full understanding of why and how everything works (sounds like
a lifetime study to me. g)

HTH
Martin


"Bob Phillips" wrote in message
...
That is very similar to what I offered, but Les thinks it is too complex
for his needs.

"MartinW" wrote in message
...
Hi Les,

I use Chip Pearson's Time entry for timesheets.
The users love it and don't seem to get confused
too often.

With Chips code
8:00 is entered as 800 on the numpad
8:35 is entered as 835 on the numpad
etc

Here is the link.
http://www.cpearson.com/excel/DateTimeEntry.htm

Don't be intimidated by the VBA code, I'm VBA
illiterate but I still managed to work it out from
Chips explanations in his more information link.

HTH
Martin


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1 column
per field. One of the columns will be 'start time' (column S) and the
next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when
I tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet range
in a data validation formula.

Hope someone can help

Many thanks
Les

Hello Les,

You might have already solved this, just thought I'd let you know I
did something similar to this, using custom cell formatting. Just
select the cell, go to Format|Cells Menu, select Custom, and type
this:

[8]hh:mm "am";[1]hh:mm "pm"

Now if you type "8" in the cell it will format as "8:00 am" and so on.
Doesn't work on times like 8:30 however.

Sorry this is off the top of my head, I may be typing it slightly
wrong.

Hope this helps,
--JP


On Oct 4, 6:35 pm, "Les Isaacs" wrote:
Bob

Many thanks for this. The code you suggested is quite a bit beyond my
understanding, I'm afraid, so although I'm sure it would work I am reluctant
to use it: I would be out of my depth! Instead I think I'll just instruct
the users to enter the times correctly - with colons. I do appreciate your
help though.

Thanks again
Les (not Jack!)

"Bob Phillips" wrote in message

...



Les,

You could use code to shift it for you on entry

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '=== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value = 0 And .Value = 240000 Then
Select Case Len(.Value)
Case 1:
.Value = TimeSerial(.Value, 0, 0)
Case 3:
.Value = TimeSerial(.Value \ 100, _
(.Value \ 100) * 100, 0)
Case 5, 6:
.Value = TimeSerial(.Value \ 10000, _
(.Value - (.Value \ 10000)
* 10000) \ 100, _
(.Value - (.Value \ 100) *
100))
End Select
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

With this code you enter the input as

h
hh
hmm
hhmm
hmmdd
hhmmdd

that is you myust always use two digit minutes and secs, but you can hyave
1 or digit hours.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Isaacs" wrote in message
...
Hello Peo

Thanks for your reply.
I was just hoping that entering 8 would be accepted as 8:00 - lazy I know
by the users would like this! If that's not possible I (they) can live
with it.
The harder problem is setting the constraint for 'end time' to be
'start time' - for the whole column. Can this be done?

Thanks again
Les

"Peo Sjoblom" wrote in message
...
Why would you enter 8? 8 is 8*24 hours so it is 8 days. You need to
enter it as 8:00,
formatting has nothing to do with that. Do datavalidationallow and
select time and set your constraints there

--

Regards,

Peo Sjoblom

"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1
column per field. One of the columns will be 'start time' (column S)
and the next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when
I tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet
range in a data validation formula.

Hope someone can help

Many thanks
Les- Hide quoted text -

- Show quoted text -

Martin

Many thanks for your step by step instructions - which I have followed and
it all works perfectly ... except that for some reason my validadtion is not
working!
With the formatted ('start') times entered in column S, I then need to check
that the (similarly formatted) ('end') times entered in column T are after
the corresponding column S value. So, as per RagDyer's instructions earlier
in this thread, I highlighted the entire column T, did DataValidation,
selected Custom in the Allow box and typed =T1S1 in the formula box. This
validadtion is now showing for every column T row - which is what I wanted,
but it isn't actually working: I can enter 835 in S2 (which formats
beautifully as 8:35AM!) and 715 (which formats beautifully as 7:15AM) in T2.
This must not be allowed, as 7:15AM is after 8:35AM.

I really am very grateful for the time you, Bob, RD and the others have
spent helping me with this. I'm sure I'm alomost there - if I can just get
the validation to work.

Thanks again
Les



"MartinW" wrote in message
...
Hi Bob,

My main point was that Les shouldn't be intimidated.
I'll explain the process to him one step at a time, beginner
to beginner, how I got it to work. I'll use Chip's code
as that is the one I know. Feel free to correct me on any
point and also offer comment as to which is the better
way to go.

Les,

Copy the second set of code on Chip's site from End Sub
back up to Private Sub Worksheet....... (there is an extraneous
. just above the code, don't include that, typo I presume)

Open a fresh worksheet
Right click on the Sheet tab and select View Code
This opens the VBA editor.

A smaller window inside the editor will have two pulldown
boxes at the top of it showing (General) and (Declarations)
Change the first to Worksheet and the second to Change.

When you change these some starter code will be added to the
box below.
Delete this code and then right click and paste Chips code.
Close the VBA editor by clicking on the X in the top right corner.

Select cells A1:A10 and format them as custom h:mm

Now enter 100 in A1, 245 in A2 etc. you will see it working.

To change the range have a look at the code where it says
If Application.Intersect(Target, Range("A1:A10"))
change the range to something different and see how it
affects your worksheet.

Bob's code maybe more efficient, I'm sure he will let us know
on that score, but hopefully that will show you that there
is nothing to be intimidated by when *applying* VBA.
Of course *writing* the code is a different story and shouldn't
be attempted unless you intend to go the whole way and get a
full understanding of why and how everything works (sounds like
a lifetime study to me. g)

HTH
Martin


"Bob Phillips" wrote in message
...
That is very similar to what I offered, but Les thinks it is too complex
for his needs.

"MartinW" wrote in message
...
Hi Les,

I use Chip Pearson's Time entry for timesheets.
The users love it and don't seem to get confused
too often.

With Chips code
8:00 is entered as 800 on the numpad
8:35 is entered as 835 on the numpad
etc

Here is the link.
http://www.cpearson.com/excel/DateTimeEntry.htm

Don't be intimidated by the VBA code, I'm VBA
illiterate but I still managed to work it out from
Chips explanations in his more information link.

HTH
Martin


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1
column per field. One of the columns will be 'start time' (column S)
and the next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and when
I tried setting the validation of the cells in the 'end time' column to
allow Time with a formula of "=S:S" (without the apostrophies) I got a
message saying that I cannot use a direct reference to a worksheet
range in a data validation formula.

Hope someone can help

Many thanks
Les

Hi Les,

Like I said, I'm a beginner at VBA myself. Hopefully Bob
or RD will drop by and sort it out.
My only thought is have you tried using Bob's code in place
of Chips?

I'm sure it must be something fairly straightforward but I think the
validation should be built into the code rather than applied
later. For example while you have Chip's code working
try to enter text into one of the cells, it will let you enter
it, but first it pops up a message box saying your entry
is not a valid format, or something like that.

HTH
Martin


"Les Isaacs" wrote in message
...
Martin

Many thanks for your step by step instructions - which I have followed and
it all works perfectly ... except that for some reason my validadtion is
not working!
With the formatted ('start') times entered in column S, I then need to
check that the (similarly formatted) ('end') times entered in column T are
after the corresponding column S value. So, as per RagDyer's instructions
earlier in this thread, I highlighted the entire column T, did
DataValidation, selected Custom in the Allow box and typed =T1S1 in the
formula box. This validadtion is now showing for every column T row -
which is what I wanted, but it isn't actually working: I can enter 835 in
S2 (which formats beautifully as 8:35AM!) and 715 (which formats
beautifully as 7:15AM) in T2. This must not be allowed, as 7:15AM is after
8:35AM.

I really am very grateful for the time you, Bob, RD and the others have
spent helping me with this. I'm sure I'm alomost there - if I can just get
the validation to work.

Thanks again
Les



"MartinW" wrote in message
...
Hi Bob,

My main point was that Les shouldn't be intimidated.
I'll explain the process to him one step at a time, beginner
to beginner, how I got it to work. I'll use Chip's code
as that is the one I know. Feel free to correct me on any
point and also offer comment as to which is the better
way to go.

Les,

Copy the second set of code on Chip's site from End Sub
back up to Private Sub Worksheet....... (there is an extraneous
. just above the code, don't include that, typo I presume)

Open a fresh worksheet
Right click on the Sheet tab and select View Code
This opens the VBA editor.

A smaller window inside the editor will have two pulldown
boxes at the top of it showing (General) and (Declarations)
Change the first to Worksheet and the second to Change.

When you change these some starter code will be added to the
box below.
Delete this code and then right click and paste Chips code.
Close the VBA editor by clicking on the X in the top right corner.

Select cells A1:A10 and format them as custom h:mm

Now enter 100 in A1, 245 in A2 etc. you will see it working.

To change the range have a look at the code where it says
If Application.Intersect(Target, Range("A1:A10"))
change the range to something different and see how it
affects your worksheet.

Bob's code maybe more efficient, I'm sure he will let us know
on that score, but hopefully that will show you that there
is nothing to be intimidated by when *applying* VBA.
Of course *writing* the code is a different story and shouldn't
be attempted unless you intend to go the whole way and get a
full understanding of why and how everything works (sounds like
a lifetime study to me. g)

HTH
Martin


"Bob Phillips" wrote in message
...
That is very similar to what I offered, but Les thinks it is too complex
for his needs.

"MartinW" wrote in message
...
Hi Les,

I use Chip Pearson's Time entry for timesheets.
The users love it and don't seem to get confused
too often.

With Chips code
8:00 is entered as 800 on the numpad
8:35 is entered as 835 on the numpad
etc

Here is the link.
http://www.cpearson.com/excel/DateTimeEntry.htm

Don't be intimidated by the VBA code, I'm VBA
illiterate but I still managed to work it out from
Chips explanations in his more information link.

HTH
Martin


"Jack Isaacs" wrote in message
...
Hello All

This really follows on from my previous post. I have a worksheet that
will be used for simple data entry of name, address, etc etc. - 1
column per field. One of the columns will be 'start time' (column S)
and the next column will be 'end time' (column T). I have 2 problems:

1. Even though I have set the format of the cells in the 'start time'
column as Time, with a Type of 13:30, if I enter just "8" (without the
apostrophies) the displayed value is 0:00.

2. I need to ensure that 'end time' is 'start time'. When I set the
validation of the cells in the 'end time' column to be Custom with a
formula of "S:S" (without the apostrophies) this didn't work, and
when I tried setting the validation of the cells in the 'end time'
column to allow Time with a formula of "=S:S" (without the
apostrophies) I got a message saying that I cannot use a direct
reference to a worksheet range in a data validation formula.

Hope someone can help

Many thanks
Les