EXTRACTING MIN AND MAX TIME FROM TIME STRING

[dockery0848]

I need to extract the minimum and maximum times (separately) from a list of times based on one criteria. The list is in military time and I am not getting the max time correct.
this is the formula i am using - =IF(ISERROR(MAXIFS(PLAN!$O:$O,PLAN!$D:$D,'Perf Ind'!L4)),"",MAXIFS(PLAN!$O:$O,PLAN!$D:$D,'Perf Ind'!L4)) - I extracted the start and end times from the string in column E and F but when I put in the formula for max time, it gives me 23:00 because that is the biggest. How can I make this formula pick 4:55?

Op No.	SortProgram	Tour	Run#	Start	End	Fed	MODS	Last Updated	FED	START	END
439000​	439PCITY	1​	47​	5/18/2023 23:00​	5/19/2023 4:55​	28187​	5/18/2023​	5/4/2023 9:05​	28187​	23:00​	4:55​
439000​	439PCITY	3​	48​	5/18/2023 17:00​	5/18/2023 23:00​	28584​	5/18/2023​	5/4/2023 9:05​	28584​	17:00​	23:00​

 

[Anthony47]

Could you give the coordinates of your data? Which columns the date&time is in? Is this on a web based workbook or on a local, pc based, workbook?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFERROR(MOD(MAXIFS(PLAN!$F:$F,PLAN!$D:$D,'Perf Ind'!L4),1),"")

 

[dockery0848]

Gave me the same 23:00 and I was looking for 04:45, which would be the latest time.

 

[dockery0848]

the problem is that the numbers are formatted in 24 hour format - 13:50, with a colon and I can't get it to extract the latest one - to recognize the numbers past midnight at the later times. Please help

 

[Fluff]

Did you use col F which has the date? as it works for me

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Op No.	SortProgram	Tour	Run#	Start	End	Fed	MODS	Last Updated	FED	START	END	47
2	439000	439PCITY	1	47	18/05/2023 23:00	19/05/2023 04:55	28187	18/05/2023	04/05/2023 09:05	28187	23:00	04:55	04:55:00
3	439000	439PCITY	3	48	18/05/2023 17:00	18/05/2023 23:00	28584	18/05/2023	04/05/2023 09:05	28584	17:00	23:00
4	439000	439PCITY	1	47	18/05/2023 23:00	19/05/2023 03:55	28187	18/05/2023	04/05/2023 09:05	28187	23:00	04:55
5	439000	439PCITY	3	47	18/05/2023 17:00	18/05/2023 23:00	28584	18/05/2023	04/05/2023 09:05	28584	17:00	23:00
Master

Cell Formulas
Range	Formula
M2	M2	=IFERROR(MOD(MAXIFS($F:$F,$D:$D,M1),1),"")

 

[dockery0848]

What is the "M1"?

 

[dockery0848]

Maybe I'm not explaining it correctly but I really appreciate the help. I am attempting to extract the earliest (in one column) and the latest (in another column) for the same operation in the first column. For example, in the example, I should get 17:00 as the earliest and 4:55 for the latest. The issue I am having is that this is 24 hour format and I always get the latest time as 23:00.

 

[dockery0848]

Wait, Operator error, that worked!!! Dancing on the ceiling - thank you so much!

 

[dockery0848]

Will that same formula work for extracting the minimum from Column E?