In many companies, it is customary to celebrate the birthdays of employees or congratulate clients on their birthdays by offering birthday discounts. And then a problem arises: if the company has a significantly large number of employees / clients, then sorting their list by date of birth does not give a completely desirable result:
Since Microsoft Excel perceives any date as a numeric code (the number of days from the beginning of the century to the current date), the sorting is, in fact, according to this code. Thus, we get a list in the output in the order “old-young”, but from it it is not at all clear who has a birthday in which month.
Method 1: The TEXT function and an additional column
To solve the problem, we need one more auxiliary column with the function TEXT (TEXT), which can represent numbers and dates in a given format:
In our case, the format “MM DD” means that only two-digit month and day (without year) need to be displayed from the entire date.
Now simple sorting by auxiliary column (tab Data – Sorting) will give the desired result:
Voilà!
For completeness of sensations, you can add to the sorted list an automatic crossing of the months from each other with a horizontal line. To do this, select the entire list (except for the header) and select on the tab Home Command Conditional Formatting – Create Rule (Home — Conditional formatting — Create Rule). In the window that opens, select the lower rule type Use a formula to determine which cells to format and enter the following formula:
This formula checks the month number for each row, and if it differs from the month number on the next row, conditional formatting is triggered. Click the button Framework and turn on the bottom border of the cell in the tab Boundaries (Borders). Also, don’t forget to remove the extra dollar signs in the formula, because we need to pin only the columns in it.
After clicking on OK nice separating lines by months will be added to our table:
Method 2. Pivot table with grouping
This method, instead of additional columns and functions, uses Excel’s super-powerful tool – pivot tables. Highlight your list and tab Insert (Insert) click summary table (Pivot Table), and then OK in the window that appears. Drag the date field to the rows area – Excel will list all the dates in the first column on the sheet:
Right click on any date and select command Group (Group). In the next window, make sure the grouping step is selected Months and press OK. Get a list of all the months that are in the original table:
Now, to see which employees have a birthday in a particular month, drag the field with the employee’s name and drop it below the date field in the rows area:
The problem is solved, and no need to mess with the formulas. The only disadvantage of this option is that Excel cannot group columns with empty cells, i.e. you must have a column completely filled with dates in the source table.
Well, here you can go to collect money from colleagues for the next cake or buy gifts for your favorite customers 🙂
- Sorting tricks
- How Excel actually works with dates
- How to make a popup calendar in an excel sheet cell