Validating times

[Jack Isaacs]

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

 

[Peo Sjoblom]

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 data>validation>allow and select
time and set your constraints there


--


Regards,


Peo Sjoblom

 

[Jack Isaacs]

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

 

[RagDyeR]

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:

=T1>S1

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!
=====================================================








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

 

[Bernard Liengme]

Why not let users enter numbers like 8
The in another cell use =A1/24 and format this as Time to display 8:00 AM
best wishes

 

[Bob Phillips]

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)

 

[Les Isaacs]

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

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:

=T1>S1

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!
=====================================================








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

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 data>validation>allow and
select time and set your constraints there


--


Regards,


Peo Sjoblom

 

[Les Isaacs]

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!)

 

[Peo Sjoblom]

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

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

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:

=T1>S1

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!
=====================================================








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

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 data>validation>allow and
select time and set your constraints there


--


Regards,


Peo Sjoblom


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

 

[Les Isaacs]

Bernard

Thanks for your suggestion.
This works OK for 8, but not for 8.45 - where the user intended to enter
'quarter to nine': the formula and formatting you suggested obviously
converts 8.45, as a decimal, to 8:27AM. Not to worry - I'll just instruct
the users to input the times correctly - using colons!

Thanks again
Les

 

[RagDyer]

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:

=T1>S1

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 !
---------------------------------------------------------------------------

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

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:

=T1>S1

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!
=====================================================








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

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 data>validation>allow and
select time and set your constraints there


--


Regards,


Peo Sjoblom


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

 

[RagDyer]

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 !
---------------------------------------------------------------------------

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

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

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:

=T1>S1

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!
=====================================================








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


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 data>validation>allow and
select time and set your constraints there


--


Regards,


Peo Sjoblom


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

 

[MartinW]

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

 

[Bob Phillips]

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

 

[MartinW]

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

 

[JP]

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

 

[Les Isaacs]

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 Data>Validation,
selected Custom in the Allow box and typed =T1>S1 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]

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