Sorting Formula Generated Dates in a Table Excel 2013

B

BrerRabbit

Board Regular
Joined
Aug 20, 2023
Messages
66
Office Version
  1. 2021
  2. 2016
  3. 2013
Platform
  1. Windows
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)), "")
www.exceldemy.com

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 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​
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
1235.xlsm
ABCDEFGHIJKL
1Table 1Table 2
2
322/23103/07 APPLES03-07-2222/23103/07 APPLES03-07-22
4210/07 ORANGES10-07-2200210/07 ORANGES10-07-22
5320/07 CHERRIES20-07-2200417/07 STRAWBERRIES17-07-22
6417/07 STRAWBERRIES17-07-2200320/07 CHERRIES20-07-22
7531/07 ORANGES31-07-2200531/07 ORANGES31-07-22
Sheet17
Cell Formulas
RangeFormula
H3:L7H3=SORTBY(A3:E7,E3:E7,1)
Dynamic array formulas.
 
Upvote 0
Muhammad_Usman said:
1235.xlsm
ABCDEFGHIJKL
1Table 1Table 2
2
322/23103/07 APPLES03-07-2222/23103/07 APPLES03-07-22
4210/07 ORANGES10-07-2200210/07 ORANGES10-07-22
5320/07 CHERRIES20-07-2200417/07 STRAWBERRIES17-07-22
6417/07 STRAWBERRIES17-07-2200320/07 CHERRIES20-07-22
7531/07 ORANGES31-07-2200531/07 ORANGES31-07-22
Sheet17
Cell Formulas
RangeFormula
H3:L7H3=SORTBY(A3:E7,E3:E7,1)
Dynamic array formulas.
Click to expand...
Excel 2013 doesn't have any sort or filter formula
 
Upvote 0
1235.xlsm
ABCDEFGHIJK
1Table 1Table 2
2
322/23103/07 APPLES7/3/202222/23103/07 APPLES7/3/2022
4210/07 ORANGES7/10/202200210/07 ORANGES7/10/2022
5320/07 CHERRIES7/20/202200417/07 STRAWBERRIES7/17/2022
6417/07 STRAWBERRIES7/17/202200320/07 CHERRIES7/20/2022
7531/07 ORANGES7/31/202200531/07 ORANGES7/31/2022
Sheet17
Cell Formulas
RangeFormula
G3:K7G3=INDEX($A$3:$E$7, MATCH(SMALL($E$3:$E$7, ROW(G3)-2), $E$3:$E$7, 0), COLUMN(A3:E3))
Dynamic array formulas.
 
Upvote 0
Muhammad_Usman said:
1235.xlsm
ABCDEFGHIJK
1Table 1Table 2
2
322/23103/07 APPLES7/3/202222/23103/07 APPLES7/3/2022
4210/07 ORANGES7/10/202200210/07 ORANGES7/10/2022
5320/07 CHERRIES7/20/202200417/07 STRAWBERRIES7/17/2022
6417/07 STRAWBERRIES7/17/202200320/07 CHERRIES7/20/2022
7531/07 ORANGES7/31/202200531/07 ORANGES7/31/2022
Sheet17
Cell Formulas
RangeFormula
G3:K7G3=INDEX($A$3:$E$7, MATCH(SMALL($E$3:$E$7, ROW(G3)-2), $E$3:$E$7, 0), COLUMN(A3:E3))
Dynamic array formulas.
Click to expand...
can we talk this through:

This is a huge start. Thank you. This also handles duplicate dates, which is... AWESOME!!! May I trouble you one step further? Can I humbly request a single column instead of a dynamic array? Say just from teh date column?? I'm studying the formula now but am going a little slow. I've not had any experience with DAs. Pretty please? I need to be able to apply in other applications as well. I do understand multiple results though using match, index and offset, but in single columns rather than arrays.

I deeply appreciate both you responses and your absolute abundance of excel knowledge.
 
Upvote 0
BrerRabbit said:
can we talk this through:

This is a huge start. Thank you. This also handles duplicate dates, which is... AWESOME!!! May I trouble you one step further? Can I humbly request a single column instead of a dynamic array? Say just from teh date column?? I'm studying the formula now but am going a little slow. I've not had any experience with DAs. Pretty please? I need to be able to apply in other applications as well. I do understand multiple results though using match, index and offset, but in single columns rather than arrays.

I deeply appreciate both you responses and your absolute abundance of excel knowledge.
Click to expand...
Plus now the formula wont work.
 
Upvote 0
BrerRabbit said:
Can I humbly request a single column instead of a dynamic array? Say just from teh date column??
Click to expand...
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
ABCDEFGH
1/22/23103/07 APPLES3/07/20223/07/20223/07/2022
222210/07 ORANGES10/07/202210/07/202210/07/2022
3320/07 CHERRIES20/07/202220/07/202217/07/2022
4417/07 STRAWBERRIES17/07/202217/07/202220/07/2022
5531/07 ORANGES31/07/202231/07/202231/07/2022
6   
7   
8   
9   
10   
Sort Dates
Cell Formulas
RangeFormula
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
F1:F10F1=IF(D1="","",DATEVALUE(LEFT(D1,5)&"/"&A$2+(MID(D1,4,2)-6<0)))
H1:H10H1=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
ABCDEFGH
1/22/23103/07 APPLES3/07/202203/07 APPLES3/07/2022
222210/07 ORANGES10/07/202210/07 ORANGES10/07/2022
3320/07 CHERRIES20/07/202217/07 STRAWBERRIES17/07/2022
4417/07 STRAWBERRIES17/07/202220/07 CHERRIES20/07/2022
5531/07 ORANGES31/07/202231/07 ORANGES31/07/2022
6   
7   
8   
9   
10   
Sort Dates (2)
Cell Formulas
RangeFormula
G1:G10G1=IF(H1="","",INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$10)/(E$1:E$10=H1),COUNTIF(H$1:H1,H1))))
H1:H10H1=IFERROR(AGGREGATE(15,6,E$1:E$6,ROWS(H$1:H1)),"")
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
 
