Help combining three "IF" nested statements

[#1]

Hello,

I'm having trouble combining 3 nested if statements.

Each one works fine on its own and each has the same output (1, 0).

Could anyone offer advice?

Many thanks in advance.

=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1, 0),

// FC on time

If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1, 0),

// b/w 7-830

If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1, 0))

//PIR: Sched + 5

[fkeuroglian]

Try this, good luck

=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1, If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1,If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1,0))))

[#3]

Try this for nested IFs:

=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 1,

     If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 1,

          If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 1, 0)))

[MarcoWedel]

=If(PIR_TIME=Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON)), 'FC on time',

    If((Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00'), 'b/w 7-830',

       If(Time(PIR_TIME) >= Time(SCHED_START_TIME)+5/1440, 'Sched + 5'))

      )

    )

[#5]

Thank you all for your suggestions.

I made a mistake in one of the formulas that I modified: If(Time(PIR_TIME) <= Time(SCHED_START_TIME)+5/1440, 1, 0)

I added the formula in the "Combine Ifs" column.

So for Dr. S, I only want to flag him if he had first case of day (FC of Day), it started the earliest between 7 and 8:30 and if the Patient in Room (PIR) is less than the scheduled start time (adding a 5 minute grace period).

So I only want to show Dr. S' first case beginning at 07:02 because it met all criteria as earliest case in the day.

All other cases should not be flagged.

Do you have a suggestion (I can see if it's a counting issue that the 07:22 case would show too)?

SURGEON	Sched Start	PIR	FC of Day	B/w 7-830	PIR <= Sched St + 5	Combine Ifs
Dr. S	07:00	07:02	1	1	1	1
Dr. S	08:30	09:10	0	0	0	0
Dr. S	07:30	07:22	1	1	1	1
Dr. S	08:15	08:05	0	1	1	1
Dr. S	08:45	08:47	0	0	1	1
Dr. S	09:30	09:27	0	0	1	1
Dr. S	10:00	10:14	0	0	0	0
Dr. S	10:45	10:57	0	0	0	0

[Peter_Cammaert · Accepted solution]

Probably spelling or something. Also, the clauses are being badly combined, you should use ANDs to combine the three.

Here you go.

caused by:

=If( (PIR_TIME = Timestamp(Aggr(NODISTINCT Min(PIR_TIME), SURGEON))) AND

     (Time(PIR_TIME) >= '6:54:00') AND (Time(PIR_TIME) <= '8:36:00') AND

     (Time(PIR_TIME) <= Time(SCHED_START_TIME)+5/1440), 1, 0)

Peter

[#7]

Thank you so much! have a great night!