Sort dates by day and month only – MS Excel trick for HR people

The problem...

Usually, HR people run lists of the employees' birthdays and want it to be sorted so they can find out who's going to have a birthday in upcoming month.

So, imagine they have list with only 2 columns — the first column (A) is for name employee and the second one (B) holds employee's date of birthday.

Usually, the second column is of type "DateTime" and if you try to sort it, Excel will take the year into account and this will render the list useless (unless you want to find who's oldest or the youngest person on a payroll.

In the image above you can clearly see the problem. Given a list of hundred or so employees you will not be able to tell how many employees have a birthday in a given month without going trough the list manually.

The solution

In order to be able to sort by day and a month we will have to get rid of the year so the Excel could sort it properly.

In order to do this, we will need a helper column that uses the formula below:

=CONCATENATE(TEXT(MONTH(B2);"0");TEXT(DAY(B2);"0#"))

While this solution is okay, it is still not perfect. We can sort, but we cannot (re)use this column for other than sorting.

The solution... improved

Improved solution uses DATE function to recreate birthday date in the helper column:

=DATE(0;MONTH(B2);DAY(B2))

The trick is setting the year to zero. This makes all the dates to be in year 1900. Given the fact that all the dates have the same year, they can be sorted properly and still have the DateTime column format. This gives you the possibility to display day and a month using different format or culture. See the proof below:

In fact, you can use current year in the formula to build complete schedule of birthdays for current year and have it exported to your Sharepoint site so you have reminders set for you:

=DATE(YEAR(NOW());MONTH(B2);DAY(B2))

And the proof pic follows:

Pivot Table

How about counting the number of birthdays in a given month? Easy!

Add another column and use formula =MONTH(birthday) and use the table as a source data for the simple pivot table.

Any comments, suggestions, ideas? Please use the form below