如何在Excel中快速將出生日期轉換為年齡？
例如，您在Excel中獲得了各種出生日期數據，並且需要轉換這些出生日期以在Excel中顯示其確切年齡值，您想如何計算？ 本文列出了一些技巧，可以在Excel中輕鬆地將出生日期轉換為年齡。
方法A：
使用公式將生日轉換為年齡
使用公式將生日轉換為年齡
以下公式可以幫助您根據Excel中的生日來計算年齡。
使用INT函數將生日轉換為年齡
INT功能可以根據給定的出生日期幫助計算一個人的年齡，請執行以下操作。
1.假設有兩列分別包含生日和當前日期。
2.選擇一個空白單元格以輸出年齡，在其中輸入以下公式，然後按 Enter 鍵。 選擇結果單元格，然後將其拖動 填充手柄 下來獲得所有結果。
=INT((B2A2)/365)
使用DATEDIF函數將生日轉換為年齡
下面的DATEDIF函數也可以提供幫助。
選擇一個空白單元格以輸出年齡，在其中輸入以下公式，然後按 Enter 鍵。 選擇結果單元格，然後將其拖動 填充手柄 下來獲得所有結果。
=DATEDIF(A2,NOW(),"y")
使用ROUNDDOWN函數將生日轉換為年齡
嘗試使用ROUNDDOWN函數根據生日計算年齡，如下所示。
選擇一個空白單元格以輸出年齡，在其中輸入以下公式，然後按 Enter 鍵。 選擇結果單元格，然後將其拖動 填充手柄 下來獲得所有結果。
=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)
使用DATEDIF函數將年齡顯示為年+月+日格式
如果要將年齡顯示為年+月+日格式，請嘗試使用以下DATEDIF函數。
選擇一個空白單元格以輸出年齡，在其中輸入以下公式，然後按 Enter 鍵。 選擇結果單元格，然後將其拖動 填充手柄 下來獲得所有結果。
=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"
輕鬆將生日轉換為年齡 不記得公式
您可以輕鬆地將生日轉換為年齡，而無需記住公式 日期和時間助手 of Kutools for Excel.
申請前 Kutools for Excel請 首先下載並安裝.
1.選擇一個空白單元格以輸出年齡。 然後點擊 Kutools > 公式助手 > 日期和時間助手.
2。 在裡面 日期和時間助手 對話框，請進行以下配置。
 2.1）前往 年齡 標籤;
 2.2）在 出生日期 框，選擇包含要轉換為年齡的生日的單元格；
 2.3）選擇 今天 在選項 至 部分;
 2.4）從 輸出結果類型 下拉列表;
 2.5）點擊 OK 按鈕。
然後，在選定的單元格中填充年齡。 選擇結果單元格，然後將“填充手柄”一直向下拖動以獲取所有年齡。
備註：如果要將年齡顯示為“年+月+日”格式，請選擇 年+月+日 來自 輸出結果類型 下拉列表。 結果將顯示為以下屏幕截圖。 它還支持根據您的需要將年齡顯示為月，週或日。
如果您想免費試用該工具（30天）， 請點擊下載，然後按照上述步驟進行操作。
相關文章：
最佳辦公效率工具
Kutools for Excel解決了您的大多數問題，並使您的生產率提高了80％
 重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
 合併單元格/行/列 不會丟失數據； 拆分單元格內容； 合併重複的行/列...防止細胞重複； 比較範圍...
 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
 超過300種強大功能。 支持Office / Excel 20072019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆
 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，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.

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.

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 wholehearted appreciation of my solution! 
To post as a guest, your comment is unpublished.@Catherine YOU ARE AMAZING!!! It worked fantastically. I can now convert all the data for tests from the children in my class using this and I don;t have to work out their age each time. This makes my job much quicker and easier.
THANKYOU! 
To post as a guest, your comment is unpublished.@Catherine 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.@Catherine 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.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.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.

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.@Cedric 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.

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 20160304:20160304 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 semicolons (;) 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.@Subramanian K Datedif is better, but was only introduced on more recent versions of Excel. I believe you can use it as far back as 2003, but it won't give you any autosum guidance (You can still use it though!)

To post as a guest, your comment is unpublished.@Daini Malhotra a problem i faced. like: birth date = 1st Nov.'2014, today is 31st Dec.'2015 then the result comes = 1 year, 1 month, 30 days, but the days should be 1day more i.e. 31 days. how can I do this in the above formula? pls help

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.@Narsing rao K 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. 112 and months in the 2nd column JanDec) 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.@JAGDISH BAUDH Hi Jagdish,
Could you please clarify what exactly you need to do? Did you want to work out the date but roll it back by 1 day, 2 months and three years? if so, I'd go with method D (Date Difs) to get each one (year, months and days and wrap each datedif within an =sum() and minus the needed difference form each them. Happy to provide an example if you can clarify what exactly you're after :)
Thanks,
Thom 
To post as a guest, your comment is unpublished.@Atomicpetro 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.@Thom h 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.@Channing 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.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 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.

To post as a guest, your comment is unpublished.@Atomicpetro Swap the reference to cell b1 with =date then you build the date with three values: year, month and day. You reference b1 add the year then you'll just have to put 01 as the year and month. Or you could steal the dates from cell b2 using =month and =day