Need Formula help

[nicolemterrien]

I want to pull ONLY negative time delays PLUS zero times by physicians for each month: All Dr. MENDOZA's times that are 0 and below / negative for the month of October. I have tried the multiple formulas however, I either cannot include everything I want or the solution / answer is invalid or errors out.

DATE 10/3/23 10/4/23 10/3/23 10/7/23 10/3/23 10/20/23 10/4/23 11/1/23 11/3/23 10/5/23 11/7/23 11/3/23 10/30/23 10/6/23 10/7/23 10/10/23 10/11/23 10/11/23 10/11/31 10/11/35 10/12/23 10/12/23 10/12/23 10/13/23 10/13/23 10/13/23 10/13/23 10/14/23

ROOM CVIR 1 CL 1 CVIR 2 CL 2 CVIR 4 CVIR 3 CVIR 2 CVIR 2 CVIR 1 CVIR 4 CVIR 3 CVIR 2 CVIR 1 CVIR 4 CVIR 1 CL 2 CL 2 CL 1 CVIR 2 CVIR 1 CVIR 3 CL 1 CVIR 4 CVIR 2 CVIR 1 CL 2 CVIR 4 CVIR 1

Patient Name smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john smith, john

DOCTOR MENDOZA EDWARDS ALAEDDINI HOLSTE RAMAKRISHNA MENDOZA ALAEDDINI ALAEDDINI ZICHAL RAMAKRISHN CHU ABOU ZIKI MENDOZA RAMAKRISHNA REPIC MENDOZA MENDOZA MICALE ALAEDDINI HOLSTE HARRIS ANANTHRAM RAMAKRISHNA ABOU ZIKI REPIC ZICHAL RAMAKRISHNA REPIC

Sche Time 8:00 8:00 8:00 8:30 9:00 8:00 8:00 8:00 8:00 9:00 8:00 8:00 8:00 9:00 8:00 8:00 8:00 8:00 8:15 8:30 8:00 8:00 9:00 8:00 8:00 8:00 9:00 8:00

Start Time 7:54 7:54 7:48 8:20 9:17 7:58 8:24 7:43 8:00 8:46 9:41 8:57 7:58 8:46 8:17 8:00 8:01 8:33 8:31 8:42 7:36 7:49 9:17 8:48 8:15 7:59 8:46 8:05

Time Delay -0:06 0:06 0:12 0:10 0:17 0:02 0:24 0:17 0:00 0:14 1:41 0:57 0:02 0:14 0:17 0:00 0:01 0:33 0:16 0:12 0:24 0:11 0:17 0:48 0:15 -0:01 0:14 0:05

 

[RobP]

Can I ask, how are you generating your time delay (what formula are you using in all the cells of that column ?)

The issue as I see it lies with your negative values - but we can't see how your formatting is.

Rob

 

[nicolemterrien]

Can I ask, how are you generating your time delay (what formula are you using in all the cells of that column ?)

The issue as I see it lies with your negative values - but we can't see how your formatting is.

Rob

Sched Time = E
Start Time = F
=E2-F2

 

[nicolemterrien]

Sched Time = E
Start Time = F
=E2-F2

Sorry, I meant Start time F - Sched Time E
=F2-E2

 

[RobP]

Sorry, I'm not able to replicate your data, or what you say is the formula in column G in my test.

If you can use the "XL2BB" tool on this forum to actually post a copy of the spreadsheet it might make things a little easier for us to try and help solve.

thanks
Rob

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATE	ROOM	Patient Name	DOCTOR	Sche Time	Start Time	Time Delay		Month =	10	Doctor=	MENDOZA
2	10/3/2023	CVIR 1	smith, john	MENDOZA	8:00 AM	7:54 AM	-0.06		DATE	ROOM	Patient Name	DOCTOR	Sche Time	Start Time	Time Delay
3	10/4/2023	CL 1	smith, john	EDWARDS	8:00 AM	7:54 AM	0:06		10/3/2023	CVIR 1	smith, john	MENDOZA	8:00 AM	7:54 AM	-0.06
4	10/3/2023	CVIR 2	smith, john	ALAEDDINI	8:00 AM	7:48 AM	0:12		10/10/2023	CL 2	smith, john	MENDOZA	8:00 AM	8:00 AM	0
5	10/7/2023	CL 2	smith, john	HOLSTE	8:30 AM	8:20 AM	0:10
6	10/3/2023	CVIR 4	smith, john	RAMAKRISHNA	9:00 AM	9:17 AM	0:17
7	10/20/2023	CVIR 3	smith, john	MENDOZA	8:00 AM	7:58 AM	0:02
8	10/4/2023	CVIR 2	smith, john	ALAEDDINI	8:00 AM	8:24 AM	0:24
9	11/1/2023	CVIR 2	smith, john	ALAEDDINI	8:00 AM	7:43 AM	0:17
10	11/3/2023	CVIR 1	smith, john	ZICHAL	8:00 AM	8:00 AM	0:00
11	10/5/2023	CVIR 4	smith, john	RAMAKRISHN	9:00 AM	8:46 AM	0:14
12	11/7/2023	CVIR 3	smith, john	CHU	8:00 AM	9:41 AM	1:41
13	11/3/2023	CVIR 2	smith, john	ABOU ZIKI	8:00 AM	8:57 AM	0:57
14	10/30/2023	CVIR 1	smith, john	MENDOZA	8:00 AM	7:58 AM	0:02
15	10/6/2023	CVIR 4	smith, john	RAMAKRISHNA	9:00 AM	8:46 AM	0:14
16	10/7/2023	CVIR 1	smith, john	REPIC	8:00 AM	8:17 AM	0:17
17	10/10/2023	CL 2	smith, john	MENDOZA	8:00 AM	8:00 AM	0:00
18	10/11/2023	CL 2	smith, john	MENDOZA	8:00 AM	8:01 AM	0:01
19	10/11/2023	CL 1	smith, john	MICALE	8:00 AM	8:33 AM	0:33
20	10/11/1931	CVIR 2	smith, john	ALAEDDINI	8:15 AM	8:31 AM	0:16
21	10/11/1935	CVIR 1	smith, john	HOLSTE	8:30 AM	8:42 AM	0:12
22	10/12/2023	CVIR 3	smith, john	HARRIS	8:00 AM	7:36 AM	0:24
23	10/12/2023	CL 1	smith, john	ANANTHRAM	8:00 AM	7:49 AM	0:11
24	10/12/2023	CVIR 4	smith, john	RAMAKRISHNA	9:00 AM	9:17 AM	0:17
25	10/13/2023	CVIR 2	smith, john	ABOU ZIKI	8:00 AM	8:48 AM	0:48
26	10/13/2023	CVIR 1	smith, john	REPIC	8:00 AM	8:15 AM	0:15
27	10/13/2023	CL 2	smith, john	ZICHAL	8:00 AM	7:59 AM	-0.01
28	10/13/2023	CVIR 4	smith, john	RAMAKRISHNA	9:00 AM	8:46 AM	0:14
29	10/14/2023	CVIR 1	smith, john	REPIC	8:00 AM	8:05 AM	0:05
Sheet1

Cell Formulas
Range	Formula
I3:O4	I3	=FILTER($A$2:$G$29,(MONTH($A$2:$A$29)=$J$1)*($G$2:$G$29<=0)*($D$2:$D$29=$L$1))
Dynamic array formulas.