Filter date time data so I only get one data point per hour

jappi · Mar 15, 2024

Hi - data sample attached. The date time reporting intervals in column A are every 10 mins, all day, across multiple dates. My goal:

Create a line graph that only has an event per hour. I don't really mind what time within the hour it is, as long as its consistent. E.g. show every event at h:06m
I want to filter out anything that is before 8am and after 6pm and ideally, not include Saturday or Sunday but the latter is a nice to have which I could manage manually if need be
I can then select which of the columns from B onwards I want to show in the line graphs

Thanks in advance

Peter_SSs · Mar 15, 2024

Thanks for updating.

jappi · Mar 15, 2024

Peter_SSs said:
View attachment 108411

Thanks for updating.

Understood. Profile now fully updated. OK, I have the plugin working. I select data and select mini sheet. It says its saved to my clipboard. Sorry for the numpty questions....but what next? How do I get the mini file on here?

Cubist · Mar 15, 2024

jappi said:
Understood. Profile now fully updated. OK, I have the plugin working. I select data and select mini sheet. It says its saved to my clipboard. Sorry for the numpty questions....but what next? How do I get the mini file on here?

Ctrl + V to paste.

jappi · Mar 15, 2024

History_6707D32768530805.xlsx

A

B

C

D

E

F

G

1

Time

Temperature

Humidity

CO2

TVOC

PM2.5

PM10

2

3/2/24 0:06

24.5 ℃

67%

424 ppm

1

4 μg/m³

3

3/2/24 0:16

24.5 ℃

67%

424 ppm

1

4 μg/m³

4

3/2/24 0:26

24.5 ℃

67%

424 ppm

1

4 μg/m³

5

3/2/24 0:36

24.5 ℃

67%

424 ppm

1

4 μg/m³

6

3/2/24 0:46

24.6 ℃

67%

424 ppm

1

5 μg/m³

7

3/2/24 0:56

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

8

3/2/24 1:06

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

9

3/2/24 1:16

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

10

3/2/24 1:26

24.5 ℃

67.50%

425 ppm

1

5 μg/m³

11

3/2/24 1:36

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

12

3/2/24 1:46

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

8 μg/m³

13

3/2/24 1:56

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

8 μg/m³

14

3/2/24 2:06

24.5 ℃

67.50%

424 ppm

1

5 μg/m³

8 μg/m³

15

3/2/24 2:16

24.5 ℃

68%

424 ppm

1

5 μg/m³

8 μg/m³

16

3/2/24 2:26

24.5 ℃

68%

424 ppm

1

5 μg/m³

8 μg/m³

17

3/2/24 2:36

24.5 ℃

68%

424 ppm

1

5 μg/m³

8 μg/m³

18

3/2/24 2:46

24.6 ℃

68%

424 ppm

1

5 μg/m³

19

3/2/24 2:56

24.6 ℃

68%

423 ppm

1

5 μg/m³

20

3/2/24 3:06

24.6 ℃

68%

424 ppm

1

5 μg/m³

21

3/2/24 3:16

24.5 ℃

68%

424 ppm

1

5 μg/m³

22

3/2/24 3:26

24.5 ℃

68.50%

423 ppm

1

5 μg/m³

23

3/2/24 3:36

24.6 ℃

68.50%

424 ppm

1

5 μg/m³

24

3/2/24 3:46

24.6 ℃

68.50%

424 ppm

1

5 μg/m³

6 μg/m³

25

3/2/24 3:56

24.6 ℃

68.50%

424 ppm

1

5 μg/m³

6 μg/m³

26

3/2/24 4:06

24.6 ℃

68.50%

424 ppm

1

5 μg/m³

6 μg/m³

27

3/2/24 4:16

24.5 ℃

68.50%

425 ppm

1

5 μg/m³

6 μg/m³

28

3/2/24 4:26

24.6 ℃

68.50%

425 ppm

1

5 μg/m³

6 μg/m³

29

3/2/24 4:36

24.6 ℃

68.50%

425 ppm

1

5 μg/m³

6 μg/m³

30

3/2/24 4:46

24.6 ℃

68.50%

425 ppm

1

5 μg/m³

31

3/2/24 4:56

24.6 ℃

69%

425 ppm

1

