Min/Max of date range

P

paconovellino

New Member
Joined
Sep 17, 2013
Messages
27
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
Hello everyone,
I was wondering if someone can help me out figuring this one out:
I have a table with 3 columns; date, start time and, end time and I would like to use a formula to find the min for the start column of a given date and the max of the end column for the same date. For example, 7/16 min 5:04 AM and max 2:04 PM. I prefer not to use pivot tables because after getting the min/max value, I need to get the time difference between the 2 IE: 2:04 PM - 5:04 AM.
Any help is appreciated.

Stay safe :)

datestartendminmax
7/16/216:30 AM6:51 AM
7/16/216:51 AM6:53 AM
7/16/216:53 AM7:19 AM
7/16/217:19 AM7:20 AM
7/16/217:36 AM7:36 AM
7/16/217:36 AM7:37 AM
7/16/217:50 AM7:51 AM
7/16/217:38 AM7:41 AM
7/16/217:41 AM7:45 AM
7/16/217:51 AM7:52 AM
7/16/217:47 AM7:49 AM
7/16/217:54 AM8:00 AM
7/16/218:29 AM8:31 AM
7/16/218:08 AM8:27 AM
7/16/218:47 AM8:47 AM
7/16/218:58 AM8:59 AM
7/16/2110:00 AM10:02 AM
7/16/219:59 AM10:00 AM
7/16/2110:14 AM10:15 AM
7/16/2110:03 AM10:14 AM
7/16/2110:16 AM10:31 AM
7/16/2110:40 AM10:52 AM
7/16/2110:31 AM10:39 AM
7/16/2110:52 AM11:12 AM
7/16/2111:12 AM11:13 AM
7/16/2111:28 AM11:29 AM
7/16/2111:36 AM11:37 AM
7/16/2111:35 AM11:36 AM
7/16/2111:31 AM11:35 AM
7/16/2111:15 AM11:28 AM
7/16/2111:52 AM11:52 AM
7/16/2111:52 AM12:18 PM
7/16/2112:18 PM12:21 PM
7/16/2112:21 PM1:07 PM
7/16/211:29 PM1:30 PM
7/16/211:32 PM1:33 PM
7/16/211:33 PM1:34 PM
7/16/211:07 PM1:29 PM
7/16/211:34 PM2:04 PM
7/19/214:59 AM5:00 AM
7/19/215:08 AM5:13 AM
7/19/215:14 AM5:14 AM
7/19/215:39 AM5:43 AM
7/19/215:47 AM6:03 AM
7/19/216:06 AM6:15 AM
7/19/216:29 AM6:35 AM
7/19/216:15 AM6:29 AM
7/19/216:35 AM6:41 AM
7/19/216:41 AM6:49 AM
7/19/216:49 AM6:51 AM
7/19/218:09 AM8:13 AM
7/19/217:48 AM7:48 AM
7/19/218:08 AM8:09 AM
7/19/217:48 AM7:48 AM
7/19/217:48 AM7:48 AM
7/19/217:51 AM7:51 AM
7/19/217:50 AM7:50 AM
7/19/217:47 AM7:47 AM
7/19/218:13 AM8:21 AM
7/19/217:50 AM7:50 AM
7/19/217:46 AM7:46 AM
7/19/217:50 AM7:50 AM
7/19/217:50 AM7:50 AM
7/19/217:51 AM7:51 AM
7/19/218:45 AM8:46 AM
7/19/218:21 AM8:42 AM
7/19/217:49 AM7:50 AM
7/19/218:43 AM8:45 AM
7/19/218:46 AM8:56 AM
7/19/219:24 AM9:38 AM
7/19/219:15 AM9:23 AM
7/19/2110:03 AM11:28 AM
7/19/2111:28 AM11:29 AM
7/19/2111:31 AM11:32 AM
7/19/2111:30 AM11:30 AM
7/19/2111:30 AM11:30 AM
7/19/2111:30 AM11:31 AM
7/19/2111:31 AM11:31 AM
7/19/2111:29 AM11:29 AM
7/19/2111:30 AM11:30 AM
7/19/2111:31 AM11:31 AM
7/19/2111:31 AM11:31 AM
7/19/2111:29 AM11:30 AM
7/19/2111:31 AM11:31 AM
7/19/2111:31 AM11:31 AM
7/19/2112:32 PM12:32 PM
7/19/2112:32 PM12:32 PM
7/19/2112:32 PM12:36 PM
7/19/211:52 PM1:52 PM
7/19/211:52 PM1:52 PM
7/19/211:51 PM1:51 PM
7/20/214:59 AM5:00 AM
7/20/215:58 AM6:05 AM
7/20/215:57 AM5:58 AM
7/20/216:05 AM6:25 AM
7/20/217:09 AM7:10 AM
7/20/216:52 AM7:08 AM
7/20/217:16 AM7:16 AM
7/20/217:10 AM7:14 AM
7/20/217:15 AM7:16 AM
7/20/217:15 AM7:15 AM
7/20/217:28 AM7:29 AM
7/20/217:16 AM7:16 AM
7/20/217:29 AM7:46 AM
7/20/217:53 AM7:53 AM
7/20/217:52 AM7:52 AM
7/20/217:52 AM7:52 AM
7/20/217:51 AM7:52 AM
7/20/217:15 AM7:15 AM
7/20/217:54 AM7:54 AM
7/20/217:53 AM7:53 AM
7/20/217:54 AM7:56 AM
7/20/217:54 AM7:54 AM
7/20/217:52 AM7:52 AM
7/20/217:46 AM7:51 AM
7/20/217:14 AM7:15 AM
7/20/217:53 AM7:53 AM
7/20/217:54 AM7:54 AM
7/20/217:53 AM7:53 AM
7/20/217:52 AM7:52 AM
7/20/217:53 AM7:53 AM
7/20/217:53 AM7:53 AM
7/20/217:52 AM7:52 AM
7/20/217:53 AM7:53 AM
7/20/218:35 AM8:56 AM
7/20/218:56 AM9:06 AM
7/20/219:06 AM9:07 AM
7/20/219:07 AM9:08 AM
7/20/219:14 AM9:35 AM
7/20/2110:34 AM10:53 AM
7/20/2110:53 AM11:07 AM
7/20/2111:07 AM11:09 AM
7/20/2111:09 AM11:31 AM
7/20/2111:53 AM11:54 AM
7/20/2111:51 AM11:52 AM
7/20/2111:53 AM11:53 AM
7/20/2111:51 AM11:51 AM
7/20/2111:53 AM11:53 AM
7/20/2111:53 AM11:53 AM
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Hi Fluff,
Thanks for sending the link. I'm updating my information
I'm using Excel for Mac V.16.50
 
