Need HELP!!!

[Kay_T1060]

Hi, 

So I am looking to create a formula to help me see if a person I am looking at is in violation of a rule.  Below is the rule. I also need to make sure that it's not the same agency (agency should overlap with a different agency name).

"Interpreter services provided by the same interpreter to multiple members at the same location on the same date of service will be reimbursed as follows: A. The first appointment of the day will be reimbursed at the one-hour minimum or actual time, whichever is greater. B. Appointment(s) following the first appointment must have at least 1 ½ hours between the end of the last appointment and the start time of the next appointment to be reimbursed at the one-hour minimum or actual time, whichever is greater. Time between appointment(s) that is less than 1 1/2 hours will be reimbursed for actual time."

Is there any way to either break this up or make it better where I can understand it. I am having some errors with my work. 

Thank you for all of your help!

Here is the formula: 

1st appt rule: =IF(AND(T3<6,(M3-L3)<4,Q3>=4),"YES","NO") or =IF(AND(L3-M3<>"",K3=K2,N3=N2,T3<6,Q3>=4),"YES","NO")

Time between appointments in units: =IF(AND(K3=K2,N3=N2),(L3-M2)*96,"N/A") 

***NOTE***

1 unit=15 minutes

Time formats are in 24HR

[#2]

@Kay_T1060 

Hi Kay,

I do not quite understand everything you are trying to do but as a first step I have inserted a couple of green columns; the first is to calculate to the difference between 2 times; the second will be to take some action once time difference is greater than 1:30.

Can you clarify a bit further what you want to do next?

thanks,

Peter

[#3]

@peteryac60 

Hi, 

Thank you for your help with that. The points I need to hit for the formula are to see if it has the same date of service, same place of service, and different agency name, then I need the formula to see if when the time between the previous appointment is below 90 minutes (6 units Column T) but is also greater then 4 units billed (Column Q), if all of this is true, it will be a violation (Yes). 

The appointment times are in 24hr format, which is recommended due to how it's billed. I am thinking of changing it back to 12hr and doing the formula that way as well calculating the hour and minute of time. 

Did this help? Thank you for your help. 

[#4]

@Kay_T1060 

Hi Kay,

See attached - green columns.

In the first green columns I have just subtracted the end time from the start time of next session. In two cases (highlighted in yellow) there is an error because the end time is BEFORE the start time.

In the rest of the cases there are only one case where the time is greater than 90 minutes. In the second green column I check if the time is > 90 and if the value in column Q is > 4 - I think that is the requirement you had?

it is possible to do this all in one formula but it makes it difficult to follow so I have split it over 2 columns.

If this is the solution you are looking forward I'd be grateful if you could mark this as complete.

thanks,

Peter