How old is born in 1966. How to calculate age by date of birth or work experience by date of employment in excel

Calculating a person's age is not an easy task, because age depends not only on the current year, but also on the current day. And you should also consider the additional complications associated with leap years.

In this article, I present three methods for calculating a person's age. These formulas assume that cell B1 contains the date of birth (for example, 2/16/1952), and cell B2 contains the current date (calculated using the function TODAY).

Method 1

Method 2

A more accurate way to calculate age is using the function LENGTH: \u003d INTEGER (LONGEVITY (B2, B1)). Function LENGTH usually used in financial calculations, but works great for calculating age as well. This function calculates the fraction of a year, represented by the number of full days between two dates. Using the function WHOLE allows you to get rid of the fractional part and get an integer.

Method 3

The third method for calculating age uses the function DATED... This undocumented feature is not documented in the Excel help system: \u003d DATEDAT (B1; B2; "Y").

If you like precision, use a different version: \u003d DATEDIF (B1; B2; "y") & "year," & DATEDIF (B1; B2; "ym" & "months," & DATEDIF (B1; B2; "md") &" day") . This function returns a text string, for example: 24 years, 2 months, 21 days.

Can different ways to calculate the age of a person. The table below shows common methods using the datetime function.

To use these examples in Excel, select the data you want in the table, right-click it, and select Copy... In the new sheet, right-click cell A1 and select Items Paste Options\u003e Keep Original Formatting.

additional information

Note: Disclaimer regarding machine translation... This article was translated using a computer system without human intervention. Microsoft offers these machine translations to help users who don't know of English language, read materials about Microsoft products, services, and technologies. Since the article was translated using machine translation, it may contain lexical, syntax and grammatical errors.

Calculate age in Excel you can by - differently: either only years, or years, months and days. Calculate age for today or on a specific date. There are several options.
First option.
How to count years, months and days of age in Excel.

How to set the current date, see the article "current date in Excel".
In cell C1 we write the formula:
\u003d DATEDIF (A1; B1; "y") & "y." & DATEDIF (A1; B1; "ym") & "month" & DATEDIF (A1; B1; "md") & "days"
Let me explain the formula:
DATEDIF is the name of the function.
y - counts years
ym - counts months
md - counts days
"Year", "month", "day" written between signs & - this is how years, months and days in the final date will be signed.
Happened.
How to calculate the seniority with this function so that years, months and days are recorded in different columns for further addition, see the article "Calculate seniority in Excel".
How to enter a cell address quickly, see the article "Addition, subtraction, multiplication, division in Excel".
If you need to calculate the age of a person on a certain date (not the current one), then write this date in cell B2.
Second option.
If enough count the number of full years, then we do this.
Cell A1 contains the date of birth.
Cell B2 contains the current date if we need to calculate the age to date, or if there is a date for which we need to calculate the age.
In cell D1, write the formula. We put "Equal", then we find the function "LENGTH". It is located on the "Formulas" -\u003e "Date and Time" tab. In the dialog box that appears, specify the addresses of the cells with dates.
In the line "Start_date" put the address of the cell with the date of birth.
In the line "End_date" we put the cell address with today's (current) date. It turned out like this.

In this window, you can already see the counting result (54.85).Click "OK". We got the following formula:
\u003d HARNESS (A1, B1)
It turned out to be 54 years old. And the digits after the decimal point (85) are not months. They need to be removed, rounded, but using a special formula. It's easy.In cell E1, set the "FLOOR" function.
Attention!
There is also the ROUNDDOWN function - it does not suit us now.
So, go to the "Formulas" tab -\u003e "Function library" -\u003e "Insert function". The "Function Wizard" window will appear.
In the "Category" section, select "Mathematical". In the window "Select a function" select "FLOOR". Click "OK".
In the dialog box in the "Number" line, specify the address of the cell with the number that we want to round.
In the line "Accuracy" put one.It turned out like this.


Click "OK". The result is already visible in this window (54).We got the following formula:\u003d FLOOR (D1,1)
All. The result is such a plate.
You can hide the column D and column B (for example, in the questionnaire).Right-click on the letter D (column address), select "Hide".
Third option.
How to calculate the number of full years in Excel can be counted like that.
Cell A1 contains the date of birth.
Cell B2 contains the current date if we need to calculate the age for today.
In cell C1 we write the formula:
\u003d DATEDIF (A1; B1; "y")
The format of the date cells must be "Date".Where to find this feature and how to install it, see the article “ Excel number... Format"

To calculate age or seniority, we will use undocumented excel function - DATED () (in the English version of Excel - DATEDIF ())

This function is described only in the English help, it cannot be called in a standard way, it is saved in Excel only for compatibility with older versions of Excel and Lotus 1-2-3

But despite this, if we write it in Excel, then it will work perfectly in all versions of Excel without the need to include or install anything additionally.

Function syntax DATED () with which we will calculate the age or seniority of a person as follows:

\u003d DATEDATE (start_date; end_date; measurement_method)

start_date - this is the starting date, if we calculate the age, then this is the date of birth, if we calculate the length of service, then this is the date of employment.

final date - this is the date on which we need to calculate this or that value. If we consider seniority and age, then, as a rule, you need to count on today's number. That is, what is the employee's experience from the beginning of the date of employment to the present day. Similarly for the date of birth. The current date can be inserted manually, but if you open the file the next day, today's date turns into yesterday, so you can use the function TODAY() which will automatically insert today's date whenever the file is updated. So you have work experience or age will be calculated automatically in Excel whenever you open the file.

Pay particular attention to the very last argument - measurement_method which defines the type and unit of measurement between the initial and end dates... All possible variations of this argument are listed below:

• "Y" date difference in full years
• "M" date difference in full months
• "D" date difference in full days
• "Yd" date difference in days from the beginning of the year excluding years
• "Md" date difference in days excluding months and years
• "Ym" date difference in full months excluding years

DATEDATE (A2; B2; "y")

In the English version of Excel \u003d DATEDIF (A2; B2; "y")

Or you can delete the second column and write the formula \u003d RAZDAT (A2; Today (); "y")

The most important thing is that at the same time date and month of birth will be taken into account, for example, if today it was 10.04.2014 (in the example in the picture), then the age would not be 31, but 30 years, since the birthday in 2014 has not yet arrived.

If you want to indicate work experience in full, for example “2 years 3 months 4 days", then the formula will look like this:

DAT (A2; B2; "y") & "d" & DAT (A2; B2; "ym") & "month" & DAT (A2; B2; "md") & "days"

in english version of Excel \u003d DATEDIF (A2; B2; "y") & "g." & DATEDIF (A2; B2; "ym") & "month." "& DATEDIF (A2; B2;" md ") &" days "

Here we first calculate the number of full years "y", then the number of full months excluding the years "ym" and the last is "Md" - the difference in dates excluding months and years. After that, all these and we get the desired result.

I hope the article helped you in solving your problems in Excel, thanks for the likes, join our groups in

We recommend reading

Windows instructions

Cough after and during meals: reasons for what to do

Windows instructions

Useful properties and contraindications for the use of barberry

Android instructions

A wen appeared on the eyelid: causes, negative consequences and methods of treatment

Windows instructions

Total protein: what is it and its norm in a blood test

Android instructions

Uterine cancer: first signs and symptoms, early treatment

Windows instructions

Causes of coughing while eating