Identify time(s) when max occurred

edwardj3

New Member
Joined
Jan 16, 2018
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi,

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

@Timestamp per hourCount of recordsdatehourMonth
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
Date00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00Grand 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.

DateMax in HrWhen
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
MrExcelPlayground2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
10:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00TotsMcGoats
21-Apr81223130151183171581011917510986182
32-Apr87355201878897121511666101696175
43-Apr6108122015810568810126510118717166
54-Apr151946110279611101612711131315148136219
65-Apr8142240158767641211201012169117173
76-Apr56643231551179125171295798108169
87-Apr758732064978487128119681468167
98-Apr14321021473511657161217911410911170
109-Apr1482531188387885767799161119180
1110-Apr10723111125121014109131061191051012174
1211-Apr2211932112290077561416677638154
1312-Apr94213124451294962677141012511149
1413-Apr2453202021311119159119118951171160
1514-Apr4447254451110111811101099742387169
1615-Apr66223100315141151214918169107988188
1716-Apr105823211881198177214111020961410196
1817-Apr11138421000051277811161312149111012186
1918-Apr101081222017121110119159616128125610204
2019-Apr9079340141717871517910131067574189
2120-Apr1459323118131412161281213113516863198
2221-Apr104231133419915814821111155145512193
2322-Apr2257000211172116151210131311123141484212
24
25DateDay MaxWhen?
261-Apr1712:00, 18:00
272-Apr1621:00
283-Apr1723:00
294-Apr191:00
305-Apr2017:00
316-Apr1715:00
Sheet9
Cell Formulas
RangeFormula
A26:A31A26=A2
B26:B31B26=MAX(B2:Y2)
C26:C31C26=TEXTJOIN(", ",TRUE,IF(B2:Y2=B26,INT(24*B$1:Y$1)&":00",""))
 
Solution

edwardj3

New Member
Joined
Jan 16, 2018
Messages
33
Office Version
  1. 365
Platform
  1. Windows
MrExcelPlayground2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
10:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00TotsMcGoats
21-Apr81223130151183171581011917510986182
32-Apr87355201878897121511666101696175
43-Apr6108122015810568810126510118717166
54-Apr151946110279611101612711131315148136219
65-Apr8142240158767641211201012169117173
76-Apr56643231551179125171295798108169
87-Apr758732064978487128119681468167
98-Apr14321021473511657161217911410911170
109-Apr1482531188387885767799161119180
1110-Apr10723111125121014109131061191051012174
1211-Apr2211932112290077561416677638154
1312-Apr94213124451294962677141012511149
1413-Apr2453202021311119159119118951171160
1514-Apr4447254451110111811101099742387169
1615-Apr66223100315141151214918169107988188
1716-Apr105823211881198177214111020961410196
1817-Apr11138421000051277811161312149111012186
1918-Apr101081222017121110119159616128125610204
2019-Apr9079340141717871517910131067574189
2120-Apr1459323118131412161281213113516863198
2221-Apr104231133419915814821111155145512193
2322-Apr2257000211172116151210131311123141484212
24
25DateDay MaxWhen?
261-Apr1712:00, 18:00
272-Apr1621:00
283-Apr1723:00
294-Apr191:00
305-Apr2017:00
316-Apr1715:00
Sheet9
Cell Formulas
RangeFormula
A26:A31A26=A2
B26:B31B26=MAX(B2:Y2)
C26:C31C26=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

New Member
Joined
Jan 16, 2018
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,815
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top