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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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

S
  • Question Question
Add Conditional formatting via VBA to a multiple sheets
Replies
4
Views
153
Schturman
S
J
  • Question Question
Average of Two Columns Meeting a Specific Criterion
Replies
4
Views
200
JeffFinnan
J
G
  • Question Question
What dates are the first zero, and the first non-zero after a specified date
Replies
8
Views
398
gooniegirl180
G
A
  • Solved
Count entry by time
Replies
3
Views
196
admvlad
A
E
  • Question Question
Best Solution for returning data - index?
Replies
5
Views
185
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,997
Messages
6,175,871
Members
452,679
Latest member
darryl47nopra

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