Last edited:
Upvote 0
Solution
Muhammad_Usman said:
1235.xlsm
ABCDEFGHIJK
1Table 1Table 2
2
322/23103/07 APPLES7/3/202222/23103/07 APPLES7/3/2022
4210/07 ORANGES7/10/202200210/07 ORANGES7/10/2022
5320/07 CHERRIES7/20/202200417/07 STRAWBERRIES7/17/2022
6417/07 STRAWBERRIES7/17/202200320/07 CHERRIES7/20/2022
7531/07 ORANGES7/31/202200531/07 ORANGES7/31/2022
Sheet17
Cell Formulas
RangeFormula
G3:K7G3=INDEX($A$3:$E$7, MATCH(SMALL($E$3:$E$7, ROW(G3)-2), $E$3:$E$7, 0), COLUMN(A3:E3))
Dynamic array formulas.
Click to expand...
THANK YOU. I'm yelling this from the roof tops. I'm deeply grateful. You are a steely eyed missile man ("The Martian" quote).
 
Upvote 0
Peter_SSs said:
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
ABCDEFGH
1/22/23103/07 APPLES3/07/20223/07/20223/07/2022
222210/07 ORANGES10/07/202210/07/202210/07/2022
3320/07 CHERRIES20/07/202220/07/202217/07/2022
4417/07 STRAWBERRIES17/07/202217/07/202220/07/2022
5531/07 ORANGES31/07/202231/07/202231/07/2022
6   
7   
8   
9   
10   
Sort Dates
Cell Formulas
RangeFormula
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
F1:F10F1=IF(D1="","",DATEVALUE(LEFT(D1,5)&"/"&A$2+(MID(D1,4,2)-6<0)))
H1:H10H1=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
ABCDEFGH
1/22/23103/07 APPLES3/07/202203/07 APPLES3/07/2022
222210/07 ORANGES10/07/202210/07 ORANGES10/07/2022
3320/07 CHERRIES20/07/202217/07 STRAWBERRIES17/07/2022
4417/07 STRAWBERRIES17/07/202220/07 CHERRIES20/07/2022
5531/07 ORANGES31/07/202231/07 ORANGES31/07/2022
6   
7   
8   
9   
10   
Sort Dates (2)
Cell Formulas
RangeFormula
G1:G10G1=IF(H1="","",INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$10)/(E$1:E$10=H1),COUNTIF(H$1:H1,H1))))
H1:H10H1=IFERROR(AGGREGATE(15,6,E$1:E$6,ROWS(H$1:H1)),"")
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
Click to expand...
I didn't even see this!! That works too!! Thank you.
 
Upvote 0
Peter_SSs said:
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
ABCDEFGH
1/22/23103/07 APPLES3/07/20223/07/20223/07/2022
222210/07 ORANGES10/07/202210/07/202210/07/2022
3320/07 CHERRIES20/07/202220/07/202217/07/2022
4417/07 STRAWBERRIES17/07/202217/07/202220/07/2022
5531/07 ORANGES31/07/202231/07/202231/07/2022
6   
7   
8   
9   
10   
Sort Dates
Cell Formulas
RangeFormula
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
F1:F10F1=IF(D1="","",DATEVALUE(LEFT(D1,5)&"/"&A$2+(MID(D1,4,2)-6<0)))
H1:H10H1=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
ABCDEFGH
1/22/23103/07 APPLES3/07/202203/07 APPLES3/07/2022
222210/07 ORANGES10/07/202210/07 ORANGES10/07/2022
3320/07 CHERRIES20/07/202217/07 STRAWBERRIES17/07/2022
4417/07 STRAWBERRIES17/07/202220/07 CHERRIES20/07/2022
5531/07 ORANGES31/07/202231/07 ORANGES31/07/2022
6   
7   
8   
9   
10   
Sort Dates (2)
Cell Formulas
RangeFormula
G1:G10G1=IF(H1="","",INDEX(D:D,AGGREGATE(15,6,ROW(D$1:D$10)/(E$1:E$10=H1),COUNTIF(H$1:H1,H1))))
H1:H10H1=IFERROR(AGGREGATE(15,6,E$1:E$6,ROWS(H$1:H1)),"")
E1:E10E1=IF(D1<>"",IF(NUMBERVALUE(MID(D1,4,2))>=7,DATEVALUE(LEFT(D1,5)&$A$1),DATEVALUE(LEFT(D1,5)&$B$1)),"")
Click to expand...
Would that work if there were two dates that were the same ie 10/07 Bananas?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to copy specific columns to other sheet in office 2021 without vba with some filter and format..
Replies
7
Views
104
Fluff
Fluff
P
  • Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
210
POLACOLOCO
P
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
413
Fluff
Fluff
P
  • Question
workday, but even more flexible
Replies
6
Views
314
KRice
K
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
319
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,069
Messages
6,122,954
Members
449,095
Latest member
nmaske

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