Use data if corresponding times are between certain hours

[rjsnl]

Hi all,

I am looking for a solution to the following issue.

I have a column with TIME, ROOM and CO2.
I would like to use the CO2 data only between 8:00 hours and 11:00 hours and put them in another column to make a graph of only the CO2 between those hours.

I would like to include the room name into the formula, because I will have to do this for different room name, who will all be shown in the same row.

Hopefully this question makes sense and someone has an idea.

Thanks.

[alansidman]

Able to accomplish with the aid of Power Query. Below is result, Mcode employed and a file for your review.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Excel 2016 (Windows) 32 bit

	A	B	C	D
1	Date	Time	Room	CO2
2	11/2/2020	8:04:00 AM	ELOY	498
3	11/2/2020	8:14:00 AM	ELOY	473
4	11/2/2020	8:24:00 AM	ELOY	449
5	11/2/2020	8:35:00 AM	ELOY	537
6	11/2/2020	8:45:00 AM	ELOY	670
7	11/2/2020	8:55:00 AM	ELOY	813
8	11/2/2020	9:05:00 AM	ELOY	906
9	11/2/2020	9:15:00 AM	ELOY	1005
10	11/2/2020	9:23:00 AM	ELOY	1067
11	11/2/2020	9:33:00 AM	ELOY	1094
12	11/2/2020	9:43:00 AM	ELOY	1153
13	11/2/2020	9:53:00 AM	ELOY	1188
14	11/2/2020	10:02:00 AM	ELOY	1153
15	11/2/2020	10:13:00 AM	ELOY	1150
16	11/2/2020	10:22:00 AM	ELOY	1151
17	11/2/2020	10:31:00 AM	ELOY	1165
18	11/2/2020	10:39:00 AM	ELOY	1047
19	11/2/2020	10:49:00 AM	ELOY	918
20	11/2/2020	10:59:00 AM	ELOY	833
21	11/2/2020	8:00:00 AM	Eloy	400

Sheet: Sheet2

[Estevaoba]

An option with array formula (Ctrl+Shift+Enter) in F2, for example, and copied across and down:

Please Login or Register  to view this content.

Good luck!

[Bo_Ry]

Please try

=FILTER(A2:D34,(B2:B34>=8/24)*(B2:B34<=11/24)*(C2:C34="Eloy"))