## Excel에서 생년월일을 나이로 빠르게 변환하는 방법은 무엇입니까?

예를 들어 Excel에서 다양한 생년월일 데이터를 가져오고 이러한 생년월일을 Excel에 정확한 나이 값을 표시하도록 변환해야합니다. 어떻게 알아 내시겠습니까? 이 기사에는 Excel에서 생년월일을 쉽게 나이로 변환하는 몇 가지 팁이 나와 있습니다.

**방법 A :**

수식을 사용하여 생년월일을 나이로 변환

**수식을 사용하여 생년월일을 나이로 변환**

아래 수식은 Excel에서 생년월일을 기준으로 나이를 계산하는 데 도움이 될 수 있습니다.

**INT 함수를 사용하여 생년월일을 나이로 변환**

INT 함수는 주어진 생년월일을 기준으로 한 사람의 나이를 계산하는 데 도움이 될 수 있습니다. 다음과 같이하십시오.

1. 생년월일과 현재 날짜를 별도로 포함하는 두 개의 열이 있다고 가정합니다.

2. 나이를 출력 할 빈 셀을 선택하고 아래 수식을 입력 한 후 **엔터 버튼** 키. 결과 셀을 선택한 다음 드래그하여** 채우기 핸들** 모든 결과를 얻을 수 있습니다.

**=INT((B2-A2)/365)**

**DATEDIF 함수를 사용하여 생년월일을 나이로 변환**

아래 DATEDIF 함수도 도움이 될 수 있습니다.

나이를 출력 할 빈 셀을 선택하고 아래 수식을 입력하고 **엔터 버튼** 키. 결과 셀을 선택한 다음 드래그하여 **채우기 핸들** 모든 결과를 얻을 수 있습니다.

**=DATEDIF(A2,NOW(),"y")**

##### ROUNDDOWN 함수를 사용하여 생년월일을 나이로 변환

ROUNDDOWN 함수를 사용하여 다음과 같이 생일을 기준으로 나이를 계산합니다.

나이를 출력 할 빈 셀을 선택하고 아래 수식을 입력하고 **엔터 버튼** 키. 결과 셀을 선택한 다음 드래그하여 **채우기 핸들** 모든 결과를 얻을 수 있습니다.

**=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)**

**DATEDIF 함수를 사용하여 연령을 년 + 월 + 일 형식으로 표시**

연령을 년 + 월 + 일 형식으로 표시하려면 아래 DATEDIF 기능을 사용해보십시오.

나이를 출력 할 빈 셀을 선택하고 아래 수식을 입력하고 **엔터 버튼** 키. 결과 셀을 선택한 다음 드래그하여 **채우기 핸들** 모든 결과를 얻을 수 있습니다.

**=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"**

**생년월일을 나이로 쉽게 변환 공식을 기억하지 않고**

공식을 기억하지 않고도 생년월일을 나이로 쉽게 변환 할 수 있습니다. **날짜 및 시간 도우미** of **Exce 용 Kutools**l.

신청하기 전에** Excel 용 Kutools**, 제발 **먼저 다운로드하여 설치하십시오.**.

1. 나이를 출력 할 빈 셀을 선택합니다. 그런 다음 **쿠툴** > **수식 도우미** > **날짜 및 시간 도우미**.

2. 에서** 날짜 및 시간 도우미** 대화 상자에서 다음과 같이 구성하십시오.

- 2.1)로 이동
**연령**탭; - 2.2)에서
**생년월일**상자에서 나이로 변환 할 생년월일이 포함 된 셀을 선택합니다. - 2.3) 선택
**오늘**에서 옵션**에**섹션; - 2.4) 다음에서 결과 유형을 지정합니다.
**출력 결과 유형**드롭 다운 목록; - 2.5) 클릭
**OK**버튼을 클릭합니다.

그런 다음 선택한 셀에 연령이 채워집니다. 결과 셀을 선택한 다음 채우기 핸들을 끝까지 끌어 모든 연령을 가져옵니다.

