Need formulae to get first and last date

M

Mathman

Board Regular
Joined
Jan 28, 2017
Messages
152
Office Version
  1. 2016
Platform
  1. Windows
Hi


What I need to get is the first date when a number appears in column M which is 9-Jan-17 (min date) and the second is the last date a number appears in column M which is 3-Feb-17 (max date) Also it must also fall between the dates that are in columns LL and LM.


Any idea how I get arrive to the answer?


Thank you
MM








[TABLE="width: 144"]
<tbody>[TR]
[TD="width: 70, bgcolor: transparent, align: left"]Column LL[/TD]
[TD="width: 74, bgcolor: transparent, align: left"]Column LM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Date[/TD]
[TD="bgcolor: transparent, align: left"]Date[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]>=40949[/TD]
[TD="bgcolor: transparent, align: left"]<=42776[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: left"]Column B[/TD]
[TD="bgcolor: transparent, align: left"]Column M[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3-Feb-17[/TD]
[TD="bgcolor: transparent, align: right"]1.69[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1-Feb-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]31-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]30-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]27-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]26-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]24-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]23-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]17-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9-Jan-17[/TD]
[TD="bgcolor: transparent, align: right"]1.67[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4-Jan-17[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3-Jan-17[/TD]
[/TR]
</tbody>[/TABLE]
​
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
If the dates will always be in newest to oldest then this should work


Excel 2010
BCDEFGHIJKLM
1min datemax date
21/12/20172/3/2017
3
4
52/10/2017
62/9/2017
72/8/2017
82/7/2017
92/6/2017
102/5/2017
112/4/2017
122/3/20171.67
132/2/2017
142/1/2017
151/31/20171.67
161/30/20171.67
171/29/2017
181/28/2017
191/27/2017
201/26/2017
211/25/2017
221/24/20171.67
231/23/20171.67
241/22/2017
251/21/2017
261/20/2017
271/19/20171.67
281/18/2017
291/17/20171.67
301/16/2017
311/15/2017
321/14/2017
331/13/2017
341/12/20171.67
351/11/2017
361/10/2017
371/9/2017
381/8/2017
391/7/2017
401/6/2017
411/5/2017
421/4/2017
431/3/2017
Sheet5
Cell Formulas
RangeFormula
D2{=INDEX(B5:B43,LARGE(IF(ISNUMBER(M5:M43),ROW(B5:B43)-ROW(B5)+1),1))}
E2{=INDEX(B5:B43,SMALL(IF(ISNUMBER(M5:M43),ROW(B5:B43)-ROW(B5)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Excel Workbook
BMNOPLLLM
1DateT2First9-Jan-17
210-Feb-17Last3-Feb-1710-Feb-1210-Feb-17
39-Feb-17
48-Feb-17
57-Feb-17
66-Feb-17
73-Feb-171.69
82-Feb-17
91-Feb-17
1031-Jan-171.68
1130-Jan-171.68
1227-Jan-17
1326-Jan-17
1425-Jan-17
1524-Jan-171.68
1623-Jan-171.68
1720-Jan-17
1819-Jan-171.68
1918-Jan-17
2017-Jan-171.67
2116-Jan-17
2213-Jan-17
2312-Jan-171.67
2411-Jan-17
2510-Jan-17
269-Jan-171.67
276-Jan-17
285-Jan-17
294-Jan-17
303-Jan-17
First & Last date
 
Last edited:
Upvote 0
[TABLE="width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 12px; margin-bottom: 0px;">[TR]
[TD]P1[/TD]
[TD]=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]=AGGREGATE(14,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)[/TD]
[/TR]
</tbody>[/TABLE]
These work great, Thank you very much for your time and help!!!

MM
 
Upvote 0
I'm curious and wanted to know something if possible. The formulae is dependent on the lowest date and the highest most current date. The current date will always be the first date listed so my question is can/how would the formulae look like if we took out the last section?


[TABLE="class: gmail-cms_table, width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TD]P1[/TD]
[TD]=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)




can we remove this section
*​
(B2:B30<=LM2)​
) and if yes what would it now look like? I've tried a few variations but can get it to return the same value. ​

​
Thanks ​
MM
[/TD]
[/TR]
</tbody>[/TABLE]
​
 
Upvote 0
Mathman said:
[TABLE="width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 12px; margin-bottom: 0px;">[TR]
[TD]P1[/TD]
[TD]=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]=AGGREGATE(14,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)[/TD]
[/TR]
</tbody>[/TABLE]
These work great, Thank you very much for your time and help!!!

MM
Click to expand...
Good news. You are welcome. :)


Mathman said:
I'm curious and wanted to know something if possible. The formulae is dependent on the lowest date and the highest most current date. The current date will always be the first date listed so my question is can/how would the formulae look like if we took out the last section?


[TABLE="class: gmail-cms_table, width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TD]P1[/TD]
[TD]=AGGREGATE(15,6,B2:B30/((M2:M30<>"")*(B2:B30>=LL2)*(B2:B30<=LM2)),1)




can we remove this section
*​
(B2:B30<=LM2)​
) and if yes what would it now look like? I've tried a few variations but can get it to return the same value. ​

​
Thanks ​
MM
[/TD]
[/TR]
</tbody>[/TABLE]
​
Click to expand...
I don't quite understand that. In my layout above, which cell(s) contain what you are describing as the "current date"?
 
Upvote 0
Hi Peter

I was able to work it out. What I realized after seeing your formulae was that I only needed to reference the 1 date and not both. So I changed the formulae to
=AGGREGATE(15,6,B2:B30/(​
(M2:M30<>"")​
*​
(B2:B30>=LL2)​
1) Since my dates are always in order and the data I want to use is 5 years from the most current date and your formulae references this date as => so no need to make the formulae any longer than needed.


I do have a question, I tried a few variations but I was unsuccessful. What if I used the "same" formulae
=AGGREGATE(15,6,B2:B30/(​
(M2:M30<>"")​
*​
(B2:B30>=LL2)​
,1)​
But rather than getting the Largest/smallest date that we instead get the average price in column M based on the date being => in cell LL2?

I currently use DAverage function and I curious if theirs a different way to get the same results.

Thanks again!
MM
​
 
Upvote 0
Mathman said:
I do have a question,...
... get the average price in column M based on the date being => in cell LL2?
Click to expand...
Is this what you mean?

=AVERAGEIF(B2:B30,">="&LL2,M2:M30)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
882
mst3kr
M
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
256
Alex Blakenburg
A
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
446
myall_blues
myall_blues
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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