First/Last Date in a Range

N

normb4

New Member
Joined
Feb 25, 2009
Messages
10
I have a stubborn issue that has bugged me for a long time in Excel.

I have a report that renders a long string of date and times in a column. What I need to do is to locate the first event and last event for each day in that range.

Example: the data in columns looks something like this

<table style="border-collapse: collapse; width: 190px; height: 440px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 84pt;" width="112"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 84pt;" width="112" height="20">2/3/2009 15:29:53</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/3/2009 15:34:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/3/2009 22:15:49</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/3/2009 22:17:26</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/4/2009 12:30:47</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/4/2009 12:30:49</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/4/2009 14:07:03</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/4/2009 14:19:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/5/2009 15:27:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/5/2009 16:29:37</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2/5/2009 22:06:44</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2/5/2009 22:06:45</td> </tr> </tbody></table>

What I need to produce the information in a separate column/row to look like this....

<table style="border-collapse: collapse; width: 168pt;" border="0" cellpadding="0" cellspacing="0" width="224"><col style="width: 84pt;" span="2" width="112"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 84pt;" width="112" height="20">First</td> <td style="width: 84pt;" width="112">Last</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/3/2009 15:29:53</td> <td class="xl65">2/3/2009 22:17:26</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/4/2009 12:30:47</td> <td class="xl65">2/4/2009 14:19:21</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2/5/2009 15:27:00</td> <td class="xl65">2/5/2009 22:06:45</td> </tr> </tbody></table>

Any ideas? rather than manually scrolling thru, copy and pasting them.... which takes forever when your dealing with a 30 day report with around 100 entries per day (have have to reproduce the information for around 20 samples)
 
Sort by date Sort by votes
If B2 contains an Excel serial date, the formula
=MIN(INT(A1:A100)=B2,MOD(A1:A100,1))
should work.
You are right that DATEVALUE requires text as its argument, and outputs an Excel serial date.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
ok, so I got the direct to a cell value to work with =B4.... but here is where i'm stuck now..... I enter the formula of and always get the result of the value in B

=MIN(INT(B2:B6)=C3,MOD(B2:B6,1))
and the result is always the value of the adjacent value in B

The Max formula
=MAX(INT(B2:B6)=C3,MOD(B2:B6,1))
always returns 12:00am

Below is a what i'm seeing
 <table> <tbody><tr> <td>
A1​
</td><td>
B​
</td><td>
C​
</td><td>
D​
</td><td>
E​
</td></tr><tr> <td>
2​
</td><td>
Time List​
</td><td>
Date​
</td><td>
Start​
</td><td>
End​
</td></tr><tr> <td>
3​
</td><td>
2/3/2009 3:29:53 PM​
</td><td>
2/3​
</td><td>
3:29:53 PM​
</td><td>
12:00:00 AM​
</td></tr><tr> <td>
4​
</td><td>
2/3/2009 3:34:21 PM​
</td><td>
2/3​
</td><td>
3:34:21 PM​
</td><td>
12:00:00 AM​
</td></tr><tr> <td>
5​
</td><td>
2/3/2009 10:15:49 PM​
</td><td>
2/3​
</td><td>
10:15:49 PM​
</td><td>
12:00:00 AM​
</td></tr><tr> <td>
6​
</td><td>
2/3/2009 10:17:26 PM​
</td><td>
2/3​
</td><td>
10:17:26 PM​
</td><td>
12:00:00 AM​
</td></tr></tbody></table>


Here are the formula view <table><tbody><tr> <td>
A1​
</td><td>
B​
</td><td>
C​
</td><td>
D​
</td><td>E </td></tr><tr> <td>
2​
</td><td>
Time List​
</td><td>
Date​
</td><td>
Start​
</td><td>End </td></tr><tr> <td>
3​
</td><td>
39847.6457523148​
</td><td>
39847​
</td><td>
=MIN(INT(B3:B6)=C3,MOD(B3:B6,1))​
</td><td>=MAX(INT(B3:B6)=C3,MOD(B3:B6,1)) </td></tr><tr> <td>
4​
</td><td>
39847.6488541667​
</td><td>
39847​
</td><td>
=MIN(INT(B3:B6)=C4,MOD(B3:B6,1))​
</td><td>=MAX(INT(B3:B6)=C4,MOD(B3:B6,1)) </td></tr><tr> <td>
5​
</td><td>
39847.927650463​
</td><td>
39847​
</td><td>
=MIN(INT(B3:B6)=C5,MOD(B3:B6,1))​
</td><td>=MAX(INT(B3:B6)=C5,MOD(B3:B6,1)) </td></tr><tr> <td>
6​
</td><td>
39847.9287731481​
</td><td>
39847​
</td><td>
=MIN(INT(B3:B6)=C6,MOD(B3:B6,1))​
</td><td>=MAX(INT(B3:B6)=C6,MOD(B3:B6,1))</td></tr></tbody></table>
 