**주의 사항**: 연령을 년 + 월 + 일 형식으로 표시하려면 선택하세요.** 년 + 월 + 일** 인사말 **출력 결과 유형** 드롭 다운 목록. 결과는 아래 스크린 샷과 같이 표시됩니다. 또한 필요에 따라 나이를 월, 주 또는 일로 표시하도록 지원합니다.

이 유틸리티의 무료 평가판(30일)을 사용하려면 **그것을 다운로드하려면 클릭하십시오**을 클릭 한 다음 위 단계에 따라 작업 적용으로 이동합니다.

**관련 기사:**

### 최고의 사무 생산성 도구

#### Excel 용 Kutools는 대부분의 문제를 해결하고 생산성을 80% 향상시킵니다.

**재사용**: 빠르게 삽입**복잡한 공식, 차트**그리고 이전에 사용한 모든 것; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...- 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
- 셀 / 행 / 열 병합 데이터 손실없이; 셀 내용 분할; 중복 행 / 열 결합... 중복 셀 방지; 범위 비교...
- 중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
- 정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
- 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
- 슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
- 통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
- 300개 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업 또는 조직에 쉽게 배포 할 수 있습니다. 전체 기능 30 일 무료 평가판. 60 일 환불 보장.

#### Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

**Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용**, Publisher, Access, Visio 및 Project.- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
- 생산성이 50% 증가하고 매일 수백 번의 마우스 클릭이 줄어듭니다!

###### You are guest

###### or post as a guest, but your post won't be published automatically.

- To post as a guest, your comment is unpublished.· 1 years agoHow to convert age to date of birth
- To post as a guest, your comment is unpublished.Hi Catherine,

thank you so much for your formula! I am a related service provider for many students with various ages in a school setting. thanks to your formula, the student's age is in front of me during each session, and I am able to adjust session goals appropriately. - To post as a guest, your comment is unpublished.thanks you so much very very good formula
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Do not show the Detedif Formula in my Excel

What to do Know? - To post as a guest, your comment is unpublished.if I have their id number how to get their age example their id no consist first 6digit is date of birth

example 830901056252 , 830901 is date of birth.

how to take calculate their age- To post as a guest, your comment is unpublished.

- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.please do i calculate the birth date from age
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Thank you Catherine,

for the whole-hearted appreciation of my solution! - To post as a guest, your comment is unpublished.I have used the following to calculate the age of children in my class.

=DATEDIF(B4,TODAY(),"Y")&"."&DATEDIF(B4,TODAY(),"YM")

with B4 being their date of birth. It produces the correct answer (eg) 9.11

They take tests 3 months later so I need in another cell to calculate '+3' months. But each time I do this I get 9.14 when I need it to say 10.2

Can anyone help.

Thanks- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Unclear my post went through or not, in response to Catherine's query.

Repeat my formula which finds the difference between cell B4 contents and a date TODAY() advanced by 3 months:

=DATEDIF(B4,DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"Y")&"."&DATEDIF(B4,DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"YM") - To post as a guest, your comment is unpublished.The DATEDIF formula used to give the Years and Months in a YY.MM format is understandable. However, each of the 2 components of this formula is a separate number, integer. So, adding 3 to the MM part will only give 14 if it is 11 before the addition. The formula does not know you are looking to set it up as a MONTH.

So, if you get the DATEDIF between B4, and a date which is a valid date but 3 months after TODAY(), try the following formula, works for me and gave 10.2:

=DATEDIF(B4,DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"Y")&"."&DATEDIF(B4,DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),"YM")

Please confirm it worked for you.

- To post as a guest, your comment is unpublished.Hi! how to calculate running out date (ROD), if i have a date of birth(DOB). i want that, if enter DOB the ROD can display automatically. i.e i was born in 23 Jan 1998 i need to display automatically ROD. Please help me
- To post as a guest, your comment is unpublished.How to calculate the retied date, if have date of birth(DOB)? i want to display automatic once enter DOB, the retied date display
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.One should be careful using Method C, with the YEARFRAC() function, because of rounding error in computations involving leap years. You will calculate the incorrect age for people born in a leap year.

