How to calculate age with function?
The function formula of age is YEAR(TODAY())-YEAR(B2)& “Years”, in which the function formula of the imaginary year is YEAR(TODAY())-YEAR(B2)+1. age refers to the length of time that a person survives from the time of birth up to the time of computation, which is usually expressed by the number of years; moreover, the age is a kind of natural sign with biological basis. After the birth of a person, with the passage of time, age also increases, which is an irresistible law of nature.
Principle of the age function
Date year function YEAR syntax: YEAR (serial_number). Parameters, serial_number for the date of the year to be calculated. Application examples, “virtual age” calculation of the so-called “virtual age” is calculated from participation in the work, every year after a year to increase the age of one year, the use of YEAR function to calculate the age of the formula is “= YEAR (A1)-YEAR (B1)-YEAR (B1)-YEAR (B1)-YEAR (B1) YEAR (B1.
The formula A1 and B1 were stored in the starting and ending dates of age, YEAR (A1) and YEAR (B1) were calculated for the two dates corresponding to the year, after subtracting the virtual age.
2. Real age = between the two dates (birth, current date) = datedif (A1, today (), “y”) real age = total number of days – the year of birth and then divided by 365, and then rounded = ROUND ((TODAY () – A1) / 365,0) = ROUNDTODAY () – A1) / 365,0) = ROUNDTODAY () – A1)/365.25,0.
In EXCEL, know the year, month and day of birth, how to use the function to calculate the age ah?
In EXCEL, know the month and year of birth, you can use the Today function and YEAR function to calculate the age.
Method steps are as follows:
1, open the need to operate the EXCEL form, in any blank position above the function of the edit box enter “= YEAR (TODAY ()) – YEAR (A1)”, and press Enter.
2, select the cell after entering the formula, through the keyboard “Ctrl +1”, open the set cell format.
3, in the number of tabs, select “General” and click on the OK button below.
4, return to the EXCEL form, you can find that the specified month and year corresponding to the age has been successfully calculated.
How to use the function to calculate the age?
Method 1:
1, on the computer to open the example Excel table containing the ID number, in the cell to calculate the age of the input = YEAR (TODAY ()) – MID (A2,7,4) where A2 is the ID number of the cell. For example, if the identity card number where the cell is B3 then enter = YEAR (TODAY ()) – MID (B3,7,4).
2, enter the formula, press the keyboard Enter key can calculate the age of this one
Method two:
1, open the example EXCEL file, the table has four columns: ID number, name, date of birth, age. Date of birth and age need to be extracted and calculated by EXCLE, the 2 columns of the cell format for the “regular” format
2, the extraction of the date of birth need to use the function there are two functions, one is the DATE function, one is the MID function. DATE function of the structure of DATE (year, month, day). month, day), its role is to extract the numbers into date format for display. MID function structure for the MID (text,
start_num,
num_chars), its role is to extract the specified number of bits of characters from a string, which text is the need to extract the characters where the string, start _num is the starting position of the character to be extracted in the string, and num_chars is the number of bits of the character to be extracted.
3, in cell C3 enter the formula “= DATE (MID (A3, 7, 4), MID (A3, 11, 2), MID (A3, 13, 2))” and pull down,
which MID (A3, 7, 4) for the extraction of the year, MID (A3, 11, 2) for the extraction of the month, MID (A3, 11, 2) for the extraction of the month. 2) is used to extract the month and MID(A3,13,2) is used to extract the day.
4, do not need to be accurate to the date of the age calculation can be directly with the need to calculate the year minus the ID card number of the year to arrive. If you want to calculate the age of all people in 2016, in cell C3 enter the formula “=2016-
MID (A3,7,4)” can be.
5, need to be accurate to the date of the age of the calculation need to use the hidden DATEDIF function. The structure of the function DATEDIF (start_date, end_date, unit), which start_date is the starting date (in this case is the date of birth), end_date is the end date (in this case is the need to calculate the age of the specific year date), unit is the type of information to be returned. Such as to calculate the exact age of all people in June 1, 2016, in cell C3 enter the formula “= DATEDIF (C3,
“2016/6/1”, “y”)” can be.
excel table how to set the formula:
1, first open the excel worksheet, open after you can see in the “Start” menu bar there is a “Apply Format” button, click the button.
2, after clicking the drop-down selection, in the drop-down option to select the format to be used, which would like to use which click on which
3, after clicking the dialog box will pop up as shown in Figure, this time, select the use of good use of the form formatting of the region, click the button marked with a red box to select the region.
4, after selecting the region will appear as shown in the figure, if the regional selection is complete, then click just under the red box at the button.
5, after clicking on the picture will appear, this time you can see just select the region from where to where, after confirming that there is no error, click on the bottom of the “OK” button.
6, click the OK button will appear as shown in the figure, this time you can see that the format has just changed, has become a selection of the application of the table format.