Identify time(s) when max occurred

[edwardj3]

Hi,

I have a data source in the following format with date, hour and Month derived from the @Timestamp per hour field

@Timestamp per hour	Count of records	date	hour	Month
01/04/2021 00:00​	8​	01/04/2021​	00:00	Apr-21​
01/04/2021 01:00​	12​	01/04/2021​	01:00	Apr-21​

The data is used in a pivot table as below which is updated with new data periodically throughout a month.

Hour

Date

00:00

01:00

02:00

03:00

04:00

05:00

06:00

07:00

08:00

09:00

10:00

11:00

12:00

13:00

14:00

15:00

16:00

17:00

18:00

19:00

20:00

21:00

22:00

23:00

Grand Total

01-Apr

8​

12​

2​

3​

1​

3​

0​

1​

5​

11​

8​

3​

17​

15​

8​

10​

11​

9​

17​

5​

10​

9​

8​

6​

182​

02-Apr

8​

7​

3​

5​

5​

2​

0​

1​

8​

7​

8​

8​

9​

7​

12​

15​

11​

6​

6​

6​

10​

16​

9​

6​

175​

03-Apr

6​

10​

8​

1​

2​

2​

0​

1​

5​

8​

10​

5​

6​

8​

8​

10​

12​

6​

5​

10​

11​

8​

7​

17​

166​

04-Apr

15​

19​

4​

6​

1​

1​

0​

2​

7​

9​

6​

11​

10​

16​

12​

7​

11​

13​

13​

15​

14​

8​

13​

6​

219​

05-Apr

8​

1​

4​

2​

2​

4​

0​

1​

5​

8​

7​

6​

7​

6​

4​

12​

11​

20​

10​

12​

16​

9​

11​

7​

173​

06-Apr

5​

6​

6​

4​

3​

2​

3​

1​

5​

5​

11​

7​

9​

12​

5​

17​

12​

9​

5​

7​

9​

8​

10​

8​

169​

07-Apr

7​

5​

8​

7​

3​

2​

0​

6​

4​

9​

7​

8​

4​

8​

7​

12​

8​

11​

9​

6​

8​

14​

6​

8​

167​

08-Apr

14​

3​

2​

1​

0​

2​

1​

4​

7​

3​

5​

11​

6​

5​

7​

16​

12​

17​

9​

11​

4​

10​

9​

11​

170​

09-Apr

14​

8​

2​

5​

3​

1​

1​

8​

8​

3​

8​

7​

8​

8​

5​

7​

6​

7​

7​

9​

9​

16​

11​

19​

180​

10-Apr

10​

7​

2​

3​

1​

1​

1​

1​

2​

5​

12​

10​

14​

10​

9​

13​

10​

6​

11​

9​

10​

5​

10​

12​

174​

11-Apr

22​

11​

9​

3​

2​

1​

1​

2​

2​

9​

0​

0​

7​

7​

5​

6​

14​

16​

6​

7​

7​

6​

3​

8​

154​

12-Apr

9​

4​

2​

1​

3​

1​

2​

4​

4​

5​

12​

9​

4​

9​

6​

2​

6​

7​

7​

14​

10​

12​

5​

11​

149​

13-Apr

2​

4​

5​

3​

2​

0​

2​

0​

2​

13​

11​

11​

9​

15​

9​

11​

9​

11​

8​

9​

5​

11​

7​

1​

160​

14-Apr

4​

4​

4​

7​

2​

5​

4​

4​

5​

11​

10​

11​

18​

11​

10​

10​

9​

9​

7​

4​

2​

3​

8​

7​

169​

15-Apr

6​

6​

2​

2​

3​

1​

0​

0​

3​

15​

14​

11​

5​

12​

14​

9​

18​

16​

9​

10​

7​

9​

8​

8​

188​

16-Apr

10​

5​

8​

2​

3​

2​

1​

1​

8​

8​

11​

9​

8​

17​

7​

2​

14​

11​

10​

20​

9​

6​

14​

10​

196​

17-Apr

11​

13​

8​

4​

2​

1​

0​

0​

0​

0​

5​

12​

7​

7​

8​

11​

16​

13​

12​

14​

9​

11​

10​

12​

186​

18-Apr

10​

10​

8​

12​

2​

2​

0​

1​

7​

12​

11​

10​

11​

9​

15​

9​

6​

16​

12​

8​

12​

5​

6​

10​

204​

19-Apr

9​

0​

7​

9​

3​

4​

0​

1​

4​

17​

17​

8​

7​

15​

17​

9​

10​

13​

10​

6​

7​

5​

7​

4​

189​

20-Apr

14​

5​

9​

3​

2​

3​

1​

1​

8​

13​

14​

12​

16​

12​

8​

12​

13​

11​

3​

5​

16​

8​

6​

3​

198​

21-Apr

10​

4​

2​

3​

1​

1​

3​

3​

4​

19​

9​

15​

8​

14​

8​

21​

11​

1​

15​

5​

14​

5​

5​

12​

193​

22-Apr

2​