Example:

John Smith was born on 6/5/1932. 1932 is a leap year. Compute John Smith's age on 6/5/2002. We would say John Smith is 70 years old on his 70th birthday, 6/5/2002. However, because YEARFRAC(DATE(1932,6,5),DATE(2002,6,5), 1) computes to 69.99795627, adding ROUNDDOWN computes to 69. In fact, John is 70. I'm not sure if this is the best solution, but I've added a precise day to the computation, which I assume shouldn't affect other calculations because it is shorter than the formula assumes a day to be.

(1/365.2422) is a precise day when accounting for leap years

My adjusted YEARFRAC is:

YEARFRAC(DATE(1932,6,5),DATE(2002,6,5), 1)+(1/365.2422) which computes to 70.00069418 and when combined with ROUNDDOWN, gives 70.

Putting it all together according to the references in the article:

=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1)+(1/365.2422), 0) - To post as a guest, your comment is unpublished.Correction to what I just posted, the data is sitting in Cells B2:C13 (not A1:B12)
- To post as a guest, your comment is unpublished.What formula can I use to obtain the invoice amount for a specific month from a list of months with balances.
- To post as a guest, your comment is unpublished.Month Invoice Amount Assume cells A1 through B12 contain the 12 month

Jan 810 names and amounts to invoive as at left.

Feb 1200 For month Jan

Mar 850 Invoie Amount 810

Apr 930

May 1250 The formula entered in the cell above here

Jun 1300 framed above is

Jul 1100

Aug 820 =VLOOKUP(G3,$B$2:$C$13,2,FALSE)

Sep 750

Oct 875

Nov 980

Dec 1450

Cedric, please confirm this answers your query

- To post as a guest, your comment is unpublished.Thanks a lot for the formula. I got the desired result. Many thanks to the thread poster.

Regards,

KT - To post as a guest, your comment is unpublished.6/4/1990 in A2

=TEXT(TODAY()-A2,"YY")&" Years, "&TEXT(TODAY()-A2,"mm")&" Months, "&TEXT(TODAY()-A2,"dd")&" Days"

use this simple formula and get answer as below :

26 Years, 03 Months, 15 Days - To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.This function is great. Very easy to understand. Thanks!
- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Responding to Beloved 2016-03-04:2016-03-04 in my yahoo mail box.Somehow, I thought they were not directed at me as I don't seem to have given details with commas, spaces, etc. I had only suggested the use of YEARFRAC() instead of DATEDIF function which i wasn't getting on my Excel. A clarification is welcome.
- To post as a guest, your comment is unpublished.Your teachings COULD be helpful but they are DEFINITELY NOT! Not because they are wrong per se but simply because 1. why do you use comma's (,) when Excel only accepts semi-colons (;) really beats me? 2. why do you put spaces in your examples when Excel NEVER accepts spaces?? Your work could be helpful but it confuses people instead. So, a perfectly good answer fails to produce the desired result practically on Excel because of YOUR carelessness. You spoil your own good work!...
- To post as a guest, your comment is unpublished.It is amazing to understand and very very useful.
- To post as a guest, your comment is unpublished.I couldn't find DATEDIF() in my Excel but YEARFRAC() did it. Hope it is reliable, any comment anyone?
- To post as a guest, your comment is unpublished.In my Excel, I could not get the DATEDIF() function for whatever reason. Yet, I got a YEARFRAC() function which seems to do the same. Hope it is reliable.

Any comment anyone? - To post as a guest, your comment is unpublished.how to convert date of birth in to worlds

02/02/1966

second february nineteen sixty six- To post as a guest, your comment is unpublished.I have a feeling date functions may do this in later versions of Excel i.e. =month() etc. If not, you'll need to create a table with the number in the first column i.e. 1-12 and months in the 2nd column Jan-Dec) then use vlookups so that the function can convert the numbers into the correct text. A lot of fun to be had there as I once wrote a sheet that did this very thing, there was further issues involved in using the correct affix i.e. nd rd st or th. Again, lookup tables did that and I was able to shorten the funtions calculations by using if statements instead but that was very fiddly and half the time I found myself trying to use PHP which would do the job in no time! Have a look around online as someone's likely done some VBA that'll do the trick or KUTools probably have something.