Upvote 0
Are you entering it as a CSE formula, by pressing Ctrl-Shift-Enter (Cmd+Return for Mac)?

After you have confirmed it, is the formula inside curly braces { } ?
 
Upvote 0
this is looking like it may work - I will give it the full test tomorrow and let ya know

thanks for your help
 
Upvote 0
no dice with cse either.... here is what I have now

RESULTS
<table style="border-collapse: collapse; width: 501px; height: 1202px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 125pt;" width="167"> <col style="width: 43pt;" width="57"> <col style="width: 125pt;" width="167"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; width: 48pt;" width="64" height="17">A1</td> <td class="xl71" style="border-left: medium none; width: 125pt;" width="167">B</td> <td class="xl71" style="border-left: medium none; width: 43pt;" width="57">C</td> <td class="xl71" style="border-left: medium none; width: 125pt;" width="167">D</td> </tr> <tr style="height: 24pt;" height="32"> <td class="xl71" style="border-top: medium none; height: 24pt;" height="32">2</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Time List</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Date </td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">3</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:29 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">2/1</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">4</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:31 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">2/2</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">5</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:31 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">2/3</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">6</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 5:09:11 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">2/4</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">7</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">8</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">10:00:54 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">9</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">10</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl79" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">11</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:49 PM</td> <td class="xl79" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">12</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:52 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">13</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:52 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">14</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 5:06:03 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">15</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 5:06:09 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">16</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">17</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">18</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">19</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">20</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl80" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl81" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">21</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/4/2009 5:00:59 PM</td> <td class="xl80" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl81" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">22</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 5:01:02 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">23</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 5:01:02 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">24</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 5:19:22 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">25</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">26</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">27</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">28</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">29</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">30</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl80" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl81" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">31</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:54 AM</td> <td class="xl80" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl81" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">32</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:56 AM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">33</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:56 AM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">34</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">2/5/2009 2:30:22 PM</td> <td class="xl82" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl83" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

FORMULA VIEW