5 μg/m³

32

3/2/24 5:06

24.6 ℃

69%

426 ppm

1

5 μg/m³

33

3/2/24 5:16

24.6 ℃

69%

426 ppm

1

5 μg/m³

34

3/2/24 5:26

24.6 ℃

69%

426 ppm

1

5 μg/m³

35

3/2/24 5:36

24.6 ℃

69%

427 ppm

1

5 μg/m³

36

3/2/24 5:46

24.6 ℃

69%

427 ppm

1

6 μg/m³

37

3/2/24 5:56

24.6 ℃

69%

428 ppm

1

6 μg/m³

38

3/2/24 6:06

24.6 ℃

69%

428 ppm

1

6 μg/m³

39

3/2/24 6:16

24.7 ℃

69%

429 ppm

1

6 μg/m³

40

3/2/24 6:26

24.8 ℃

69%

429 ppm

1

6 μg/m³

41

3/2/24 6:36

24.9 ℃

68.50%

430 ppm

1

6 μg/m³

42

3/2/24 6:46

25 ℃

68.50%

431 ppm

1

8 μg/m³

9 μg/m³

43

3/2/24 6:56

25.1 ℃

68.50%

431 ppm

1

8 μg/m³

9 μg/m³

44

3/2/24 7:06

25.3 ℃

68%

432 ppm

1

8 μg/m³

9 μg/m³

45

3/2/24 7:16

25.4 ℃

68%

432 ppm

1

8 μg/m³

9 μg/m³

46

3/2/24 7:26

25.5 ℃

68%

432 ppm

1

8 μg/m³

9 μg/m³

47

3/2/24 7:36

25.7 ℃

67.50%

433 ppm

1

8 μg/m³

9 μg/m³

48

3/2/24 7:46

25.8 ℃

67.50%

433 ppm

1.1

8 μg/m³

9 μg/m³

49

3/2/24 7:56

25.9 ℃

67%

435 ppm

1.1

8 μg/m³

9 μg/m³

50

3/2/24 8:06

26.1 ℃

67%

435 ppm

1.1

8 μg/m³

9 μg/m³

51

3/2/24 8:16

26.2 ℃

67%

435 ppm

1.1

8 μg/m³

9 μg/m³

52

3/2/24 8:26

26.3 ℃

66.50%

435 ppm

1.1

8 μg/m³

9 μg/m³

53

3/2/24 8:36

26.4 ℃

66.50%

436 ppm

1.1

8 μg/m³

9 μg/m³

54

3/2/24 8:46

26.5 ℃

66%

435 ppm

1.1

6 μg/m³

55

3/2/24 8:55

26.6 ℃

66%

435 ppm

1.1

6 μg/m³

56

3/2/24 9:05

26.7 ℃

66%

434 ppm

1.1

6 μg/m³

57

3/2/24 9:16

26.4 ℃

64%

433 ppm

1.1

6 μg/m³

58

3/2/24 9:25

25.8 ℃

62%

431 ppm

1.1

6 μg/m³

59

3/2/24 9:35

25.3 ℃

60%

430 ppm

2.2

6 μg/m³

60

3/2/24 9:45

25 ℃

59%

429 ppm

2.7

4 μg/m³

61

3/2/24 9:55

24.7 ℃

58%

428 ppm

2.8

4 μg/m³

62

3/2/24 10:05

24.4 ℃

57%

427 ppm

2.6

4 μg/m³

63

3/2/24 10:15

24.2 ℃

56.50%

424 ppm

2.5

4 μg/m³

64

3/2/24 10:25

23.9 ℃

56%

420 ppm

2.3

4 μg/m³

65

3/2/24 10:35

23.7 ℃

55.50%

418 ppm

2.1

4 μg/m³

66

3/2/24 10:45

23.5 ℃

55.50%

418 ppm

1.9

3 μg/m³

67

3/2/24 10:55

23.3 ℃

55.50%

418 ppm

1.8

3 μg/m³

68

3/2/24 11:05

23.2 ℃

56%

417 ppm

1.9

3 μg/m³

69

3/2/24 11:15

23.1 ℃

58%

417 ppm

1.9

3 μg/m³

70

3/2/24 11:25

23.1 ℃

58%

415 ppm

1.8