2​

5​

7​

0​

0​

0​

2​

11​

17​

21​

16​

15​

12​

10​

13​

13​

11​

12​

3​

14​

14​

8​

4​

212​

Grand Total

204​

146​

110​

93​

46​

41​

20​

45​

114​

207​

217​

200​

205​

235​

194​

234​

243​

239​

203​

195​

213​

198​

181​

190​

3973​

I would like to identify the hour(s) that the max count in each day occurred and display these something like below. I have manually added the values in When as example and to show the max may occur one or more times within the 24hr period.

Date	Max in Hr	When
01-Apr	17​	12:00 & 18:00
02-Apr	16​	21:00​
03-Apr	17​
04-Apr	19​
05-Apr	20​
06-Apr	17​
07-Apr	14​
08-Apr	17​
09-Apr	19​
10-Apr	14​
11-Apr	22​
12-Apr	14​
13-Apr	15​
14-Apr	18​
15-Apr	18​
16-Apr	20​
17-Apr	16​
18-Apr	16​
19-Apr	17​	09:00, 10:00, & 14:00
20-Apr	16​
21-Apr	21​
22-Apr	21​

Any advice on how the When column could be auto-populated would be gratefully received.

Thanks

 

[JamesCanale]

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		0:00	1:00	2:00	3:00	4:00	5:00	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	TotsMcGoats
2	1-Apr	8	12	2	3	1	3	0	1	5	11	8	3	17	15	8	10	11	9	17	5	10	9	8	6	182
3	2-Apr	8	7	3	5	5	2	0	1	8	7	8	8	9	7	12	15	11	6	6	6	10	16	9	6	175
4	3-Apr	6	10	8	1	2	2	0	1	5	8	10	5	6	8	8	10	12	6	5	10	11	8	7	17	166
5	4-Apr	15	19	4	6	1	1	0	2	7	9	6	11	10	16	12	7	11	13	13	15	14	8	13	6	219
6	5-Apr	8	1	4	2	2	4	0	1	5	8	7	6	7	6	4	12	11	20	10	12	16	9	11	7	173
7	6-Apr	5	6	6	4	3	2	3	1	5	5	11	7	9	12	5	17	12	9	5	7	9	8	10	8	169
8	7-Apr	7	5	8	7	3	2	0	6	4	9	7	8	4	8	7	12	8	11	9	6	8	14	6	8	167
9	8-Apr	14	3	2	1	0	2	1	4	7	3	5	11	6	5	7	16	12	17	9	11	4	10	9	11	170
10	9-Apr	14	8	2	5	3	1	1	8	8	3	8	7	8	8	5	7	6	7	7	9	9	16	11	19	180
11	10-Apr	10	7	2	3	1	1	1	1	2	5	12	10	14	10	9	13	10	6	11	9	10	5	10	12	174
12	11-Apr	22	11	9	3	2	1	1	2	2	9	0	0	7	7	5	6	14	16	6	7	7	6	3	8	154
13	12-Apr	9	4	2	1	3	1	2	4	4	5	12	9	4	9	6	2	6	7	7	14	10	12	5	11	149
14	13-Apr	2	4	5	3	2	0	2	0	2	13	11	11	9	15	9	11	9	11	8	9	5	11	7	1	160
15	14-Apr	4	4	4	7	2	5	4	4	5	11	10	11	18	11	10	10	9	9	7	4	2	3	8	7	169
16	15-Apr	6	6	2	2	3	1	0	0	3	15	14	11	5	12	14	9	18	16	9	10	7	9	8	8	188
17	16-Apr	10	5	8	2	3	2	1	1	8	8	11	9	8	17	7	2	14	11	10	20	9	6	14	10	196
18	17-Apr	11	13	8	4	2	1	0	0	0	0	5	12	7	7	8	11	16	13	12	14	9	11	10	12	186
19	18-Apr	10	10	8	12	2	2	0	1	7	12	11	10	11	9	15	9	6	16	12	8	12	5	6	10	204
20	19-Apr	9	0	7	9	3	4	0	1	4	17	17	8	7	15	17	9	10	13	10	6	7	5	7	4	189
21	20-Apr	14	5	9	3	2	3	1	1	8	13	14	12	16	12	8	12	13	11	3	5	16	8	6	3	198
22	21-Apr	10	4	2	3	1	1	3	3	4	19	9	15	8	14	8	21	11	1	15	5	14	5	5	12	193
23	22-Apr	2	2	5	7	0	0	0	2	11	17	21	16	15	12	10	13	13	11	12	3	14	14	8	4	212
24
25	Date	Day Max	When?
26	1-Apr	17	12:00, 18:00
27	2-Apr	16	21:00
28	3-Apr	17	23:00
29	4-Apr	19	1:00
30	5-Apr	20	17:00
31	6-Apr	17	15:00
Sheet9

Cell Formulas
Range	Formula
A26:A31	A26	=A2
B26:B31	B26	=MAX(B2:Y2)
C26:C31	C26	=TEXTJOIN(", ",TRUE,IF(B2:Y2=B26,INT(24*B$1:Y$1)&":00",""))

 

