Returning a value based on Day & Date

[Phil_L]

Hi all.

I am trying to work out how to pull data from one sheet into another and return it based on the day and date that it happened.

Context: I work in a hospital and we are trying to work out how many patients came into ICU week by week from the data we input.

If you look at the pictures added, In the January sheet, there are 2 patients added and 1 patient in the February sheet. On the Data sheet, it shows 2 patients added for January and 1 Patient added for February however, the patient added at the bottom of the January sheet, although the week begins in January, the patient should be in February's tally.

Is there a formula I can add so that any patients added into the end of a week will show on the month that it should in the data sheet.

I hope this make sense. (Apologies, I cannot install XL2BB de to my computer being part of an organisation)

Regards

 

[Sam_D_Ben]

Hi, Please explain a little more of this statement "Is there a formula I can add so that any patients added into the end of a week will show on the month that it should in the data sheet"

You want "Patient B" of January month to be added against January or February month in "Data Sheet". Also are you counting "Date form Submitted" or "Date of Operation"

Thanks

 

[Phil_L]

Hi, Please explain a little more of this statement "Is there a formula I can add so that any patients added into the end of a week will show on the month that it should in the data sheet"

You want "Patient B" of January month to be added against January or February month in "Data Sheet". Also are you counting "Date form Submitted" or "Date of Operation"

Thanks

Apologies, I thought I had explained it a bit better than I did.

'Patient B' needs to be added into the February month on the Data sheet as the "Date of Operation" is actually in February but due to the week beginning in January that is why it is on the January Sheet. The "Date form Submitted" is not recorded so is not needed for the Data sheet. We only need to reference from the "Date of Operation" to show in the relevant month on the Data sheet.

Apologies if I have over complicated it again.

 

[Sam_D_Ben]

Since yours is an image file, i tried on a sample data, my bad. I hope someone can help you.

Returning a value based on Day & Date.xlsx
	A	B	C
1	Week Commencing
2	1-Jan-23	Patient 1	Patient 2
3	Patient Name	Patient A
4	Data	-
5	Data	-
6	Data	-
7	DFS	29-Dec-23
8	DOO	02-Jan-24
9	DAY	Tuesday
10
11	Week Commencing
12	29-Jan-24	Patient 1	Patient 2
13	Patient Name	Patient B
14	Data	-
15	Data	-
16	Data	-
17	DFS	26-Dec-23
18	DOO	01-Feb-24
19	DAY	Thursday
Jan

Returning a value based on Day & Date.xlsx
	A	B	C
1	Week Commencing
2	5-Feb-23	Patient 1	Patient 2
3	Patient Name	Patient A
4	Data	-
5	Data	-
6	Data	-
7	DFS	29-Dec-23
8	DOO	08-Feb-24
9	DAY	Thursday
Feb

Expected Output:

Returning a value based on Day & Date.xlsx
	A	B	C	D	E	F	G	H	I
1	Daily Act
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total
3	January	0	1	0	0	0	0	0	1
4	February	0	0	0	2	0	0	0	2
Data

Thanks

 

[Phil_L]

Since yours is an image file, i tried on a sample data, my bad. I hope someone can help you.

Returning a value based on Day & Date.xlsx
	A	B	C
1	Week Commencing
2	1-Jan-23	Patient 1	Patient 2
3	Patient Name	Patient A
4	Data	-
5	Data	-
6	Data	-
7	DFS	29-Dec-23
8	DOO	02-Jan-24
9	DAY	Tuesday
10
11	Week Commencing
12	29-Jan-24	Patient 1	Patient 2
13	Patient Name	Patient B
14	Data	-
15	Data	-
16	Data	-
17	DFS	26-Dec-23
18	DOO	01-Feb-24
19	DAY	Thursday
Jan

Returning a value based on Day & Date.xlsx
	A	B	C
1	Week Commencing
2	5-Feb-23	Patient 1	Patient 2
3	Patient Name	Patient A
4	Data	-
5	Data	-
6	Data	-
7	DFS	29-Dec-23
8	DOO	08-Feb-24
9	DAY	Thursday
Feb

Expected Output:

Returning a value based on Day & Date.xlsx
	A	B	C	D	E	F	G	H	I
1	Daily Act
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total
3	January	0	1	0	0	0	0	0	1
4	February	0	0	0	2	0	0	0	2
Data

Thanks

Thank you for your help

 

[felixstraube]

How about?:
I added some example data to test for november and december.
The data is filtered by the column A with the exact text "Date of Operation (DD/MM/YYYY)". If this is not exactly that text it won't work. If it has a line break for example.
There are other solution but I would need to see a complete month sheet. If the date we are interested in is in always in rows 8,18,28,38 and 48 we could use that information to filter the data.
Also i don't know how many patients columns there could be. I am considering from column B to column Z.

Book1
	A	B	C	D	E	F	G	H	I
1	Daily Act
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Total
3	January	-	1	-	-	-	-	-	1
4	February	-	-	-	2	-	-	-	2
5	March	-	-	-	-	-	-	-	-
6	April	-	-	-	-	-	-	-	-
7	May	-	-	-	-	-	-	-	-
8	June	-	-	-	-	-	-	-	-
9	July	-	-	-	-	-	-	-	-
10	August	-	-	-	-	-	-	-	-
11	September	-	-	-	-	-	-	-	-
12	October	-	-	-	-	-	-	-	-
13	November	1	1	1	1	1	-	-	5
14	December	2	3	3	3	2	2	6	21
Data

Cell Formulas
Range	Formula
B3:H14	B3	=LET( t,VSTACK(INDIRECT("'"&$A3&"'!A1:A100"),IFERROR(INDIRECT("'"&$A2&"'!A1:A100"),"")), d,VSTACK(INDIRECT("'"&$A3&"'!B1:Z100"),IFERROR(INDIRECT("'"&$A2&"'!B1:Z100"),"")), fd,TOCOL(FILTER(d,(t="Date of Operation (DD/MM/YYYY)"))), res,FILTER(fd,(fd>0)*(TEXT(fd,"mmmm")=$A3)*(TEXT(fd,"dddd")=B$2)), IFERROR(COUNT(res),0) )
I3:I14	I3	=SUM(B3:H3)