MAXIFs not working.

[ItalianPlatinum]

I am trying to find a way when to locate the max time when two jobs execute at the same time. Instance 1 and 2 will start at the same time but may finish at different times. So I need to know the elapsed time for the two jobs to complete. See below for XLBB. As you can see it just pulls zero.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1									REPLACE(REPLACE(G3,15,1,":"),18,1,":")	REPLACE(REPLACE(H3,15,1,":"),18,1,":")	MAXIFS($J$3:$J$214,$F$3:$F$214,K3,$A$3:$A$214,A3)
2	ACCOUNT	Instance	TO_DATE	STATUS	DAYS	SUBMISSION	EXECUTION	COMPLETION	EXECUTION_TIME	COMPLETION_TIME	MAX TIME
3	AB	1	10/3/2024	C	1	03-OCT-2024 02.36.20.000000000 PM	03-OCT-2024 02.36.29.678000000 PM	03-OCT-2024 02.36.38.681000000 PM	03-OCT-2024 02:36:29.678000000 PM	03-OCT-2024 02:36:38.681000000 PM	0
4	AB	2	10/3/2024	C	1	03-OCT-2024 02.36.20.000000000 PM	03-OCT-2024 02.36.29.921000000 PM	03-OCT-2024 02.36.38.881000000 PM	03-OCT-2024 02:36:29.921000000 PM	03-OCT-2024 02:36:38.881000000 PM	0
5	CA	1	10/3/2024	C	1	03-OCT-2024 03.36.20.000000000 PM	03-OCT-2024 03.36.29.678000000 PM	03-OCT-2024 03.37.29.678000000 PM	03-OCT-2024 03:36:29.678000000 PM	03-OCT-2024 03:37:29.678000000 PM	0
6	CA	2	10/3/2024	C	1	03-OCT-2024 03.36.20.000000000 PM	03-OCT-2024 03.36.29.921000000 PM	03-OCT-2024 03.38.29.678000000 PM	03-OCT-2024 03:36:29.921000000 PM	03-OCT-2024 03:38:29.678000000 PM	0
7	BC	1	10/3/2024	C	1	03-OCT-2024 03.46.20.000000000 PM	03-OCT-2024 03.46.29.921000000 PM	03-OCT-2024 03.49.28.921000000 PM	03-OCT-2024 03:46:29.921000000 PM	03-OCT-2024 03:49:28.921000000 PM	0
8	BC	2	10/3/2024	C	1	03-OCT-2024 03.46.20.000000000 PM	03-OCT-2024 03.46.29.921000000 PM	03-OCT-2024 03.49.31.921000000 PM	03-OCT-2024 03:46:29.921000000 PM	03-OCT-2024 03:49:31.921000000 PM	0
Sheet1

Cell Formulas
Range	Formula
I3:J8	I3	=REPLACE(REPLACE(G3,15,1,":"),18,1,":")
K3:K8	K3	=MAXIFS($J$3:$J$214,$F$3:$F$214,K3,$A$3:$A$214,A3)

 

[Joe4]

The MAXIFS function is really designed to work on valid numeric or date/time values.
You appear to be working with TEXT values. So since TEXT entries have a value of 0, your MAXIFS function will return a 0.
The left-justification and triangle in the upper left corner of your entries in columns I and J are telling you that these are Text values.

To coerce them to valid date/time value, simply add zero to the end of your formulas in columns I and J, i.e.

=REPLACE(REPLACE(G3,15,1,":"),18,1,":")+0

The result will look like a strange number, because Excel stores dates and times as numbers, specifically the number of days since 1/0/1900. But if you simply format these columns in your desired date/time format, you will see the dates and times like you are used to.

Now that the values in columns I and J are valid date/time entries, you will be able to use MAXIFS functions on them.

EDIT:
Note: I have not evaluated your MAXIFS function yet, as you need to fix the data first. But I am guessing that you may have issues there too, as you appear to have a circular reference.

 

[Joe4]

After you fix the data like I said, I believe that your formula in cell K3 should look something like this:

=MAXIFS($J$3:$J$214,$I$3:$I$214,I3)

 

[kvsrinivasamurthy]

Formula in K3 refers K3. It will cause Circular Reference.
=MAXIFS($J$3:$J$214,$F$3:$F$214,K3,$A$3:$A$214,A3)

 

[ItalianPlatinum]

I think that worked. I had to create another submission column in order to achieve the +0 as you mentioned. Once I did that and changed the MAXIFs to reference the formulas with +0 it seems to be working

 

[Joe4]

Excellent!
I am glad that worked out for you.