Upvote 0
Thanks for that, how about
+Fluff 1.xlsm
ABCDEF
1datestartendminmax
216/07/20216:30 AM6:51 AM16/07/202106:30:0014:04:00
316/07/20216:51 AM6:53 AM19/07/202104:59:0013:52:00
416/07/20216:53 AM7:19 AM
516/07/20217:19 AM7:20 AM
616/07/20217:36 AM7:36 AM
716/07/20217:36 AM7:37 AM
816/07/20217:50 AM7:51 AM
916/07/20217:38 AM7:41 AM
1016/07/20217:41 AM7:45 AM
1116/07/20217:51 AM7:52 AM
1216/07/20217:47 AM7:49 AM
1316/07/20217:54 AM8:00 AM
1416/07/20218:29 AM8:31 AM
1516/07/20218:08 AM8:27 AM
1616/07/20218:47 AM8:47 AM
1716/07/20218:58 AM8:59 AM
1816/07/202110:00 AM10:02 AM
1916/07/20219:59 AM10:00 AM
2016/07/202110:14 AM10:15 AM
2116/07/202110:03 AM10:14 AM
2216/07/202110:16 AM10:31 AM
2316/07/202110:40 AM10:52 AM
2416/07/202110:31 AM10:39 AM
2516/07/202110:52 AM11:12 AM
2616/07/202111:12 AM11:13 AM
2716/07/202111:28 AM11:29 AM
2816/07/202111:36 AM11:37 AM
2916/07/202111:35 AM11:36 AM
3016/07/202111:31 AM11:35 AM
3116/07/202111:15 AM11:28 AM
3216/07/202111:52 AM11:52 AM
3316/07/202111:52 AM12:18 PM
3416/07/202112:18 PM12:21 PM
3516/07/202112:21 PM1:07 PM
3616/07/20211:29 PM1:30 PM
3716/07/20211:32 PM1:33 PM
3816/07/20211:33 PM1:34 PM
3916/07/20211:07 PM1:29 PM
4016/07/20211:34 PM2:04 PM
4119/07/20214:59 AM5:00 AM
4219/07/20215:08 AM5:13 AM
4319/07/20215:14 AM5:14 AM
4419/07/20215:39 AM5:43 AM
4519/07/20215:47 AM6:03 AM
4619/07/20216:06 AM6:15 AM
4719/07/20216:29 AM6:35 AM
4819/07/20216:15 AM6:29 AM
4919/07/20216:35 AM6:41 AM
5019/07/20216:41 AM6:49 AM
5119/07/20216:49 AM6:51 AM
5219/07/20218:09 AM8:13 AM
5319/07/20217:48 AM7:48 AM
5419/07/20218:08 AM8:09 AM
5519/07/20217:48 AM7:48 AM
5619/07/20217:48 AM7:48 AM
5719/07/20217:51 AM7:51 AM
5819/07/20217:50 AM7:50 AM
5919/07/20217:47 AM7:47 AM
6019/07/20218:13 AM8:21 AM
6119/07/20217:50 AM7:50 AM
6219/07/20217:46 AM7:46 AM
6319/07/20217:50 AM7:50 AM
6419/07/20217:50 AM7:50 AM
6519/07/20217:51 AM7:51 AM
6619/07/20218:45 AM8:46 AM
6719/07/20218:21 AM8:42 AM
6819/07/20217:49 AM7:50 AM
6919/07/20218:43 AM8:45 AM
7019/07/20218:46 AM8:56 AM
7119/07/20219:24 AM9:38 AM
7219/07/20219:15 AM9:23 AM
7319/07/202110:03 AM11:28 AM
7419/07/202111:28 AM11:29 AM
7519/07/202111:31 AM11:32 AM
7619/07/202111:30 AM11:30 AM
7719/07/202111:30 AM11:30 AM
7819/07/202111:30 AM11:31 AM
7919/07/202111:31 AM11:31 AM
8019/07/202111:29 AM11:29 AM
8119/07/202111:30 AM11:30 AM
8219/07/202111:31 AM11:31 AM
8319/07/202111:31 AM11:31 AM
8419/07/202111:29 AM11:30 AM
8519/07/202111:31 AM11:31 AM
8619/07/202111:31 AM11:31 AM
8719/07/202112:32 PM12:32 PM
8819/07/202112:32 PM12:32 PM
8919/07/202112:32 PM12:36 PM
9019/07/20211:52 PM1:52 PM
9119/07/20211:52 PM1:52 PM
9219/07/20211:51 PM1:51 PM
9320/07/20214:59 AM5:00 AM
Main
Cell Formulas
RangeFormula
E2:E3E2=MINIFS(B:B,A:A,D2)
F2:F3F2=MAXIFS(C:C,A:A,D2)
 
