Sorting Formula Generated Dates in a Table Excel 2013

[BrerRabbit]

I have a table that is populated solely by formula. One of those columns is dates generated by formula from another table. I've discovered that bcuz the date column is formula getting data from another table I can't sort it even though excel does recognise the date column as dates. Both tables are dynamic and are updated with more information added. So I need to account for blank rows as well.

Is there a index match offset countif type formula that I can use to put the dates in another table in chronological order?
I found this at ExcelDemy but couldn't make it work. I've found nothing else: =IFERROR(INDEX($D$5:$D$11, MATCH(ROWS($D$10:D10), COUNTIF($D$5:$D$11, "<="&$D$5:$D$11), 0)), "")

How to Sort Dates in Chronological Order in Excel (6 Easy Ways)

In this article, I have tried to explain 6 effective ways on how to sort dates in chronological order in Excel. I hope it'll be helpful.

www.exceldemy.com

The data looks like this. The date is generated using this formula: =IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")

/22	/23	1​	03/07 APPLES	03-07-22​
		2​	10/07 ORANGES	10-07-22​
		3​	20/07 CHERRIES	20-07-22​
		4​	17/07 STRAWBERRIES	17-07-22​
		5​	31/07 ORANGES	31-07-22​

I need the data in the new table to look like this:

/22	/23	1​	03/07 APPLES	03-07-22​
		2​	10/07 ORANGES	10-07-22​
		4​	17/07 STRAWBERRIES	17-07-22​
		3​	20/07 CHERRIES	20-07-22​
		5​	31/07 ORANGES	31-07-22​

 

[Peter_SSs]

Would that work if there were two dates that were the same ie 10/07 Bananas?

Why not give it a try?

I would also suggest that you try the same thing with the "roof tops" formula.

 

[BrerRabbit]

What about something like this (col H)?
I have also included a shorter way you might like to consider for the dates in the original table (col F).

23 11 04.xlsm
	A	B	C	D	E	F	G	H
1	/22	/23	1	03/07 APPLES	3/07/2022	3/07/2022		3/07/2022
2	22		2	10/07 ORANGES	10/07/2022	10/07/2022		10/07/2022
3			3	20/07 CHERRIES	20/07/2022	20/07/2022		17/07/2022
4			4	17/07 STRAWBERRIES	17/07/2022	17/07/2022		20/07/2022
5			5	31/07 ORANGES	31/07/2022	31/07/2022		31/07/2022
6
7
8
9
10
Sort Dates

Cell Formulas
Range	Formula
E1:E10	E1	=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
F1:F10	F1	=IF(D1="","",DATEVALUE(LEFT(D1,5)&"/"&A$2+(MID(D1,4,2)-6<0)))
H1:H10	H1	=IFERROR(AGGREGATE(15,6,E$1:E$6,ROWS(H$1:H1)),"")

If you did want to sort both columns of the table ..

23 11 04.xlsm
	A	B	C	D	E	F	G	H
1	/22	/23	1	03/07 APPLES	3/07/2022		03/07 APPLES	3/07/2022
2	22		2	10/07 ORANGES	10/07/2022		10/07 ORANGES	10/07/2022
3			3	20/07 CHERRIES	20/07/2022		17/07 STRAWBERRIES	17/07/2022
4			4	17/07 STRAWBERRIES	17/07/2022		20/07 CHERRIES	20/07/2022
5			5	31/07 ORANGES	31/07/2022		31/07 ORANGES	31/07/2022
6
7
8
9
10
Sort Dates (2)

Cell Formulas
Range	Formula
G1:G10	G1	=IF(H1="","",INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$10)/(E$1:E$10=H1),COUNTIF(H$1:H1,H1))))
H1:H10	H1	=IFERROR(AGGREGATE(15,6,E$1:E$6,ROWS(H$1:H1)),"")
E1:E10	E1	=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")

I bow before your greatness, you awesome Excel guru you.
I finally got to experiment with your formulae after the weekend not really expecting your formula to handle the duplicate issue. I was wrong. Dead wrong. You absolute legend.

BTW, thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
.. and for your kind words.