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]

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

Hi Afzal,

The following formula will get you the results from the durations entered in the h:mm format (2nd column above).
You will need to change the number formatting of the formula's result to h:mm

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

MAXIFS will find the maximum from the range based on the criteria entered.

=MAXIFS(
A2:A15 <- range to search for the maximum.
A2:A15 <- search range for first criteria.
">="&TIME(8,0,0) <- first criteria - Greater Than Or Equal To 8:00.
A2:A15 <- search range for second criteria.
"<="&TIME(9,0,0) <- second criteria - Less Than Or Equal To 9:00
)

 

[AFZAL SOHAIL]

Thanks a lot Sir,
I will check and give you response.

 

[AFZAL SOHAIL]

I check this formula it give me like these values in h:mm format:-
But i need to count how many times comes the time value between 08-09
10-14 and 15-18 hrs range:

9:40​

12:56​

18:26​

Again thanks:

 

[t0ny84]

So to confirm, you are wanting to find how many results fall within each time period?

E.g. Using your data above you would expect to see the following results;

Range - Result
1. 08:00 to 09:45 - 1
2.10:00 to 14:45 - 2
3.15:00 to 18:45 - 0

t0ny84

 

[AFZAL SOHAIL]

Yes Sir, I expected this the same:

 

[t0ny84]

Yes Sir, I expected this the same:

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

This formula should work, for the other time ranges you would just need to update the numbers in the TIME(hours, minutes, seconds) portions.

t0ny84

 

[AFZAL SOHAIL]

Thanks a lot Sir, it is working well.
Can it is possible also view the entries of countif formula results?

 

[t0ny84]

To confirm you want to know how many fall in each category/range and also get the results for each category/range?

Or do you want to get how many are in each category/range and also get the maximum for each category/range?

 

[AFZAL SOHAIL]

Thanks a lot Sir,
I want to get how many are in each category/range and also get the maximum for each category/range?