- To post as a guest, your comment is unpublished.I have about 100 cells with the calculated age as you done.

Is there any easy way to make groupings on all that are the age of 10 etc.

I have automated the document as we will have more people in all the time so I need automated groupings as well.

Any suggestions?

/Jacob - To post as a guest, your comment is unpublished.Hello

Retirement age is 60 years and last date of each month and the formula works, but in our case if an employee's date of birth falls on 1st of any month then he/she will retire on the previous months last date. (eg. employee 1 DOB is 03/01/1960 then output needed is 28-02-2020 and employee 2 DOB is 03/02/1960 then 31-03-2020) - To post as a guest, your comment is unpublished.Brilliant tips!

Really worked well! If you want the age to display as a single number on Method B - just format the cell to 'Number' with no decimal points - it works!! Hurrah!! - To post as a guest, your comment is unpublished.Literally none of these worked. When I subtract, it just gives me another date like 03/03/1953
- To post as a guest, your comment is unpublished.Channing,

Can you provide cell references and what data is entered in them along with your formula and that cell reference?

First thought is you should format your result as a number instead of Date. That might be your issue.

- To post as a guest, your comment is unpublished.Another question in regards to age. How can I get the age in cell B2 [age?] when I have in Cell A2 [26/06/1966] and in Cell B1 [2013] just the year. Thanks.
- To post as a guest, your comment is unpublished.Are you just trying to get the age they will be in the year of B1? If so, then just do B2 =(B1-(YEAR(A2)))

If that's not what you are looking for, can you explain what it is you need in more detail? - To post as a guest, your comment is unpublished.

- To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Here is one i have that accounts for leap days (well it excludes them pretty much)

=IF((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),MONTH(C3),DAY(C3))),C2,"d"))=365,(DATEDIF(C3,C2,"y"))+((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),(MONTH(C3)),(DAY(C3)))),C2,"d")-1)/365),(DATEDIF(C3,C2,"y"))+((DATEDIF((DATE((YEAR(C3)+(DATEDIF(C3,C2,"y"))),(MONTH(C3)),(DAY(C3)))),C2,"d"))/365))

C2 = Current Date - could use TODAY()

C3 = DOB - To post as a guest, your comment is unpublished.Not sure if this was covered, but a lot of people right these and don't consider leap years when looking at the difference in days. Here is one I wrote that considers leap year rules (even for the 100 & 400 year rules)....

=IF((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),MONTH(A2),DAY(A2))),A1,"d"))=365,(DATEDIF(A2,A1,"y"))+((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),(MONTH(A2)),(DAY(A2)))),A1,"d")-1)/365),(DATEDIF(A2,A1,"y"))+((DATEDIF((DATE((YEAR(A2)+(DATEDIF(A2,A1,"y"))),(MONTH(A2)),(DAY(A2)))),A1,"d"))/365))

where A2 is the date of birth, and A1 is the current date (which could be replaced with TODAY() also.

Please let me know if there are any flaws in this or simpliar ways to calculate it.- To post as a guest, your comment is unpublished.Excellent function, Steve. I believe datedif also does consider leap years :) yours looks very similar to something I wrote a couple of years ago add the client wanted to account for leap years and didn't trust datedif
- To post as a guest, your comment is unpublished.If I recall correctly, but datedif wasn't the issue regarding leap years. Once you get the number of days difference and you need to identify a specific date at which they will turn an age is when you need to factor in leap days. The second formula I wrote below does account for those days. If you don't consider that in the formula when you forecast you will be off by a few days.