3 μg/m³

71

3/2/24 11:35

23.2 ℃

57%

414 ppm

1.7

3 μg/m³

72

3/2/24 11:45

23.3 ℃

57%

413 ppm

1.3

4 μg/m³

73

3/2/24 11:55

23.4 ℃

57%

412 ppm

1

4 μg/m³

74

3/2/24 12:05

23.3 ℃

56.50%

411 ppm

1

4 μg/m³

75

3/2/24 12:15

23.3 ℃

56.50%

409 ppm

1

4 μg/m³

76

3/2/24 12:25

23.3 ℃

56.50%

409 ppm

1

4 μg/m³

77

3/2/24 12:35

23.1 ℃

56.50%

409 ppm

1

4 μg/m³

78

3/2/24 12:45

23.2 ℃

56%

407 ppm

1

4 μg/m³

79

3/2/24 12:55

23.2 ℃

56%

407 ppm

1

4 μg/m³

80

3/2/24 13:05

23.1 ℃

56.50%

406 ppm

1

4 μg/m³

81

3/2/24 13:15

23.1 ℃

56.50%

406 ppm

1

4 μg/m³

82

3/2/24 13:25

23.1 ℃

56.50%

405 ppm

1

4 μg/m³

83

3/2/24 13:35

23 ℃

57%

406 ppm

1

4 μg/m³

84

3/2/24 13:45

23.1 ℃

56.50%

405 ppm

1

3 μg/m³

85

3/2/24 13:55

23.1 ℃

57%

404 ppm

1

3 μg/m³

86

3/2/24 14:05

23.1 ℃

57.50%

404 ppm

1

3 μg/m³

87

3/2/24 14:15

23.1 ℃

57.50%

404 ppm

1

3 μg/m³

88

3/2/24 14:25

23 ℃

57.50%

404 ppm

1

3 μg/m³

89

3/2/24 14:35

23 ℃

57.50%

404 ppm

1

3 μg/m³

90

3/2/24 14:45

23 ℃

57.50%

403 ppm

1

3 μg/m³

91

3/2/24 14:55

23 ℃

58%

404 ppm

1

3 μg/m³

92

3/2/24 15:05

23 ℃

58%

403 ppm

1

3 μg/m³

93

3/2/24 15:15

23 ℃

58%

402 ppm

1

3 μg/m³

94

3/2/24 15:25

23 ℃

58.50%

403 ppm

1

3 μg/m³

95

3/2/24 15:35

23.1 ℃

59.50%

403 ppm

1

3 μg/m³

96

3/2/24 15:45

23 ℃

59%

403 ppm

1

3 μg/m³

97

3/2/24 15:55

22.8 ℃

59.50%

403 ppm

1

3 μg/m³

98

3/2/24 16:05

22.8 ℃

60%

404 ppm

1

3 μg/m³

99

3/2/24 16:15

22.7 ℃

59.50%

404 ppm

1

3 μg/m³

100

3/2/24 16:25

22.7 ℃

60%

404 ppm

1

3 μg/m³

101

3/2/24 16:35

22.7 ℃

60.50%

404 ppm

1

3 μg/m³

102

3/2/24 16:45

22.9 ℃

59.50%

404 ppm

1

3 μg/m³

103

3/2/24 16:55

23.3 ℃

59.50%

404 ppm

1

3 μg/m³

104

3/2/24 17:05

23.7 ℃

59%

404 ppm

1

3 μg/m³

105

3/2/24 17:15

23.9 ℃

60%

404 ppm

1

3 μg/m³

106

3/2/24 17:25

24 ℃

59.50%

404 ppm

1

3 μg/m³

107

3/2/24 17:35

24.2 ℃

60%

404 ppm

1

3 μg/m³

108

3/2/24 17:45

24.4 ℃

61%

404 ppm

1

3 μg/m³

109

3/2/24 17:55

24.5 ℃

61.50%

404 ppm

1

3 μg/m³

110

3/2/24 18:05

24.6 ℃

61.50%

404 ppm

1

3 μg/m³

111

3/2/24 18:15

24.6 ℃

62%

404 ppm

1

3 μg/m³

112

3/2/24 18:25

24.7 ℃

62.50%

404 ppm

1

3 μg/m³

