This is how you create a birthday list in Excel


Sorting birth dates in Excel is not difficult, but knowing whose birthday is next month is of no use to the year of birth. And so it is still difficult to see who will have a birthday soon. How do you efficiently remove all birthdays from a long list so you know who can send you a birthday card next month? With a birthday list in Excel.

Step 1: Convert to text

To create a birthday list, you have to sort the birth dates by month. In Excel you can’t just sort dates by month with the regular sort function and ignore the year of birth at the same time. After all, with a birthday list you do not consider the year of birth. To work around this problem, create an extra column where the dates will be converted to text. For this you use the function in the extra column: = TEXT (A2, “mm-dd-yyyy”). In plain language, this means as much as ‘convert the date data into text in the order: month-day-year’. Apply this function to all cells of that extra column. If you now sort the data based on the newly created column, all birthdays will be ordered by month.

First, convert the date of birth to text and mm-dd-yyyy format.

Step 2: Birthday date

Now create a new column with the birthday date for the current year. In this example we will call that column Birthday date. You can record this date with the Excel function DATE. Apply the function to the cells containing the birth dates. In this example it looks like this: =DATE (“2021”, MONTH (B2), DAY (B2)). 2021 is simply the year in which your birthdays are celebrated. Now when you sort the list based on the column Birthday date the list of birthdays is ready.

Create a new column containing the day of this year’s birthday.

Step 3: Who’s birthday when?

When the list is very long, you can accidentally miss a birthday even in this list. You avoid this by filtering on who has a birthday this or next month. To do this, select all columns containing data and click on the tab data the button Filter. Now there will be filter buttons at the top of the columns. Then click on the filter button of the column Birthday date and tick the month for which you want to see the birthdays. click on OK to see only the birthday of that month. Via the same button you can uncheck the filter boxes again so that you can see the full list again.

By applying the filter, we only get to see the March birthdays.
.

Recent Articles

Related Stories