<table style="border-collapse: collapse; width: 543pt;" border="0" cellpadding="0" cellspacing="0" width="722"><col style="width: 47pt;" width="62"> <col style="width: 174pt;" width="232"> <col style="width: 71pt;" width="94"> <col style="width: 251pt;" width="334"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; width: 47pt;" width="62" height="17">A1</td> <td class="xl71" style="border-left: medium none; width: 174pt;" width="232">B</td> <td class="xl71" style="border-left: medium none; width: 71pt;" width="94">C</td> <td class="xl71" style="border-left: medium none; width: 251pt;" width="334">D</td> </tr> <tr style="height: 24pt;" height="32"> <td class="xl71" style="border-top: medium none; height: 24pt;" height="32">2</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Time List</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Date </td> <td class="xl72" style="border-top: medium none; border-left: medium none;">Time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">3</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">39846.7086655903</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">39845</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C3,MOD($B$3:$B$34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">4</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846.7086921296</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">39846</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C4,MOD($B$3:$B$34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">5</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846.7086921296</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">39847</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C5,MOD($B$3:$B$34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">6</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846.7147106481</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">39848</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C6,MOD($B$3:$B$34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">7</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846.9590972222</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C7,MOD($B$3:$B$34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="border-top: medium none; height: 12.75pt;" height="17">8</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846.9590972222</td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">=MIN(INT($B$3:$B$34)=C8,MOD($B$3:$B$34,1))</td> </tr> </tbody></table>

and they do have the squiggle { }

{=MIN(INT($B$3:$B$34)=C8,MOD($B$3:$B$34,1))}
 
Upvote 0
Untested, but try this CSE

=MAX((INT(B3:B6)=C3)*MOD(B3:B6,1))

If that doesn't work, I'll dig into it after work.
 
Upvote 0
Max is working, Min is not

RESULTS

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="457" height="1188"><col style="width: 23pt;" width="31"> <col style="width: 109pt;" width="145"> <col style="width: 28pt;" width="37"> <col style="width: 80pt;" width="107"> <col style="width: 80pt;" width="107"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; width: 23pt;" align="center" width="31" height="17">A1</td> <td class="xl78" style="border-left: medium none; width: 109pt;" align="center" width="145">B</td> <td class="xl78" style="border-left: medium none; width: 28pt;" align="center" width="37">C</td> <td class="xl78" style="border-left: medium none; width: 80pt;" align="center" width="107">D</td> <td class="xl79" style="width: 80pt;" align="center" width="107">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" align="center" height="17">2</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="center">Time List</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="center">Date </td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="center">Start</td> <td class="xl79" align="center">End</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">3</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:29 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;">2/1</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">12:00:00 AM</td> <td class="xl73" style="border-left: medium none;">12:00:00 AM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">4</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:31 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;">2/2</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">12:00:00 AM</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">11:01:06 PM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">5</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 5:00:31 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;">2/3</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">12:00:00 AM</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">11:00:20 PM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">6</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 5:09:11 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;">2/4</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">12:00:00 AM</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">11:00:30 PM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">7</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;">2/5</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">12:00:00 AM</td> <td class="xl73" style="border-top: medium none; border-left: medium none;">2:30:22 PM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">8</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl81" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">9</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">10</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/2/2009 11:01:06 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">11</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:49 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">12</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:52 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">13</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 5:01:52 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">14</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 5:06:03 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">15</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 5:06:09 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">16</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">17</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">18</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">19</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl72" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">20</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/3/2009 11:00:20 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">21</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 5:00:59 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">22</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 5:01:02 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">23</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 5:01:02 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">24</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 5:19:22 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">25</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">26</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">27</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">28</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">29</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">30</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/4/2009 11:00:30 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">31</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:54 AM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">32</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:56 AM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">33</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/5/2009 10:00:56 AM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="border-top: medium none; height: 12.75pt;" height="17">34</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">2/5/2009 2:30:22 PM</td> <td class="xl76" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74">
</td> </tr> </tbody></table>

FORMULA is cse { }

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="736" height="119"><col style="width: 47pt;" width="62"> <col style="width: 90pt;" width="120"> <col style="width: 56pt;" width="74"> <col style="width: 161pt;" width="214"> <col style="width: 162pt;" width="216"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl73" style="height: 12.75pt; width: 47pt;" width="62" height="17">A1</td> <td class="xl73" style="border-left: medium none; width: 90pt;" width="120">B</td> <td class="xl73" style="border-left: medium none; width: 56pt;" width="74">C</td> <td class="xl73" style="border-left: medium none; width: 161pt;" width="214">D</td> <td class="xl74" style="width: 162pt;" width="216">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">2</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">Time List</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">Date </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">Start</td> <td class="xl74">End</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">3</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">39846.7086655903</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39845</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MIN((INT(B3:B34)=C3)*MOD(B3:B34,1))</td> <td class="xl72" style="border-left: medium none;">=MAX((INT(B3:B34)=C3)*MOD(B3:B34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">4</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">39846.7086921296</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39846</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MIN((INT(B3:B34)=C4)*MOD(B3:B34,1))</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MAX((INT(B3:B34)=C4)*MOD(B3:B34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">5</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">39846.7086921296</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39847</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MIN((INT(B3:B34)=C5)*MOD(B3:B34,1))</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MAX((INT(B3:B34)=C5)*MOD(B3:B34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">6</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">39846.7147106481</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39848</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MIN((INT(B3:B34)=C6)*MOD(B3:B34,1))</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MAX((INT(B3:B34)=C6)*MOD(B3:B34,1))</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl73" style="border-top: medium none; height: 12.75pt;" height="17">7</td> <td class="xl71" style="border-top: medium none; border-left: medium none;">39846.9590972222</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">39849</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MIN((INT(B3:B34)=C7)*MOD(B3:B34,1))</td> <td class="xl72" style="border-top: medium none; border-left: medium none;">=MAX((INT(B3:B34)=C7)*MOD(B3:B34,1))</td> </tr> </tbody></table>
 
Upvote 0
This CSE formula should work for the MIN.
=MIN(IF(INT($B$3:$B$34)=C3,MOD($B$3:$B$34,1)))

Two, points:
1) if there is no data for a date (e.g. 2/1), then the formula will return 0.
2) if the formulas cell is formatted for time, that 0 will show as 12:00AM. One way around this is to use the custom format [=0]0;h:mm AM/PM
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
213
John_w
J
J
  • Question
Calculating time frames
Replies
2
Views
94
myall_blues
myall_blues
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
158
dilshod_k
D
A
  • Solved
Find Number smallest and largest between a group of numbers that are in a 2d range of cells.
Replies
5
Views
96
anilsharaf
A
B
  • Solved
Group number of purchased SKUs by counting customer
2
Replies
16
Views
231
shaowu459
shaowu459

Forum statistics

Threads
1,214,985
Messages
6,122,607
Members
449,090
Latest member
vivek chauhan

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