GET MAX TIME VALUES BETWEEN TWO RANGES IN EXCEL LIST

[AFZAL SOHAIL]

Dear Time:-
I am sharing the list of Time value I need the max time value between two ranges:-
1. 08:00 to 09:45
2.10:00 to 14:45
3.15:00 to 18:45
I will be thankful to you
I need this formula in coloumn B:-

Duration	Duration
4 hr. 45 min.	4:45
1 hr. 41 min.	1:41
2 hr. 55 min.	2:55
8 hr. 58 min.	8:58
4 hr. 59 min.	4:59
5 hr. 31 min.	5:31
7 hr. 06 min.	7:06
11 hr. 04 min.	11:04
6 hr. 48 min.	6:48
14 hr. 20 min.	14:20
5 hr. 29 min.	5:29
1 hr. 45 min.	1:45
25 hr. 07 min.	25:07
3 hr. 15 min.	3:15

 

[t0ny84]

The two formulas above will get the results:

COUNTIF formula will give you how many results fall with in the time ranges.

MAXIFS formula will give you the maximum for the time ranges.

 

[AFZAL SOHAIL]

Sir, you are right,
I want to know the entries of these ranges, for example b5 has the 08-0900 ranges,
b9 and b11 has the results of 10-14.45
I think you understand my querry.

 

[t0ny84]

I believe the FILTER function would give you this.

=FILTER(range, (criteria_range1 = criteria1) * (criteria_range2 = criteria2))

=FILTER(B:B, (A:A >= TIME(8,0,0)) * (A:A <= TIME(9,0,0)))

 

[AFZAL SOHAIL]

Sir, I check the formula as you give me but it is giving the same values as colomn B, like this
=FILTER(B:B, (A:A >= TIME(8,0,0)) * (A:A <= TIME(9,0,0)))

Duration​	Duration​
4 hr. 45 min.​	04:45​
1 hr. 41 min.​	01:41​
2 hr. 55 min.​	02:55​
8 hr. 58 min.​	08:58​
4 hr. 59 min.​	04:59​
5 hr. 31 min.​	05:31​
7 hr. 06 min.​	07:06​
11 hr. 04 min.​	11:04​
6 hr. 48 min.​	06:48​
14 hr. 20 min.​	14:20​
5 hr. 29 min.​	05:29​
1 hr. 45 min.​	01:45​
25 hr. 07 min.​	25:07:00​
3 hr. 15 min.​	03:15​

And when I use the formula like as:-
=FILTER(B:B, (b:b >= TIME(8,0,0)) * (b:b <= TIME(9,0,0)))
It gives the values is as under:-

08:58​

11:04​

14:20​

01:07​

 

[t0ny84]

To get the maximum within the range you need to use:

=MAXIFS(A2:A15, A2:A15, ">="&TIME(8,0,0), A2:A15, "<="&TIME(9,0,0))

 

[AFZAL SOHAIL]

Sir,you already share this formula, i want to find out the cells also which have these values.

 

[t0ny84]

Ok if you want to get the cell value of the maximum value then try.

=ADDRESS(MATCH(MAXIFS(A2:A15, A2:A15, ">="&TIME(8,0,0), A2:A15, "<="&TIME(9,0,0)), A2:A15, 0) + ROW(A2:A15) - 1, COLUMN(A2), 4)

Is this what you want?

 

[AFZAL SOHAIL]

I want like this but the result is n/a as shown it down:

Duration	Duration
4 hr. 45 min.	04:45	#N/A​
1 hr. 41 min.	01:41	#N/A​
2 hr. 55 min.	02:55	#N/A​
8 hr. 58 min.	08:58	#N/A​
4 hr. 59 min.	04:59	#N/A​
5 hr. 31 min.	05:31	#N/A​
7 hr. 06 min.	07:06	#N/A​
11 hr. 04 min.	11:04	#N/A​
6 hr. 48 min.	06:48	#N/A​
14 hr. 20 min.	14:20	#N/A​
5 hr. 29 min.	05:29	#N/A​
1 hr. 45 min.	01:45	#N/A​
25 hr. 07 min.	25:07:00	#N/A​
3 hr. 15 min.	03:15	#N/A​

 

[t0ny84]

This formula will get you the maximum from the range.

=MAX(IF((A1:A100>=TIME(8,0,0))*(A1:A100<=TIME(9,0,0)), A1:A100))

and this formula will give the cell address.

=ADDRESS(MATCH(MAX(IF((A1:A100>=TIME(8,0,0))*(A1:A100<=TIME(9,0,0)), A1:A100)), A1:A100, 0), 1)

 

[AFZAL SOHAIL]

Thanks a lot Sir,
Both two formulas working very well.