Sorting Formula Generated Dates in a Table Excel 2013

B

BrerRabbit

Board Regular
Joined
Aug 20, 2023
Messages
84
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
317
dshtesy
D
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
82
Peter_SSs
Peter_SSs
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
295
Skyybot
Skyybot
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E
M
  • Question Question
How to sum cells in a column that match an End of Month date in an adjacent column
Replies
7
Views
249
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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