113

3/2/24 18:35

24.7 ℃

63%

405 ppm

1

3 μg/m³

114

3/2/24 18:45

24.8 ℃

63%

406 ppm

1

3 μg/m³

115

3/2/24 18:55

24.8 ℃

63.50%

405 ppm

1

3 μg/m³

116

3/2/24 19:05

24.9 ℃

64%

406 ppm

1

3 μg/m³

117

3/2/24 19:15

24.9 ℃

65%

406 ppm

1

3 μg/m³

118

3/2/24 19:25

24.9 ℃

65.50%

406 ppm

1

3 μg/m³

119

3/2/24 19:35

24.9 ℃

65.50%

405 ppm

1

3 μg/m³

120

3/2/24 19:45

25 ℃

66%

406 ppm

1

3 μg/m³

121

3/2/24 19:55

25 ℃

66.50%

400 ppm

1

3 μg/m³

122

3/2/24 20:05

25 ℃

67%

406 ppm

1

3 μg/m³

123

3/2/24 20:15

25 ℃

67%

409 ppm

1

3 μg/m³

124

3/2/24 20:25

25 ℃

67.50%

408 ppm

1

3 μg/m³

125

3/2/24 20:35

25 ℃

68%

409 ppm

1

3 μg/m³

126

3/2/24 20:45

25 ℃

68%

410 ppm

1

3 μg/m³

127

3/2/24 20:55

25 ℃

68.50%

410 ppm

1

3 μg/m³

128

3/2/24 21:05

25 ℃

69%

410 ppm

1

3 μg/m³

129

3/2/24 21:15

25 ℃

69.50%

410 ppm

1

3 μg/m³

130

3/2/24 21:25

25.1 ℃

69.50%

410 ppm

1

3 μg/m³

131

3/2/24 21:35

25 ℃

70%

410 ppm

1

3 μg/m³

132

3/2/24 21:45

25.1 ℃

70%

410 ppm

1

5 μg/m³

133

3/2/24 21:55

25.1 ℃

70%

410 ppm

1

5 μg/m³

134

3/2/24 22:05

25.1 ℃

70.50%

410 ppm

1.1

5 μg/m³

135

3/2/24 22:15

25.1 ℃

71%

410 ppm

1.1

5 μg/m³

136

3/2/24 22:25

25.1 ℃

71%

410 ppm

1.1

5 μg/m³

137

3/2/24 22:35

25.1 ℃

71%

410 ppm

1.1

5 μg/m³

138

3/2/24 22:45

25.1 ℃

71.50%

410 ppm

1.1

3 μg/m³

139

3/2/24 22:55

25.1 ℃

71.50%

410 ppm

1.1

3 μg/m³

140

3/2/24 23:05

25.1 ℃

71.50%

410 ppm

1.1

3 μg/m³

141

3/2/24 23:15

25.1 ℃

72%

410 ppm

1.1

3 μg/m³

142

3/2/24 23:25

25.2 ℃

72%

410 ppm

1.1

3 μg/m³

143

3/2/24 23:35

25.1 ℃

72%

410 ppm

1.1

3 μg/m³

144

3/2/24 23:45

25.2 ℃

72.50%

410 ppm

1.1

3 μg/m³

4 μg/m³

145

3/2/24 23:55

25.2 ℃

72.50%

410 ppm

1.1

3 μg/m³

4 μg/m³

146

3/3/24 0:05

25.2 ℃

72.50%

410 ppm

1.1

3 μg/m³

4 μg/m³

147

3/3/24 0:15

25.2 ℃

73%

410 ppm

1.1

3 μg/m³

4 μg/m³

148

3/3/24 0:25

25.2 ℃

73%

409 ppm

1.1

3 μg/m³

4 μg/m³

149

3/3/24 0:35

25.2 ℃

73.50%

409 ppm

1.1

3 μg/m³

4 μg/m³

150

3/3/24 0:45

25.3 ℃

73.50%

410 ppm

1.1

4 μg/m³

151

3/3/24 0:55

25.2 ℃

73.50%

410 ppm

1.1

4 μg/m³

152

3/3/24 1:05

25.2 ℃

73.50%

410 ppm

1.1

4 μg/m³

153

3/3/24 1:15

