Sorting Birthdates Into Their Respective Months In Excel

Mon 24th October 2011

Shady deeds are afoot at Dodd Gee Enterprises. An email is being sent out to their entire customer base with a free horoscope reading that is intended to persuade the customer to buy a targeted product. In order to do this, the dates of birth of the entire customer list need to be separated into months. This article explains how this is done.

The marketing team at Dodd Gee Enterprises have come up with an inventive wheeze that they hope will fleece their entire clientele of a portion of their hard-earned cash. Their records contain information on each client, including dates of birth, and it is through this that the scam will operate.

To coincide with the launch of their new product, a soft toy called Benny the Big Orange Bear, they intend to email each of their clients a free horoscope reading. The mail begins,
'As you will be celebrating a birthday this month, we are pleased to offer you a free horoscope reading by our resident astrologer, Charlie Cosmos'. As this is a scam, the horoscopes are identical for each client, and these are emailed in batches at the start of each month to everyone on the list with a forthcoming birthday.

The horoscope informs the clients that their lives are about to change for the better. After the waffle, the following good luck signals are listed:

Lucky Letter: B
Lucky Colour: Orange
Lucky Animal: Bear

The marketing boys hope that this blatant manipulation will have their more superstitious clients queueing up to buy a product that ticks all of their good fortune boxes. In order to get the scam under way, they must first separate the client base into their respective birth months. With over a thousand clients on their books, all stored in an Excel worksheet, sorting them by the month of their birth will be a long, arduous task. At least it would have been were Excel not able to do it for them.

There are several ways to do this but the one I will demonstrate here is a straightforward procedure that involves formatting cells and then sorting data. Again you might like to enter a few random birthdates into a column to try this.

Select the column containing birthdates and click on Format. Choose Cells from the list to open up the Format Cells dialog box. Select the Number tab and choose Custom from the Category box. In the type box, type 'mmmm'. This is not an indication that your coffee tastes nice, but an instruction that you want your list to be sorted by month only. These letters should be lower case. Click OK and the contents of the column will now show only the month of birth of each client. You may be alarmed that the original birthdates have disappeared, but they are still there.

To sort the months into groups, select the entire list and click on Data and select Sort from the list. In the Sort By box, specify that you want to sort the data contained within column C, and then click the Options button. This will open up the Sort Options dialog box. From the First Key Sort Order drop-down list, specify that you want to sort by months by choosing January. February etc. Click OK on both the Sort Options and Sort dialog boxes. The list will now be sorted into months so that groups of clients with birthdays in the same month are grouped together.

After sorting the list into month of birth, the scammers above would make a copy to be used specifically for the horoscope scam. To restore the birthdates on the original sheet, select the column again and go to Format and select Cells. Select the Number tab and choose Custom from the Category box. In the type box, click the arrow and select dd/mm/yyyy from the list. Click OK and the birthdates will reappear.

Excel is an amazing application that seems to have a solution to every problem that is thrown at it. Getting to grips with its vast capacity for manipulating data, perhaps through a training course, would be adding a considerable string to your bow. Oh, and it's written in the stars that a scam involving fake horoscopes is doomed to fail.

Author is a freelance copywriter. For more information on excel training course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2020-sorting-birthdates-into-their-respective-months-in-excel.html