Extract Max Value (Minutes) per day

[Cyril Beki]

I have Machine Problem Data with multiple records per day. I would like to extract the highest minutes per day. How do I get excel to do this ?
I have 17 Columns but Machine Problem Data is in column 13 while the Duration (minutes) take to fix the machine is in column 19

For example: raw data looks as follows

No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
1	10/1/2021	N/A	N/A	N/A	Belting	21:30	22:00	30:00
2	10/1/2021	N/A	N/A	N/A	Cylinder	23:00	01:00	120:00
3	10/2/2021	N/A	N/A	N/A	Cylinder	04:00	04:30	30:00
4	10/2/2021	N/A	N/A	N/A	Piping	06:00	08:00	120:00

Max per day table would look as follows:

Date	Problem	Max Duration
10/1/2021	Cylinder	120:00
10/2/2021	Piping	120:00

I already try to use pivot to extract maximum value but i get this error (refer image). I need max per day data to create line graph. I hope someone can help me with this. Thanks in advance

 

[jasonb75]

Based on the popup message, it looks like your times are text strings rather than valid times. If they were proper times then it would allow you to do what you want to.

Select the cells with the times, then press Shift Ctrl ~
If the times change to numbers then they are valid, if they stay as times then they are not valid.

Press Ctrl z to undo and return to the previous state.

 

[hrayani]

see if this helps

Book2
	A	B	C	D	E	F	G	H	I
1	No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)
2	1	10-Jan-21	N/A	N/A	N/A	Belting	21:30	22:00	30:00:00
3	2	10-Jan-21	N/A	N/A	N/A	Cylinder	23:00	1:00	120:00:00
4	3	10-Feb-21	N/A	N/A	N/A	Cylinder	4:00	4:30	30:00:00
5	4	10-Feb-21	N/A	N/A	N/A	Piping	6:00	8:00	120:00:00
6
7
8		DATE	Problem	Max Duration
9		10-Jan-21	Cylinder	120:00:00
10		10-Feb-21	Piping	120:00:00
Sheet1

Cell Formulas
Range	Formula
B9:B10	B9	=MIN(IF(B8<>$B$2:$B$5, $B$2:$B$5,""))
C9:C10	C9	=INDEX($F$2:$F$5,MATCH(1,(D9=$I$2:$I$5)*(B9=$B$2:$B$5),0))
D9:D10	D9	=MAX(IF(B9=$B$2:$B$5, $I$2:$I$5,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Cyril Beki]

Based on the popup message, it looks like your times are text strings rather than valid times. If they were proper times then it would allow you to do what you want to.

Select the cells with the times, then press Shift Ctrl ~
If the times change to numbers then they are valid, if they stay as times then they are not valid.

Press Ctrl z to undo and return to the previous state.

@jasonb75 i try doing your suggestion, it do change to number when i press shift ctrl ~.

 

[jasonb75]

In that case, it sounds like you're trying to set the pivot up incorrectly. I'm going to be afk for the rest of the day, hopefully @hrayani can provide further assistance.

 

[hrayani]

In that case, it sounds like you're trying to set the pivot up incorrectly. I'm going to be afk for the rest of the day, hopefully @hrayani can provide further assistance.

Hi Jason,
I have no knowledge about pivot.
I already offered what I had in post # 3 but it seems like the OP is not interested in that

 

[Cyril Beki]

H

Hi Jason,
I have no knowledge about pivot.
I already offered what I had in post # 3 but it seems like the OP is not interested in that

Hye @hrayani , currently trying your method . I’ll give you feedback once done

 

[hrayani]

H

Hye @hrayani , currently trying your method . I’ll give you feedback once done

Sure

 

[jasonb75]

I already try to use pivot to extract maximum value but i get this error (refer image).

How did you try setting up the pivot? I would have used Date and Problem as row headers (either one first depending on preference), then dragging Duration into the values box.

Book1
	A	B	C	D	E	F	G	H	I	J
1	No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
2	1	10/01/2021	N/A	N/A	N/A	Belting	21:30:00	22:00:00	30:00
3	2	10/01/2021	N/A	N/A	N/A	Cylinder	23:00:00	01:00:00	120:00
4	3	10/02/2021	N/A	N/A	N/A	Cylinder	04:00:00	04:30:00	30:00
5	4	10/02/2021	N/A	N/A	N/A	Piping	06:00:00	08:00:00	120:00
6
7
8		Row Labels	Max of Duration(Minutes)
9		10/01/2021	120:00
10		Belting	30:00
11		Cylinder	120:00
12		10/02/2021	120:00
13		Cylinder	30:00
14		Piping	120:00
15		Grand Total	120:00
Sheet27

Cell Formulas
Range	Formula
I2:I5	I2	=MOD(H2-G2,1)

 

[Cyril Beki]

Sure

hello @hrayani
I tried your formula and it works, but i would like the range to be automatically filled when new data is added. How can it do that ?

Example
For example: Data after added

No	Date	Something	Something	Something	Problem	Downtime	Uptime	Duration(Minutes)	Something
1	10/1/2021	N/A	N/A	N/A	Belting	21:30	22:00	30:00
2	10/1/2021	N/A	N/A	N/A	Cylinder	23:00	01:00	120:00
3	10/2/2021	N/A	N/A	N/A	Cylinder	04:00	04:30	30:00
4	10/2/2021	N/A	N/A	N/A	Piping	06:00	08:00	120:00
5	10/3/2021	N/A	N/A	N/A	Motor	07:00	09:00	120:00
6	10/3/2021	N/A	N/A	N/A	Servo	08:00	09:00	60:00

Automatically update below

Date	Problem	Max Duration
10/1/2021	Cylinder	120:00
10/2/2021	Piping	120:00
10/3/2021	Motor	120:00