25.2 ℃

74%

410 ppm

1.1

4 μg/m³

154

3/3/24 1:25

25.2 ℃

74%

411 ppm

1.1

4 μg/m³

155

3/3/24 1:35

25.2 ℃

74%

412 ppm

1.1

4 μg/m³

156

3/3/24 1:45

25.2 ℃

74.50%

411 ppm

1.1

6 μg/m³

157

3/3/24 1:55

25.2 ℃

74.50%

412 ppm

1.1

6 μg/m³

158

3/3/24 2:05

25.2 ℃

75%

411 ppm

1.1

6 μg/m³

159

3/3/24 2:15

25.2 ℃

75%

412 ppm

1.1

6 μg/m³

160

3/3/24 2:25

25.2 ℃

75%

412 ppm

1.1

6 μg/m³

161

3/3/24 2:35

25.2 ℃

75.50%

412 ppm

1.1

6 μg/m³

162

3/3/24 2:45

25.2 ℃

75.50%

412 ppm

1.1

4 μg/m³

163

3/3/24 2:55

25.2 ℃

75.50%

412 ppm

1.1

4 μg/m³

164

3/3/24 3:05

25.2 ℃

76%

412 ppm

1.1

4 μg/m³

165

3/3/24 3:15

25.2 ℃

76%

412 ppm

1.1

4 μg/m³

166

3/3/24 3:25

25.2 ℃

76%

412 ppm

1.1

4 μg/m³

167

3/3/24 3:35

25.2 ℃

76.50%

412 ppm

1.1

4 μg/m³

168

3/3/24 3:45

25.2 ℃

76.50%

413 ppm

1.1

5 μg/m³

169

3/3/24 3:55

25.2 ℃

76.50%

412 ppm

1.1

5 μg/m³

170

3/3/24 4:05

25.2 ℃

77%

413 ppm

1.1

5 μg/m³

171

3/3/24 4:15

25.2 ℃

77%

412 ppm

1.2

5 μg/m³

172

3/3/24 4:25

25.2 ℃

77%

413 ppm

1.2

5 μg/m³

173

3/3/24 4:35

25.2 ℃

77.50%

412 ppm

1.2

5 μg/m³

174

3/3/24 4:45

25.3 ℃

77%

412 ppm

1.2

6 μg/m³

175

3/3/24 4:55

25.2 ℃

77.50%

413 ppm

1.1

6 μg/m³

176

3/3/24 5:05

25.2 ℃

77.50%

414 ppm

1.2

6 μg/m³

177

3/3/24 5:15

25.2 ℃

77.50%

413 ppm

1.1

6 μg/m³

178

3/3/24 5:25

25.2 ℃

77.50%

414 ppm

1.1

6 μg/m³

179

3/3/24 5:35

25.2 ℃

77.50%

414 ppm

1.1

6 μg/m³

180

3/3/24 5:45

25.3 ℃

77.50%

415 ppm

1.2

6 μg/m³

181

3/3/24 5:55

25.2 ℃

78%

415 ppm

1.2

6 μg/m³

182

3/3/24 6:05

25.3 ℃

78%

415 ppm

1.1

6 μg/m³

183

3/3/24 6:15

25.2 ℃

78%

416 ppm

1.2

6 μg/m³

184

3/3/24 6:25

25.3 ℃

78%

417 ppm

1.1

6 μg/m³

185

3/3/24 6:35

25.3 ℃

78%

420 ppm

1.1

6 μg/m³

186

3/3/24 6:45

25.3 ℃

78%

421 ppm

1.1

8 μg/m³

187

3/3/24 6:55

25.4 ℃

78.50%

421 ppm

1.2

8 μg/m³

188

3/3/24 7:05

25.4 ℃

78.50%

422 ppm

1.2

8 μg/m³

189

3/3/24 7:15

25.4 ℃

78.50%

422 ppm

1.1

8 μg/m³

190

3/3/24 7:25

25.5 ℃

78.50%

422 ppm

1.2

8 μg/m³

History_6707D32768530805

awoohaw · Mar 15, 2024

Excel Formula:

=FILTER($A$2:$G$190,MINUTE($A$2:$A$190)=6,"")

Cubist · Mar 15, 2024

Perhaps someone can provide a shorter solution.

