13 August 2018

HOW TO: Calculate Age and Sort Monthly for the Birthdays using Microsoft Excel

Sometimes, you need to sort out a list of people from youngest to oldest and categorically arrange by month with the given birthdate using Microsoft Excel to increase productivity and easier access.

This could be tough and complicated but somehow it kinda helps you play some functions.

Some EXCEL functions used:
  • The DATEDIF function is useful in formulas where you need to calculate an age.
  • The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
  • The NUMBERVALUE function is useful when you convert text to a number, in a locale-independent way.
  • The TEXT function is useful when you change the way a number appears by applying to format to it with format codes.

HOW TO: Calculate Age
  • Using Function =DATEDIF()
    • Syntax: =DATEDIF(start_date,end_date,unit)
    • Sample: =DATEDIF(E4, NOW(),"y") where E4 is the birthdate field (m/d/yyyy), "y" to fetch and result for year.
1. First, you need to add some header titles on top row or wherever you want to add. Here I put it on the first row with the following header titles like:
  • #
  • Lastname
  • First name
  • Birthdate
  • Status
  • Age
  • Sex
  • MM
Since we are getting the age the most important here is just the birthdate and age cell.
Important: I usually add # or No. on headers for me to easily sort on the later part of the project.

2. Then I used some dummy information for calculating the Age. It may look like this.


3. The highlighted row and column will be used to our Function
  • =DATEDIF(start_date,end_date,unit)
start_date = D2 and D3 (hightlighted cell) ; should be mm/dd/yyyy;
end_date = now() function ; we will compare D2 and D3 to this moment.
unit = we will be used "y" for the year.


4. So in the AGE cell (especially D2 - Juan de la Cruz), we will put the formula in that cell. Notice that I put =DATEDIF(D2, NOW(),"y") in D2 and D3, and you will see the age result.
  • If you differentiate 1965 (January 5) and 2018(August 13), you will get  53.
  • If you differentiate 1970 (April 25) and 2018(August 13), you will get  48

HOW TO: Sort Birthdays According to Month

If you want to know how many birthday celebrants on a certain month, or the next following month, this could be useful tip and function. Here, I added three(3) examples to see if it works well.


Notices: 
  1. I added 3 persons namely:  Victor, Elen, and Juan Luna.
  2. MM Header title (it should be the container for sorting for the monthly)
  3. # very useful in the sorting process. Once you want to get the original arrangement, you can always sort it according to #.
1. Now, we add a text =NUMBERVALUE(TEXT())
  • =NUMBERVALUE(TEXT(D2,"MMDD")) where E3 is the cell or the birthdate
  • MMDD will fetch month and day.

TIP: Drag the corner to copy the formula(D2, D3, D4, D5, D6) without editing the cell interactively. See below.


2. Select the title header then click Sort and Filter as shown below.


Sort and Filter help you sort things on those same headers (that's why I always put No. field).

3. Click Filter, you will notice there is an arrow on the cell.


4. Choose MM cell, then click the arrow and you will see sort smallest to largest or largest to smallest. You can try the two and it will sort.


5. This is the result of the Sort Smallest to Largest


Notice the order: the first is January 5, then January 10, then April 15, April 25, and last May 5 in order.

FINAL THOUGHTS
To recap, I used these EXCEL functions:
  • The DATEDIF function is useful in formulas where you need to calculate an age.
  • The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
  • The NUMBERVALUE function is useful when you convert text to a number, in a locale-independent way.
  • The TEXT function is useful when you change the way a number appears by applying to format to it with format codes.
Useful Excel Function: Convert Numbers into Words

I hope you will find it useful especially creating a master list with birthdate. Feel free to comment below. Thank You!
Advertisement


Featured Offers:
Advertisement