get dates from a range in ascending order

dino4u86

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
On another sheet, I have dates in column A. In the new sheet, I need to first extract the earliest date from column A of that other sheet which is easy. just use the "Min" function for A column. So now the earliest date get picked and feeds into the cell of the new sheet in D1. How do I get the next earliest date after D1 from the range Column A of the other sheet and input that in D2. Basically picking the dates and sorting the dates in a ascending ordering through a formula is what I need. Thanks
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,181
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCD
1
215/03/202116/01/2021
315/09/202122/01/2021
429/03/202129/01/2021
509/06/202106/03/2021
627/07/202115/03/2021
706/03/202123/03/2021
829/01/202129/03/2021
912/08/202104/04/2021
1027/07/202115/04/2021
1104/04/202107/06/2021
1204/10/202109/06/2021
1316/01/202107/07/2021
1407/06/202127/07/2021
1515/04/202127/07/2021
1618/10/202104/08/2021
1722/01/202112/08/2021
1807/07/202115/09/2021
1904/08/202104/10/2021
2015/10/202115/10/2021
2123/03/202118/10/2021
22
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=AGGREGATE(15,6,$A$2:$A$21,ROWS(D$2:D2))
 

dino4u86

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
Thank for the help but this does not ignore the duplicate dates. sorry I should have mentioned earlier. Can you help with that?
 

Attachments

  • Date issue.PNG
    Date issue.PNG
    22.9 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,181
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff 1.xlsm
ABCD
1
215/03/202116/01/2021
315/09/202122/01/2021
429/03/202129/01/2021
509/06/202106/03/2021
627/07/202115/03/2021
706/03/202123/03/2021
829/01/202129/03/2021
912/08/202104/04/2021
1027/07/202115/04/2021
1104/04/202107/06/2021
1204/10/202109/06/2021
1316/01/202107/07/2021
1407/06/202127/07/2021
1515/04/202104/08/2021
1618/10/202112/08/2021
1722/01/202115/09/2021
1807/07/202104/10/2021
1904/08/202115/10/2021
2015/10/202118/10/2021
2123/03/2021 
22
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=IFERROR(AGGREGATE(15,6,$A$2:$A$21/(COUNTIFS(D$1:D1,$A$2:$A$21)=0),1),"")
 
Solution

dino4u86

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks a lot. Though this work I'm unable to update the cells by dragging the formulas down. I need to hit enter on every cell and it updates the cell with the result as expected. why is that?
 

dino4u86

New Member
Joined
Nov 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
Thanks a lot. Though this work I'm unable to update the cells by dragging the formulas down. I need to hit enter on every cell and it updates the cell with the result as expected. why is that?
Problem solved. thanks a lot for all your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,181
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,845
Messages
5,678,097
Members
419,742
Latest member
Dropzyl88

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
Top