Book2 (version 1).xlsb

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Time

Temperature

Humidity

CO2

TVOC

PM2.5

PM10

Time

Temperature

Humidity

CO2

TVOC

PM2.5

PM10

2

3/1/24 00:06

24.5 ℃

0.67

424 ppm

1

4 μg/m³

3/1/24 09:06

26.7 ℃

0.66

434 ppm

1.1

6 μg/m³

3

3/1/24 00:16

24.5 ℃

0.67

424 ppm

1

4 μg/m³

3/1/24 10:06

24.4 ℃

0.57

427 ppm

2.6

4 μg/m³

4

3/1/24 00:26

24.5 ℃

0.67

424 ppm

1

4 μg/m³

3/1/24 11:06

23.2 ℃

0.56

417 ppm

1.9

3 μg/m³

5

3/1/24 00:36

24.5 ℃

0.67

424 ppm

1

4 μg/m³

3/1/24 12:06

23.3 ℃

0.565

411 ppm

1

4 μg/m³

6

3/1/24 00:46

24.6 ℃

0.67

424 ppm

1

5 μg/m³

3/1/24 13:06

23.1 ℃

0.565

406 ppm

1

4 μg/m³

7

3/1/24 00:56

24.5 ℃

0.675

424 ppm

1

5 μg/m³

3/1/24 14:06

23.1 ℃

0.575

404 ppm

1

3 μg/m³

8

3/1/24 01:06

24.5 ℃

0.675

424 ppm

1

5 μg/m³

3/1/24 15:06

23 ℃

0.58

403 ppm

1

3 μg/m³

9

3/1/24 01:16

24.5 ℃

0.675

424 ppm

1

5 μg/m³

3/1/24 16:06

22.8 ℃

0.6

404 ppm

1

3 μg/m³

10

3/1/24 01:26

24.5 ℃

0.675

425 ppm

1

5 μg/m³

3/1/24 17:06

23.7 ℃

0.59

404 ppm

1

3 μg/m³

11

3/1/24 01:36

24.5 ℃

0.675

424 ppm

1

5 μg/m³

12

3/1/24 01:46

24.5 ℃

0.675

424 ppm

1

5 μg/m³

8 μg/m³

13

3/1/24 01:56

24.5 ℃

0.675

424 ppm

1

5 μg/m³

8 μg/m³

14

3/1/24 02:06

24.5 ℃

0.675

424 ppm

1

5 μg/m³

8 μg/m³

15

3/1/24 02:16

24.5 ℃

0.68

424 ppm

1

5 μg/m³

8 μg/m³

16

3/1/24 02:26

24.5 ℃

0.68

424 ppm

1

5 μg/m³

8 μg/m³

17

3/1/24 02:36

24.5 ℃

0.68

424 ppm

1

5 μg/m³

8 μg/m³

18

3/1/24 02:46

24.6 ℃

0.68

424 ppm

1

5 μg/m³

19

3/1/24 02:56

24.6 ℃

0.68

423 ppm

1

5 μg/m³

20

3/1/24 03:06

24.6 ℃

0.68

424 ppm

1

5 μg/m³

21

3/1/24 03:16

24.5 ℃

0.68

424 ppm

1

5 μg/m³

22

3/1/24 03:26

24.5 ℃

0.685

423 ppm

1

5 μg/m³

23

3/1/24 03:36

24.6 ℃

0.685

424 ppm

1

5 μg/m³

24

3/1/24 03:46

24.6 ℃

0.685

424 ppm

1

5 μg/m³

6 μg/m³

25

3/1/24 03:56

24.6 ℃

0.685

424 ppm

1

5 μg/m³

6 μg/m³

26

3/1/24 04:06

24.6 ℃

0.685

424 ppm

1

5 μg/m³

6 μg/m³

27

3/1/24 04:16

24.5 ℃

0.685

425 ppm

1

5 μg/m³

6 μg/m³

28

3/1/24 04:26

24.6 ℃

0.685

425 ppm

1

5 μg/m³

6 μg/m³

29

3/1/24 04:36

24.6 ℃

0.685

425 ppm

1

5 μg/m³

6 μg/m³

30

3/1/24 04:46

24.6 ℃

0.685

425 ppm

1

