·
1 years ago
How to convert age to date of birth
Excelで生年月日を年齢にすばやく変換するにはどうすればよいですか？
たとえば、Excelでさまざまな生年月日データを取得し、これらの生年月日を変換してExcelで正確な年齢値を表示する必要がある場合、どのように把握しますか？ この記事では、Excelで生年月日を年齢に簡単に変換するためのヒントをいくつか紹介します。
数式を使用して生年月日を年齢に変換する
以下の数式は、Excelの生年月日に基づいて年齢を計算するのに役立ちます。
INT関数を使用して生年月日を年齢に変換する
INT関数は、特定の生年月日に基づいて人の年齢を計算するのに役立ちます。次のようにしてください。
1.生年月日と現在の日付を別々に含むXNUMXつの列があるとします。
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 Excel用のKutoolsl.
申請する前に Kutools for Excelについては 最初にダウンロードしてインストールします.
1.空白のセルを選択して年齢を出力します。 次に、をクリックします クツール > フォーミュラヘルパー > 日付と時刻のヘルパー.
2。 の中に 日付と時刻のヘルパー ダイアログボックスで、次のように構成してください。
- 2.1）に行く 年齢 タブ;
- 2.2） 生年月日 ボックスで、年齢に変換する生年月日を含むセルを選択します。
- 2.3）を選択します 今日 内のオプション に セクション;
- 2.4）から結果タイプを指定します 出力結果タイプ ドロップダウンリスト;
- 2.5）をクリックします OK ボタン。
次に、選択したセルに年齢が入力されます。 結果セルを選択し、塗りつぶしハンドルを一番下までドラッグして、すべての年齢を取得します。
ノート：年齢を年+月+日の形式で表示する場合は、を選択してください 年+月+日 から 出力結果タイプ ドロップダウンリスト。 そして、結果は以下のスクリーンショットのように表示されます。 また、ニーズに基づいて月、週、または日として年齢を表示することもサポートしています。
このユーティリティの無料トライアル（30日）をご希望の場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。
関連記事：
最高のオフィス生産性ツール
Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます
- 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
- スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
- セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
- [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
- 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
- テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
- スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
- ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
- 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性が50％向上し、毎日何百ものマウスクリックが減ります。
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
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 whole-hearted 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 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.@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. 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.@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