Validating times

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

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

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

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

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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

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

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

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

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

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

"Bernard Liengme" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"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