IF Function: Time schedules
 

Hello, I need help on a IF function or tell me if I can do this using a LookUp.

We have a spreadsheet to record the times patients arrive and the time they
are seen to by the doctor depending on whether they booked an appointment or
not. The Customer Service rules state that patients who have booked an
appointment have a 00:05:00 min waiting time. If this is True we type in "On
Time". IF False, we type in "Over Time".

However, IF they have NOT booked an appointment patients have a 00:15:00 min
waiting time. IF the "Seen At" time is less than 00:15:00 then it's "On
Time". IF False, then the value is "Over Time"

Here is an example of the spreadsheet we use:

Time arrived Appoinment Y/N Seen at Length of wait Ontime/OverT
15:00:00 Y 15:20:00 00:20:00 Overtime
15:15:00 N 15:35:00 00:20:00 Overtime
16:10:00 N 16:15:00 00:05:00 On Time


I would appreciate any clues and answers

Merry Christmas to all !!!
--
Regards

Irish Mike

IF Function: Time schedules
 

=IF(COUNTA(A2:C2)<3,"",IF(OR(AND(B2="Y",(C2-A2)*1440<=5),AND(B2="N",(C2-A2)*1440<=15)),"Ontime","OverTime"))


"Mike" wrote:

Hello, I need help on a IF function or tell me if I can do this using a LookUp.

We have a spreadsheet to record the times patients arrive and the time they
are seen to by the doctor depending on whether they booked an appointment or
not. The Customer Service rules state that patients who have booked an
appointment have a 00:05:00 min waiting time. If this is True we type in "On
Time". IF False, we type in "Over Time".

However, IF they have NOT booked an appointment patients have a 00:15:00 min
waiting time. IF the "Seen At" time is less than 00:15:00 then it's "On
Time". IF False, then the value is "Over Time"

Here is an example of the spreadsheet we use:

Time arrived Appoinment Y/N Seen at Length of wait Ontime/OverT
15:00:00 Y 15:20:00 00:20:00 Overtime
15:15:00 N 15:35:00 00:20:00 Overtime
16:10:00 N 16:15:00 00:05:00 On Time


I would appreciate any clues and answers

Merry Christmas to all !!!
--
Regards

Irish Mike

IF Function: Time schedules
 

Try this:

Time Arrived is in column B
Appt Y/N is in column C
Seen at is in column D
Length of wait = D3-D3 and is in column E

In the Overtime / On time column use the following formula

=IF(OR(AND(C3="Y",E35/1440),AND(C3="N",E315/1440)),"OVERTIME","ONTIME")




"Mike" wrote:

Hello, I need help on a IF function or tell me if I can do this using a LookUp.

We have a spreadsheet to record the times patients arrive and the time they
are seen to by the doctor depending on whether they booked an appointment or
not. The Customer Service rules state that patients who have booked an
appointment have a 00:05:00 min waiting time. If this is True we type in "On
Time". IF False, we type in "Over Time".

However, IF they have NOT booked an appointment patients have a 00:15:00 min
waiting time. IF the "Seen At" time is less than 00:15:00 then it's "On
Time". IF False, then the value is "Over Time"

Here is an example of the spreadsheet we use:

Time arrived Appoinment Y/N Seen at Length of wait Ontime/OverT
15:00:00 Y 15:20:00 00:20:00 Overtime
15:15:00 N 15:35:00 00:20:00 Overtime
16:10:00 N 16:15:00 00:05:00 On Time


I would appreciate any clues and answers

Merry Christmas to all !!!
--
Regards

Irish Mike

IF Function: Time schedules
 

One way:

=IF(COUNTA(A2:C2)=3,IF((C2-A2)*((B2="Y")*3+(B2="N"))<=TIME(0,15,0),"On
Time","Over time"),"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike" wrote in message
...
Hello, I need help on a IF function or tell me if I can do this using a
LookUp.

We have a spreadsheet to record the times patients arrive and the time
they
are seen to by the doctor depending on whether they booked an appointment
or
not. The Customer Service rules state that patients who have booked an
appointment have a 00:05:00 min waiting time. If this is True we type in
"On
Time". IF False, we type in "Over Time".

However, IF they have NOT booked an appointment patients have a 00:15:00
min
waiting time. IF the "Seen At" time is less than 00:15:00 then it's "On
Time". IF False, then the value is "Over Time"

Here is an example of the spreadsheet we use:

Time arrived Appoinment Y/N Seen at Length of wait Ontime/OverT
15:00:00 Y 15:20:00 00:20:00 Overtime
15:15:00 N 15:35:00 00:20:00 Overtime
16:10:00 N 16:15:00 00:05:00 On Time


I would appreciate any clues and answers

Merry Christmas to all !!!
--
Regards

Irish Mike