5 μg/m³

31

3/1/24 04:56

24.6 ℃

0.69

425 ppm

1

5 μg/m³

32

3/1/24 05:06

24.6 ℃

0.69

426 ppm

1

5 μg/m³

33

3/1/24 05:16

24.6 ℃

0.69

426 ppm

1

5 μg/m³

34

3/1/24 05:26

24.6 ℃

0.69

426 ppm

1

5 μg/m³

35

3/1/24 05:36

24.6 ℃

0.69

427 ppm

1

5 μg/m³

36

3/1/24 05:46

24.6 ℃

0.69

427 ppm

1

6 μg/m³

37

3/1/24 05:56

24.6 ℃

0.69

428 ppm

1

6 μg/m³

38

3/1/24 06:06

24.6 ℃

0.69

428 ppm

1

6 μg/m³

39

3/1/24 06:16

24.7 ℃

0.69

429 ppm

1

6 μg/m³

40

3/1/24 06:26

24.8 ℃

0.69

429 ppm

1

6 μg/m³

41

3/1/24 06:36

24.9 ℃

0.685

430 ppm

1

6 μg/m³

Sheet7

Cell Formulas
Range	Formula
I2:O10	I2	=FILTER(A2:G190,(MINUTE(A2:A190)=6)(HOUR(A2:A190)<18)(HOUR(A2:A190)>8)*((TEXT(A2:A190,"ddd")<>"Sat")+(TEXT(A2:A190,"ddd")<>"Sun")),"None")
A3:A41	A3	=A2+10/(24*60)
Dynamic array formulas.

awoohaw · Mar 15, 2024

may need to paste this in exactly cell I2. If not adjust the ranges.

Your minutes in your data change to 5 minutes at some point, so it seems only 1 is selected.

Mr Excel Questions 75.xlsm

I

J

K

L

M

N

O

2

Time

Temperature

Humidity

CO2

TVOC

PM2.5

PM10

3

2024-03-02 08:06:00

26.1 ℃

0.67

435 ppm

1.1

8 μg/m³

9 μg/m³

Sheet5

Cell Formulas
Range	Formula
I2:O3	I2	=VSTACK($A$1:$G$1,FILTER($A$2:$G$190, (MINUTE($A$2:$A$190)=6)* (HOUR($A$2:$A$190)>=8) * (HOUR($A$2:$A$190)<=18),""))
Dynamic array formulas.

Cubist · Mar 15, 2024

Minor change to the inequality to capture the hours correctly.

Excel Formula:

=FILTER(A2:G190,(MINUTE(A2:A190)=6)*(HOUR(A2:A190)<18)*(HOUR(A2:A190)>=8)*((TEXT(A2:A190,"ddd")<>"Sat")+(TEXT(A2:A190,"ddd")<>"Sun")),"None")

awoohaw · Mar 15, 2024

Cubist said:
Minor change to the inequality to capture the hours correctly.

Excel Formula:

=FILTER(A2:G190,(MINUTE(A2:A190)=6)*(HOUR(A2:A190)<18)*(HOUR(A2:A190)>=8)*((TEXT(A2:A190,"ddd")<>"Sat")+(TEXT(A2:A190,"ddd")<>"Sun")),"None")

@Cubist, the weekday function would make it shorter:

Excel Formula:

=VSTACK($A$1:$G$1,FILTER($A$2:$G$190,
(WEEKDAY($A$2:$A$190,3)<=5)*
(MINUTE($A$2:$A$190)=6)*
(HOUR($A$2:$A$190)>=8) *
(HOUR($A$2:$A$190)<18),""))

jappi · Mar 15, 2024

Thanks everyone. Very grateful for your help. I will try and plug these options in against the data and see how I go. Once again, your help is really appreciated.

Filter date time data so I only get one data point per hour

jappi

New Member

Attachments

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

jappi

New Member

Cubist

Well-known Member

jappi

New Member

awoohaw

Well-known Member

Cubist

Well-known Member

awoohaw

Well-known Member

Cubist

Well-known Member

awoohaw

Well-known Member

jappi

New Member

Similar threads

Forum statistics

Share this page

Filter date time data so I only get one data point per hour

New Member

Attachments

MrExcel MVP, Moderator

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock