Use a formula to sort?

[SamAnnElizabeth]

Hello all,

I'm new to this forum have have a question on sorting:

I have a table where there are columns with text in the format similar to the following:
="Birthday"&CHAR(10)&TEXT(DATE(2013,3,31),"MM/DD")

What I would like to do is to sort this data based on the date part of that function. I know I can extract it with the following:
=DATEVALUE(RIGHT(cellreference,4))

Is there a way I can sort my data based on this formula? Any help would be very much appreciated.

 

[RonB1111]

A little confused on how the date in your spreadsheet looks and what you're trying to accomplish.

This link explains how you can sort by formula:
Excel – Sort using array formulas | Get Digital Help
Not sure if your data to be sort is in a single column or multiple columns, so you'll need to select the appropriate option for your setup.

If the birthdates are in a single column, the easier way is to turn on Data-Autofilters and use the sort function in the drop down arrow.

If the above doesn't help, please show a sample of your data, and elaborate on the source of the data (ie: why does the ="Birthday......" formula have no cell references in it)

 

[RonB1111]

duplicate

 

[SamAnnElizabeth]

Ron,

I've included an example table of what I'm working with below. What I want to do is sort by dates so that Mike's name would come to the top because his anniversary is the next cronologically. I already have an extensive table done in this mannor. I don't think the array tables you showed me are going to do the trick exactly because that requires another field and I really want to re-arrange my rows automatically, instead of by hand like I'm doing now.

The =Birthday formula I demonstrated has no cell reference in it because it's for the cell itself. (ie. the formula for Sara/June is ="Birthday"&CHAR(10)&TEXT(DATE(2013,6,18),"MM/DD"))

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD][TABLE="width: 81"]
<TBODY>[TR]
[TD]April</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<TBODY>[TR]
[TD]May</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]June[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Sarah[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Birthday
06/18[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<TBODY>[TR]
[TD]John</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 103"]
<TBODY>[TR]
[TD]Birthday
05/12</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<TBODY>[TR]
[TD]Mike</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<TBODY>[TR]
[TD]Anniversary
04/01</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[RonB1111]

A few more questions:
1. Is there only 1 date per person, or could everybody have both a birthday and anniversary date (or even a 3rd or more dates)?
2. Assuming a person can have multiple dates, how do you enter them when a person has both a birthday and anniversary date in the same month.
3. What is in the worksheet? - Looks like Col A is names, Cols B-M is each month Jan, Feb ... Dec. - is there anything else?

 

[SamAnnElizabeth]

To respond to RonB1111's questions:

1. Everyone has several dates, up to eight of them. I really only want to sort on whichever one is coming up next.
2. If there are two events in one month, I have it entered like the following: ="Anniversary"&CHAR(10)&TEXT(DATE(2013,5,23),"mm/dd")&CHAR(10)&"Graduation"&CHAR(10)&TEXT(DATE(2013,5,29),"mm/dd") I did this so that the date would be formatted as a date, but if there is a better way to do this I'm totally willing.
3. There are actually several more columns in the actual worksheet- this is an abbreviated version for simplicity's sake. I have columns of address and email as well inserted between name and the 12 months of dates. However, I don't want to sort by these.

Any posibilities?

 

[RonB1111]

The biggest problem in a formula solution is the structure of your database and having multiple dates with other text in the same cell. Here are several options & I know they're not what you're looking for:

Opt 1. Redesign your database so you actually have 2 separate tables that could be linked by name (or a unique identifier). One table contains names, addresses, emails, etc with 1 row per name. The other table contains 3 columns: name, type of date (eg: birthday, anniversary, etc) and date. This date table would have multiple rows for each individual - one row per date type per individual. You'd be able to sort the dates exactly as you wish and also use a pivot table if you wanted to display them across monthly columns like your current structure.

Opt 2. Sort your current database with the current month as primary, next month as secondary - this won't be the exact order your looking for but it will be usable unless your list of names is very long.

Opt 3. The biggest obstacle in your current database is having multiple dates in the same cell. If there was only 1 date per cell, this formula solution would work:
a) Assume Jan-Dec is in Cols B thru M respectively with headers in row1 and data starting in row 2
b) Enter this formula in N2 and copy across to Y2 and down to the end of data: =IF(--RIGHT(B2,5)<=TODAY(),DATE(YEAR(TODAY())+1,LEFT(B2,2),RIGHT(B2,2)),--RIGHT(B2,5))
c) Enter this formula in Z2 and copy down to the end of data: =MIN(N2:Y2)
The formula in b) gives the date (m/d/y) that an occasion will next occur based on todays date. Eg: if a birthday is 3/28, the calculated date would be 3/28/2013; if a birthday was 3/1, the calculated date would be 3/1/2014.
The formula in c) gives the next date that individual has an occasion, so you can sort this column. The disadvantage is if the individual has a birthdate on 3/15 and anniversary on 3/16, only the first date will show.

One other suggestion - you said you're entering using this formula ="Birthday"&CHAR(10)&TEXT(DATE(2013,3,31),"MM/DD")
you could actually just type directly into the cell:
Birthday (then hit Alt-Enter together) 0331
The Alt-Enter keys hit together is CHAR(10) and it should display the same way in the cell - just might be an easier entry method than the formula.