[edwardj3]

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		0:00	1:00	2:00	3:00	4:00	5:00	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00	TotsMcGoats
2	1-Apr	8	12	2	3	1	3	0	1	5	11	8	3	17	15	8	10	11	9	17	5	10	9	8	6	182
3	2-Apr	8	7	3	5	5	2	0	1	8	7	8	8	9	7	12	15	11	6	6	6	10	16	9	6	175
4	3-Apr	6	10	8	1	2	2	0	1	5	8	10	5	6	8	8	10	12	6	5	10	11	8	7	17	166
5	4-Apr	15	19	4	6	1	1	0	2	7	9	6	11	10	16	12	7	11	13	13	15	14	8	13	6	219
6	5-Apr	8	1	4	2	2	4	0	1	5	8	7	6	7	6	4	12	11	20	10	12	16	9	11	7	173
7	6-Apr	5	6	6	4	3	2	3	1	5	5	11	7	9	12	5	17	12	9	5	7	9	8	10	8	169
8	7-Apr	7	5	8	7	3	2	0	6	4	9	7	8	4	8	7	12	8	11	9	6	8	14	6	8	167
9	8-Apr	14	3	2	1	0	2	1	4	7	3	5	11	6	5	7	16	12	17	9	11	4	10	9	11	170
10	9-Apr	14	8	2	5	3	1	1	8	8	3	8	7	8	8	5	7	6	7	7	9	9	16	11	19	180
11	10-Apr	10	7	2	3	1	1	1	1	2	5	12	10	14	10	9	13	10	6	11	9	10	5	10	12	174
12	11-Apr	22	11	9	3	2	1	1	2	2	9	0	0	7	7	5	6	14	16	6	7	7	6	3	8	154
13	12-Apr	9	4	2	1	3	1	2	4	4	5	12	9	4	9	6	2	6	7	7	14	10	12	5	11	149
14	13-Apr	2	4	5	3	2	0	2	0	2	13	11	11	9	15	9	11	9	11	8	9	5	11	7	1	160
15	14-Apr	4	4	4	7	2	5	4	4	5	11	10	11	18	11	10	10	9	9	7	4	2	3	8	7	169
16	15-Apr	6	6	2	2	3	1	0	0	3	15	14	11	5	12	14	9	18	16	9	10	7	9	8	8	188
17	16-Apr	10	5	8	2	3	2	1	1	8	8	11	9	8	17	7	2	14	11	10	20	9	6	14	10	196
18	17-Apr	11	13	8	4	2	1	0	0	0	0	5	12	7	7	8	11	16	13	12	14	9	11	10	12	186
19	18-Apr	10	10	8	12	2	2	0	1	7	12	11	10	11	9	15	9	6	16	12	8	12	5	6	10	204
20	19-Apr	9	0	7	9	3	4	0	1	4	17	17	8	7	15	17	9	10	13	10	6	7	5	7	4	189
21	20-Apr	14	5	9	3	2	3	1	1	8	13	14	12	16	12	8	12	13	11	3	5	16	8	6	3	198
22	21-Apr	10	4	2	3	1	1	3	3	4	19	9	15	8	14	8	21	11	1	15	5	14	5	5	12	193
23	22-Apr	2	2	5	7	0	0	0	2	11	17	21	16	15	12	10	13	13	11	12	3	14	14	8	4	212
24
25	Date	Day Max	When?
26	1-Apr	17	12:00, 18:00
27	2-Apr	16	21:00
28	3-Apr	17	23:00
29	4-Apr	19	1:00
30	5-Apr	20	17:00
31	6-Apr	17	15:00
Sheet9

Cell Formulas
Range	Formula
A26:A31	A26	=A2
B26:B31	B26	=MAX(B2:Y2)
C26:C31	C26	=TEXTJOIN(", ",TRUE,IF(B2:Y2=B26,INT(24*B$1:Y$1)&":00",""))

Thank you for the reply @JamesCanale
I've applied the formulas to the ranges in my worksheet but I get blanks in the When column.

I have copied your solution into a blank worksheet in A1 so all values are in the same cell ref but again I am returning blanks.

Do the date and/or time need to be in a particular format? I don't understand what the formula in C26 is doing tbh. I get that if value matches the max value but how does the formula then know to return the appropriate cell value(s) for the hour?

 

[edwardj3]

Thank you for the reply @JamesCanale
I've applied the formulas to the ranges in my worksheet but I get blanks in the When column.

I have copied your solution into a blank worksheet in A1 so all values are in the same cell ref but again I am returning blanks.

Do the date and/or time need to be in a particular format? I don't understand what the formula in C26 is doing tbh. I get that if value matches the max value but how does the formula then know to return the appropriate cell value(s) for the hour?

I sorted it @JamesCanale. The formula in C26 should be an array {=TEXTJOIN(",",TRUE,IF(B77:Y77=AC77,INT(24*B76:Y76)&":00",""))} Works perfectly now. Thank you.