I have a spread sheet that 8 different people access. There are times that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from the
chart - whether it is a typo or the incorrect time is written on the patient
chart. I need to put something in the worksheet that will not let the person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

Well, that didn't work. I have a code in excel where they don't have to
enter the decimels when they are entering the time. When I take the code out
the validation works but then they have to enter the decimels when they enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

I use a fairly simple option to enter the colons when entering times.
Use the "autocorrect options" in "tools"
replace ++ with :

The validation works ok then.

Greetings from NZ
Bill K

"Denise" wrote in message
...
Well, that didn't work. I have a code in excel where they don't have to
enter the decimels when they are entering the time. When I take the code
out
the validation works but then they have to enter the decimels when they
enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in
B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from
the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

Bill,
I am confused with this. What format do I use for my cells that times are
entered? Number, time....

And the in the Auto correct I just put in the the replace ++ with :

Then when the time 300 is entered it will change to 3:00??

I must be doing something wrong.

"Bill Kuunders" wrote:

I use a fairly simple option to enter the colons when entering times.
Use the "autocorrect options" in "tools"
replace ++ with :

The validation works ok then.

Greetings from NZ
Bill K

"Denise" wrote in message
...
Well, that didn't work. I have a code in excel where they don't have to
enter the decimels when they are entering the time. When I take the code
out
the validation works but then they have to enter the decimels when they
enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in
B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from
the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

With the autocorrect in place
enter for example 3++00 and it will change to 3:00 as you enter it.

I chose the ++ because all of the entry can be done on the number pad.

regards
Bill K

"Denise" wrote in message
...
Bill,
I am confused with this. What format do I use for my cells that times are
entered? Number, time....

And the in the Auto correct I just put in the the replace ++ with :

Then when the time 300 is entered it will change to 3:00??

I must be doing something wrong.

"Bill Kuunders" wrote:

I use a fairly simple option to enter the colons when entering times.
Use the "autocorrect options" in "tools"
replace ++ with :

The validation works ok then.

Greetings from NZ
Bill K

"Denise" wrote in message
...
Well, that didn't work. I have a code in excel where they don't have
to
enter the decimels when they are entering the time. When I take the
code
out
the validation works but then they have to enter the decimels when they
enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1
in
B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are
times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from
the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let
the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at
830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

Hi Denise. Take a look at Chip Pearson's site:
http://www.cpearson.com/excel/DateTimeEntry.htm. It has a procedure for
entering time without a separator. HTH
--
Sincerely, Michael Colvin


"Denise" wrote:

Bill,
I am confused with this. What format do I use for my cells that times are
entered? Number, time....

And the in the Auto correct I just put in the the replace ++ with :

Then when the time 300 is entered it will change to 3:00??

I must be doing something wrong.

"Bill Kuunders" wrote:

I use a fairly simple option to enter the colons when entering times.
Use the "autocorrect options" in "tools"
replace ++ with :

The validation works ok then.

Greetings from NZ
Bill K

"Denise" wrote in message
...
Well, that didn't work. I have a code in excel where they don't have to
enter the decimels when they are entering the time. When I take the code
out
the validation works but then they have to enter the decimels when they
enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in
B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from
the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

How would they input 09:00 in your code technique?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
Well, that didn't work. I have a code in excel where they don't have to
enter the decimels when they are entering the time. When I take the code
out
the validation works but then they have to enter the decimels when they
enter
the time. Is there a solution to that??

"Bob Phillips" wrote:

Use data validation, with an allow type of custom.

Assume time 1 is in a1, and time 2 is in B1, use a formula of =B1=A1 in
B1.

And so on

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Denise" wrote in message
...
I have a spread sheet that 8 different people access. There are times
that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from
the
chart - whether it is a typo or the incorrect time is written on the
patient
chart. I need to put something in the worksheet that will not let the
person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at
830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.

Denise,
This is what I came up with:
Cell A4 is first time to be entered.
Column A "Arrival Time"
Column C "Triage Time"
Column E "To ED Time"
Column G "Saw MD Time"
Column I "Discharge Time"
Column K "Total Time"
Format all columns used in the format "hh:mm" This will make calculations
easy.
Cell C4
Conditional Formats:
Condition 1 Cell Value Is, greater than,"=A$4, Format Green
Condition 2 Cell Value Is, less than, "=A$4, Format Red
Copy C4 to E4,G4,I4.
In Cell K4:
"=IF(AND(C4<A4,E4<C4,G4<E4,I4<G4),"NOT POSSIBLE",I4-A4)"
without the "".
What this does is make sure all times are TRUE.

HTH
"Denise" wrote:

I have a spread sheet that 8 different people access. There are times that
are entered - Arrival Time, Triage Time, To ED Time, Saw MD Time and
Discharge Time. I have formulas to calculated the wait times.

There are lots of problems with people entering the times wrong from the
chart - whether it is a typo or the incorrect time is written on the patient
chart. I need to put something in the worksheet that will not let the person
enter the time if it is wrong.

Example - the person will arrive at 900 and be placed in a room at 830.
This is not possible since they are not even here.

Can anyone help me with this?

Thanks.