Microsoft Excel is one of the most popular spreadsheet applications used around the world. Many people, whether professionals or students, use Excel for various purposes like creating financial statements, budgets, sales reports, and many other things. It is not only powerful but also versatile, offering various features that can be used to create complex calculations and data analysis. One of the most common calculations people often do in Excel is calculating age. In this article, we will show you 3 ways to calculate age in Excel using formulas that anyone can follow easily.
Cara Menghitung Umur di Excel Menggunakan Rumus-rumus Excel
The first way to calculate age in Excel is to use formulas. Excel provides various formulas that can be used to calculate age. Here are some of those formulas:
- Using DATEDIF function
- Using YEARFRAC and INT functions
- Using TODAY and YEAR functions
Using DATEDIF function
The DATEDIF function is used to calculate the difference between two dates. It is important to note that this function is not documented in Excel and, therefore, may not be available in future versions. The syntax of this function is like this:
=DATEDIF(start_date,end_date,unit)
The start date and end date should be in date format, and the unit argument can be one of the following:
- “y” – returns the number of complete years between the two dates
- “m” – returns the number of complete months between the two dates
- “d” – returns the number of days between the two dates
- “ym” – returns the number of months between the two dates, ignoring years
- “yd” – returns the number of days between the two dates, ignoring years
- “md” – returns the number of days between the two dates, ignoring years and months
Let’s say we have a list of birth dates in column A, and we want to calculate the age in column B. We can use the DATEDIF function like this:
=DATEDIF(A2,TODAY(),"y")
This formula calculates the difference between the birth date (A2) and today’s date (TODAY()), in years. The TODAY() function is used to get the current date, which is necessary since the age changes every day. We can copy this formula down to all rows to calculate the age for all individuals.
Using YEARFRAC and INT functions
The YEARFRAC function returns the fractional years between two dates. The syntax of this function is like this:
=YEARFRAC(start_date,end_date)
This function returns a decimal value, which represents the fraction of a year between two dates. We can then use the INT function to round down the decimal value to get the whole number of years. The syntax of the INT function is like this:
=INT(number)
Let’s say we have a list of birth dates in column A, and we want to calculate the age in column B. We can use the YEARFRAC and INT functions like this:
=INT(YEARFRAC(A2,TODAY()))
This formula calculates the fractional years between the birth date (A2) and today’s date (TODAY()), and then rounds down the result to get the whole number of years. We can copy this formula down to all rows to calculate the age for all individuals.
Using TODAY and YEAR functions
The TODAY function returns the current date. The syntax of this function is like this:
=TODAY()
The YEAR function returns the year of a date. The syntax of this function is like this:
=YEAR(date)
Let’s say we have a list of birth dates in column A, and we want to calculate the age in column B. We can use the TODAY and YEAR functions like this:
=YEAR(TODAY())-YEAR(A2)
This formula subtracts the year of the birth date (YEAR(A2)) from the current year (YEAR(TODAY())) to get the age. We can copy this formula down to all rows to calculate the age for all individuals.
Rumus Excel Untuk Menghitung Umur Pada Microsoft Excel
The second way to calculate age in Excel is by using the formula. To do this, you need to know the birth date of a person and the current date. You can then use the YEARFRAC and INT functions to calculate the age. Here’s an example of how this can be done:
=INT(YEARFRAC(B1,TODAY()))
In this formula, B1 represents the birth date, and TODAY() represents the current date. The YEARFRAC function calculates the fraction of a year between two dates, and the INT function rounds down the result to get the whole number of years. You can copy this formula down to all rows to calculate the age for all individuals.
Rumus Excel Menjumlahkan Data Dengan Kriteria
Another useful formula in Excel is the SUMIF function, which can be used to sum a range of cells that meet certain criteria. The syntax of this function is like this:
=SUMIF(range, criteria, [sum_range])
The range argument is the range of cells that you want to check against the criteria. The criteria argument is the condition that you want to apply to the cells. The sum_range argument is the range of cells that you want to sum, but it is optional. If you omit this argument, the function will use the range argument as the sum_range argument.
Here’s an example of how to use the SUMIF function:
In this example, we have a list of ages in column A and their corresponding names in column B. We want to sum the ages of people whose names contain the letter “A”. To do this, we can use the following formula:
=SUMIF(B2:B6,"*A*",A2:A6)
This formula sums the ages in A2:A6 of the cells in B2:B6 that contain the letter “A”. The asterisk (*) is a wildcard character that matches any character, so “*A*” matches any text that contains the letter “A”. We can use this formula to sum the ages of people whose names contain any other letter or combination of letters.
Cara Membuat Rumus Menghitung Umur Di Excel Hongkoong
The third way to calculate age in Excel is to use Hongkoong formula. This formula is similar to the second method, but it uses the TODAY function twice instead of the YEAR function. Here’s an example of how to use this formula:
=INT((TODAY()-B1)/365)
In this formula, B1 represents the birth date, and TODAY() represents the current date. The formula subtracts the birth date from the current date to get the number of days between them, and then divides this number by 365 to get the number of years. The INT function rounds down the result to get the whole number of years.
FAQ
What is the difference between DATEDIF and YEARFRAC functions?
The DATEDIF and YEARFRAC functions are both used to calculate the difference between two dates. However, the DATEDIF function is not documented in Excel, and therefore may not be available in future versions. The YEARFRAC function, on the other hand, is a documented function and is available in all versions of Excel. The YEARFRAC function also returns a decimal value, which represents the fractional years between two dates, while the DATEDIF function returns a whole number result based on the unit argument.
Can I use these formulas to calculate age in months or days?
Yes, you can use the DATEDIF function to calculate age in months and days. To calculate age in months, use the “m” unit argument in the DATEDIF function. To calculate age in days, use the “d” unit argument in the DATEDIF function. For example, to calculate age in months:
=DATEDIF(A2,TODAY(),"m")
This formula calculates the difference between the birth date (A2) and today’s date (TODAY()), in months. To calculate age in days:
=DATEDIF(A2,TODAY(),"d")
This formula calculates the difference between the birth date (A2) and today’s date (TODAY()), in days.
Video Tutorial
For those who prefer video tutorials, here is a Youtube video that explains how to calculate age in Excel:
