Entering Times In Excel

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bill Kuunders]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Guest]

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