Sorting Excel data by by anniversary (i.e. date and month ignoring year)

OK, so you have an Excel file with the date of births (DOB) or any other kind of dates with day, month and year combination for many people and you want to sort them to find out the order of the anniversaries or birthdays. Sounds easy enough. Except when try to sort, it sort by DOB column and it sorts it chronologically – by year, then month and date (e.g. 30th July 1950 comes before 25th July 1990).

One way around this problem is to “standardize” the year, so that Excel effectively ignores the year. Here are more details.

  1. Make sure your dates are stored as Date type not text etc in Excel. (highlight the column and check format cells).
  2. Find out the birthdays (i.e. the year is now irrelevant) for all cases. You can do this in many ways. For example, by creating an adjacent column with the formula =DATE(2400,MONTH(A1),DAY(A1)) assuming that you stored your date of births in column A1. It does not matter if this column is formatted as either general, number or date.
  3. Sort the entire sheet using the birthdays column.

Notice that I have standardized all the years to year 2400 AD. Why? Because 2400 is a leap year and thus allows for 29th February. And why did I choose 2400 and not 2000 or any other leap year? Well, it is a simple precaution in case someone accidentally uses this as the true date of births!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s