Upvote 0
Thank you! It helped a lot.
I figure out I needed another column and added the minifs as per your example. Now I would like to show the result just in the row in which the value is the same. For example E2 = 5:04. E3 blank, etc.

Thanks again.

iddatestartendminmax
100-517/16/215:04 AM5:05 AM5:04:38 AM2:04:40 PM
100-517/16/216:11 AM6:12 AMNo Value from this point until next date2:04:40 PM
100-517/16/216:15 AM6:27 AM5:04:38 AM2:04:40 PM
100-517/16/216:27 AM6:29 AM5:04:38 AM2:04:40 PM
100-517/16/216:30 AM6:51 AM5:04:38 AM2:04:40 PM
100-517/16/216:51 AM6:53 AM5:04:38 AM2:04:40 PM
100-517/16/216:53 AM7:19 AM5:04:38 AM2:04:40 PM
100-517/16/217:19 AM7:20 AM5:04:38 AM2:04:40 PM
100-517/16/217:36 AM7:36 AM5:04:38 AM2:04:40 PM
100-517/16/217:36 AM7:37 AM5:04:38 AM2:04:40 PM
100-517/16/217:38 AM7:41 AM5:04:38 AM2:04:40 PM
100-517/16/217:41 AM7:45 AM5:04:38 AM2:04:40 PM
100-517/16/217:47 AM7:49 AM5:04:38 AM2:04:40 PM
100-517/16/217:50 AM7:51 AM5:04:38 AM2:04:40 PM
100-517/16/217:51 AM7:52 AM5:04:38 AM2:04:40 PM
100-517/16/217:54 AM8:00 AM5:04:38 AM2:04:40 PM
100-517/16/218:08 AM8:27 AM5:04:38 AM2:04:40 PM
 
Upvote 0
With your original data use
Excel Formula: 
=IF(B2=MINIFS(B:B,A:A,A2),B2,"")
and copy down
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Pivot Table with Overlapping Row
Replies
0
Views
82
Cubist
C
K
  • Question
conditional formatting question
Replies
2
Views
153
kerm007
K
A
  • Question
Excel Formula.
Replies
1
Views
181
StephenCrump
S
C
  • Solved
Dynamic Formula To Label Rolling 3 Months.
Replies
1
Views
111
Eric W
Eric W
C
  • Solved
Return values based on date range
Replies
3
Views
151
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,639
Messages
6,120,679
Members
448,977
Latest member
dbonilla0331

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top