- To post as a guest, your comment is unpublished.Method D is exactly what I've been looking for, minus the day portion (easily removed). I've created an inventory of PCs for my company, and am using this to calculate hardware age based on purchase date. My question is this - is there a way to take this formula and make it so that I can essentially click a button to have it applied to a cell, and then just be able to then select the cell I want it to do the calculation on? Otherwise, I'm looking at having to copy/paste the formula to several hundred cells, and then go and manually change the target cell in each one.

Thanks. - To post as a guest, your comment is unpublished.Hello,

I have added the formula for age that works well. I was able to continue the formula down the entire column which is what I needed. The issue is that a computed "age" appears in each cell despite the fact that I have not filled in a birthdate. ie: everything below the correct calculations in the column reads 115 years as a default. Is there a way to make the age cell blank until a value is placed in the birthdate cell so as not to see ongoing "115's?" Thanks in advance~ - To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.I like the "Method B" format when figuring out the age. I'm wondering if there is a way to have the years old and just the months old if younger than 2 using that format. Thanks!
- To post as a guest, your comment is unpublished.

- To post as a guest, your comment is unpublished.Hello, I have a column in my spreadsheet for birth date, that is formatted as DD-Mon-YY (ie 11-Feb-03 for my son that was born on February 11, 2003). I used the =DATEDIF function referenced above and it is returning 115 Years, 2 Months, 11 Days. What is wrong and how do I correct it? Thanks for your help.

=DATEDIF(E13,TODAY(),"Y") & " Years, " & DATEDIF(E13,TODAY(),"YM") & " Months, " & DATEDIF(E13,TODAY(),"MD") & " Days"

Note: When I type =today() in a cell it is correctly listing 11-Mar-15 as today's date.

Also note: The regional settings in my control panel list short date as dd-MMM-yy, so I don't think this would be the problem.- To post as a guest, your comment is unpublished.Hi Krista,

That would be the length of time since Excel's callander started :P

You're getting that because the cell you're referencing is blank - is you r son's DOB in Cell E13? Because that's the Cell it's calculating from.

Possible fixes:

- Your function contains errors, please see the corrected one below:

=DATEDIF(E13,TODAY(),"Y") & " Years, " & DATEDIF(E13,TODAY(),"YM") & " Months, " & DATEDIF(E13,TODAY(),"MD") & " Days"

- You need to use the format option to format it correctly as a date. i.e. to make it read in that format you'll need to go to the 'Custom' option under format and enter:[$-809]dd-mmm-yy;@ Under 'Type'

- If you're just typing it in like that, it probably won't read it as a date, you need to enter it as 11/02/2003

This will give you "12 Years, 1 Months, 1 Days"

Thanks,

Thom- To post as a guest, your comment is unpublished.Oops. LOL I had drafted this long note to you telling you it was still not working, when I noticed you were right...cell E13 was empty. My son's DOB is in cell E15. Oops. LOL Thanks for your kind help. It's working now. :) Have a great day!!

- To post as a guest, your comment is unpublished.Hi,

How do I convert fx 25102002 into a date and than age?- To post as a guest, your comment is unpublished.Hi Tanja,

I'm assuming that would be the 25th October 2002? Is the FX part of the box? If so, I'd start with =right(A1,8) which would give you the last eight digits from cell A1, so if Cell A1 contained "fx 25102002" it would simply return "25102002". Now to convert that 8 digit string into a palatable date, I'd use the =date() function. I've done one for you, you just need to switch the A1 with the cell that contains your function =DATE(RIGHT(A1,4),RIGHT(LEFT(A1,4),2),LEFT(A1,2))

Thanks, Thom

- To post as a guest, your comment is unpublished.hi what would the formula be to work out a child's date of birth in exact months (so 24/02/13 = 24mths?)

I am an early years teacher and we work in months not years and months.

Can you help?

Kimberley - To post as a guest, your comment is unpublished.
- To post as a guest, your comment is unpublished.Hi Thom,

Kindly Help me i am not understand this formula.

Please calculate my date of birth explained me my date of birth 15/09/1988 - To post as a guest, your comment is unpublished.Great!!! All formulas works perfect. Thank you so much.