Calculate Age in Google Sheet

Problem

Several times I have personally encountered this problem and I did not pay any attention to it since I do not really need an exact information. But weeks ago I needed to solve this same problem for an event which uses current age to find out if their applicants are qualified on or before the event's date.

Below is a table containing individuals with different birthdates. Let's say the date of the event is April 23, 2014. If one tries to calculate the age of the participants by merely subtracting the current year and the birth year, only those individuals which have already celebrated and are celebrating their birthdays will have the valid answer. Below, the higlighted cells are wrong since Lina, Paulo and Annerle's birthdays are beyond or after April 23.

Some details about this table: Columns A, B, C, D, E (Name, Birthday, Date, Current Age(Current Year - Birth Year), Current Age) and Rows 1, 2, 3, 4, 5, 6, 7 (Name, Bernadette, Luigi, Kathrene, Lina, Paulo, Annerle). So Name is in A1 and Bernadetted is in A2. The birthday of Annerle is in B7.

Name	Birthday	Date	Current Age (Current Year - Birth Year)	Current Age
Bernadette	1/5/2000	4/23/2014	14	14
Luigi	4/22/1990	4/23/2014	24	24
Kathrene	4/23/1990	4/23/2014	24	24
Lina	4/24/1990	4/23/2014	24	23
Paulo	8/5/1988	4/23/2014	26	25
Annerle	10/11/1987	4/23/2014	27	26

So how do we solve this? We need to implement relational operations (>, <, =, etc.) as well as use a nested if statement. Luckily, google sheet allows us to extract the date, month and year given a complete date (mm/dd/yyyy). The following functions were used to extract information from the dates given in the table:

1. month(mm/dd/yyyy)
2. year(mm/dd/yyyy)
3. day(mm/dd/yyyy)

We use the functions listed above together with the if statement to calculate the current age. The if statement has the following syntax: =IF ( test, then_true, otherwise_value ). It is just similar to the usual if(test,then,else) statement.

The Solution

If we are to calculate Lina's age then we need the following code:

=if(month(B5)>month(C5),(year(C5)-year(B5))-1,(if(month(B5)<month(C5),year(C5)-year(B5), (if(day(B5)<=DAY(C5), year(C5)-year(B5),((year(C5)-year(B5))-1))))))

Now let's dissect this small piece of code.

1st part:

=if(month(B5)>month(C5),(year(C5)-year(B5))-1,

We'd like to be sure that once the given birth month exceeds the event's month then the current year will not be counted in the calculation of the age. Since Paulo's birth month is August, which is greater than or after the month of April, then we make sure that we do not count the current year in the calculation. Thus, in the second argument of the if statement a year is subtracted.

2nd part:


(if(month(B5)<month(C5),year(C5)-year(B5),

In the previous part, we made sure that all individuals with birth months after April have their age calculated correctly. Now we'll go to those who spent their birthdays prior to the month of April. Notice that this if statement is already the else part of the first if statement. Since their birth months are prior to April, the age of each individual can then be calculated easily by subtracting the birth year from the current year (current year - birth year).

3rd part:

(if(day(B5)<=DAY(C5), year(C5)-year(B5),((year(C5)-year(B5))-1))))))

Now we'll go to the third and final part. We've already established the correct syntax for those whose birth months are before and after the month of April. What's left are those individuals whose birth month is precisely April. Notice that this third if statement is the else part of the second if statement. This means that since we've established the age of those who were born before and after April, this else part (3rd part) already refers to the month of April and all we have to do is to establish the operations on the DAY of birth. So if the day of birth is less than or equal to the event's day, then by simply subtracting the current year and year of birth (current year - birth year) we'll come up with the correct age. Else (which means if the day of birth is greater than the event's day), the age is simply calculated by using (current year - birth year)-1 since they still haven't celebrated their birthday yet.

Easier way to disable other dynamic views in blogspot and select only one

Google has given us a great option of how we want our blogspot blogs to appear with different selections of dynamic views. One flaw though is that despite selecting only one type of dynamic view template we still find a dropdown menu containing all the other dynalic views. This maybe okay for some, but for many who use dynamic views this seems to be an annoyance.
I have tried the method from southernspeakers.net and it worked on my other blog but for some reason after deciding to use Timeslide dynamic view, I cannot get rid of the Classic view. Luckily, I found an easier method to remove the dropdown menu containing the views from suresolution.blogspot.com while adding just a little CSS code which can also be found in southernspeakers.net's post on this topic.

The method:

Go to DASHBOARD and then navigate to TEMPLATE. Select the dynamic view that you want as default and you want to be maintained. Now, go back to the TEMPLATE option and click CUSTOMIZE then ADVANCED and then Add CSS. Add the following CSS code and dont forget to press enter after the code:

#views{
display: none !important;
}
 
#header #pages, #header #pages:before{
border-left: 0px !important;
}

Done! Enjoy your dynamic view!