نصيحة: اللغات الأخرى مترجمة من قبل Google. يمكنك زيارة English نسخة من هذا الرابط.
تسجيل الدخول
x
or
x
x
التسجيل
x

or

كيفية تحويل تاريخ الميلاد إلى سن بسرعة في إكسيل؟

على سبيل المثال، يمكنك الحصول على مجموعة من بيانات تاريخ الميلاد المختلفة في إكسيل، وتحتاج إلى تحويل تاريخ الميلاد هذا لعرض قيمة عمرها بالضبط في إكسيل، كيف تريد أن تعرف؟ تسرد هذه المقالة بعض النصائح لتحويل تاريخ الميلاد إلى العمر في إكسيل بسهولة.

الطريقة A:
تحويل تاريخ الميلاد إلى العمر مع الصيغ

الطريقة B:
بسهولة تحويل تاريخ الميلاد إلى سن دون تذكر الصيغ


عدة نقرات لتحويل تاريخ الميلاد إلى العمر في Excel:

الـ حساب العمر على أساس عيد ميلاد وظيفة كوتولس ل إكسيل يمكن أن يساعدك في تحويل تاريخ الميلاد بسرعة إلى عدة نقرات فقط.
قم بتنزيل الميزة الكاملة لـ 30 مجانًا من Kutools لبرنامج Excel الآن!


تحويل تاريخ الميلاد إلى العمر مع الصيغ

الطريقة A: تحويل تاريخ الميلاد إلى العمر مع الطرح

عادة نكتشف سن شخص ما مع التاريخ الحالي طرح تاريخ الميلاد المحدد. لذلك يفعل ذلك في إكسيل.

الخطوة شنومكس: أدخل التاريخ الحالي في خلية فارغة، مثل شنومكس / شنومكس / شنومكس في الخلية بكسنومك.

الخطوة شنومك: في خلية أخرى فارغة (يقول الخلية كسنومكس)، أدخل الصيغة = INT ((B2-A2) / 365)، ثم اضغط على أدخل الرئيسية.

الطريقة B: تحويل تاريخ الميلاد إلى العمر مع داتديف وظيفة

ويود البعض تطبيق وظيفة داتديف لحساب العمر. أدخل الصيغة = DATEDIF (A2، NOW ()، "ص") في خلية فارغة، فإنه يحسب العمر مباشرة بعد الضغط على أدخل الرئيسية.

الطريقة C: تحويل تاريخ الميلاد إلى العمر مع الدالة روندون

وظيفة أخرى لتحويل تاريخ الميلاد إلى العمر هو = روندون (يارفراك (أكسنومك، توداي ()، شنومكس)، شنومكس)، والتي سوف معرفة سن القياسية، مثل شنومكس.

الطريقة D: تحويل تاريخ الميلاد إلى العمر المحدد مع داتديف وظيفة

في بعض الأحيان العمر المحدد هو مطلوب، وكنت قد ترغب في معرفة عدد السنوات والشهور والأيام من تاريخ الولادة إلى التاريخ الحالي. يمكن أن تساعدك الصيغة التالية في معرفة ما يلي:

= داتديف (أكسنومك، توداي ()، "Y") و "يارس" و داتديف (أكسنومك، توداي ()، "يم") و "مونثس" و داتديف (أكسنومك، توداي ()، "مد" "أيام"

انظر لقطة الشاشة أعلاه، هذه الصيغة سوف تظهر لك نتيجة دقيقة، مثل شنومكس سنوات، أشهر شنومكس، وأيام شنومكس.

صيغة معقدة جدا أن نتذكر؟ ال النص السيارات سمة من سمات كوتولس ل إكسيل يمكن حفظ الصيغة كما إدخال النص التلقائي لإعادة استخدام بنقرة واحدة فقط في المستقبل! تحتاج فقط إلى تحديد الصيغة في شريط الصيغة، ثم إضافتها إلى مجموعة النص التلقائي المحددة في جزء النص التلقائي كما هو موضح أدناه لقطة شاشة. قم بتنزيل النسخة التجريبية المجانية بدون قيود في أيام 30.


بسهولة تحويل تاريخ الميلاد إلى العمر دون تذكر الصيغ

يمكنك بسهولة تحويل تاريخ الميلاد إلى سن دون تذكر الصيغ مع حساب العمر على أساس عيد ميلاد وظيفة كوتولس فور إكسيl.

كوتولس ل إكسيل : مع أكثر من شنومكس مفيد اكسل الإضافية، حر في محاولة دون قيود في أيام 30.

1. حدد خلية فارغة لتحديد مكان العمر. ثم اضغط Kutools > مساعد الصيغة > حساب العمر على أساس عيد ميلاد. انظر لقطة الشاشة:

2. في ال مساعد الصيغة مربع الحوار، حدد الخلية مع تاريخ الميلاد تحتاج إلى حساب في التاريخ مربع، ثم انقر فوق OK زر. انظر لقطة الشاشة:

3. بعد النقر OK في ال مساعد الصيغة مربع الحوار، يتم ملء العمر استنادا إلى تاريخ الميلاد في خلية فارغة مختارة على الفور. يمكنك سحب مقبض التعبئة إلى الخلايا التي تحتاجها لتحويل تاريخ الميلاد إلى العمر. انظر لقطة الشاشة:

إذا كنت ترغب في الحصول على نسخة تجريبية مجانية (60 يوم) من هذه الأداة ، الرجاء الضغط لتنزيله، ثم انتقل إلى تطبيق العملية وفقًا للخطوات المذكورة أعلاه.


بسهولة تحويل تاريخ الميلاد إلى سن دون تذكر الصيغ

كوتولس ل إكسيل يتضمن أكثر من 300 أدوات Excel سهلة الاستخدام. حر في محاولة دون قيود في أيام 30. تنزيل النسخة التجريبية المجانية الآن!


مقالات ذات صلة:


Kutools for Excel يحل معظم مشاكلك ، ويزيد من إنتاجيتك بنسبة 80٪

  • إعادة استخدام: إدراج بسرعة الصيغ المعقدة ، الرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة المرور إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر الفورمولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (بسهولة قراءة وتحرير أعداد كبيرة من الخلايا) ؛ لصق على المدى المصفى...
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ انقسام خلايا المحتوى ؛ الجمع بين تكرار الصفوف / الأعمدة... منع الخلايا المكررة. مقارنة النطاقات...
  • حدد تكرار أو فريد الصفوف. حدد صفوف فارغة (جميع الخلايا فارغة) ؛ سوبر البحث والعثور غامض في العديد من المصنفات ؛ اختيار عشوائي ...
  • نسخة طبق الأصل خلايا متعددة دون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة. إدراج الرصاصات، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، حذف حسب الموضع ، إزالة الفضاء. إنشاء وطباعة مجاميع ترحيل الصفحات ؛ تحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ تصنيف متقدم حسب الشهر / الأسبوع / اليوم ، التردد وأكثر ؛ فلتر خاص بواسطة جريئة ، مائل ...
  • الجمع بين المصنفات وأوراق العمل. دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; دفعة تحويل XLS ، XLSX وقوات الدفاع الشعبي...
  • أكثر من ميزات 300 القوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. ميزات كاملة النسخة التجريبية المجانية من 30.
علامة التبويب kte 201905

علامة تبويب Office توفر واجهة مبوبة لـ Office ، واجعل عملك أسهل بكثير

  • تمكين تحرير علامات التبويب والقراءة في Word و Excel و PowerPointوالناشر والوصول ، Visio ومشروع.
  • افتح مستندات متعددة وقم بإنشائها في علامات تبويب جديدة لنفس النافذة ، وليس في نوافذ جديدة.
  • يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات نقرات الماوس لك كل يوم!
أسفل مكتبه
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Arbaz Khan · 1 years ago
    Do not show the Detedif Formula in my Excel
    What to do Know?
  • To post as a guest, your comment is unpublished.
    elsie · 1 years ago
    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.
      crystal · 1 years ago
      Dear elsie,
      Please try this formula: =DATEDIF(DATE(IF(LEFT(A2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(A2,2),"20"&LEFT(A2,2)),MID(A2,3,2),MID(A2,5,2)),TODAY(),"y"). A2 is the cell contains the ID number you want to calculate the age based on.
  • To post as a guest, your comment is unpublished.
    Usama · 1 years ago
    Thanks soooooo much dear....!
  • To post as a guest, your comment is unpublished.
    Alfred · 1 years ago
    please do i calculate the birth date from age
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      This formula =DATE(YEAR(TODAY())-A1,MONTH(TODAY()),DAY(TODAY())) can help you to calculate the birthday from a given age based on today's date.
  • To post as a guest, your comment is unpublished.
    lep · 1 years ago
    Leap years! 365.25
  • To post as a guest, your comment is unpublished.
    Amelia · 2 years ago
    Can you add an if clause so that if the DOB column is blank the AGE column will also be blank?
  • To post as a guest, your comment is unpublished.
    SUBRAMANIAN K · 2 years ago
    Thank you Catherine,
    for the whole-hearted appreciation of my solution!
  • To post as a guest, your comment is unpublished.
    Catherine · 2 years ago
    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.
      Catherine · 2 years ago
      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.
      SUBRAMANIAN K · 2 years ago
      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.
      SUBRAMANIAN K · 2 years ago
      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.
    Reuben Mkumbo · 2 years ago
    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.
    Reuben Mkumbo · 2 years ago
    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.
    Kim93 · 2 years ago
    how can i get the year of birth??
  • To post as a guest, your comment is unpublished.
    Arun · 2 years ago
    [b]Very helpful....Now I knew how to convert date in text and count years. :roll: [/b]
  • To post as a guest, your comment is unpublished.
    MAJID · 2 years ago
    DEAR SIR KINDLY HELP TO FIND OUT MY DATE OF APPOINTMENT OF SERVICE , MY SERVICE LENGTH IS 24 YEARS 6 MONTH & 5 DAYS ON DATE 24 DECEMBER 2016 KINDLY SUGGEST FORMULA TO FIND OUT DATE OF APPOINTMENT I SHALL REMAIN THANKFUL TO YOU.
  • To post as a guest, your comment is unpublished.
    Matt Viverette · 2 years ago
    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.
    SUBRAMANIAN K` · 2 years ago
    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 · 2 years ago
    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.
      SUBRAMANIAN K` · 2 years ago
      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.
    Kishore Tholana · 3 years ago
    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.
    Krishna Gupta · 3 years ago
    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.
    ANNYONG · 3 years ago
    my problem is that when I used the formula and drag it down it will just copy the value of the first cell. .

    I have to enter it one by one.
  • To post as a guest, your comment is unpublished.
    Wendy · 3 years ago
    I want to find out the number of days from 8 May 2016 to 31 July 2016. Is there a formula for this?
  • To post as a guest, your comment is unpublished.
    Ranil Somarathna · 3 years ago
    This function is great. Very easy to understand. Thanks!
  • To post as a guest, your comment is unpublished.
    Moon · 3 years ago
    =DATEDIF(B10,NOW(),"Y")

    Work great for me thank you!!!
  • To post as a guest, your comment is unpublished.
    # Subramanian K` · 3 years ago
    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.
    Beloved · 3 years ago
    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.
    Velmurugan rengaraja · 3 years ago
    It is amazing to understand and very very useful.
  • To post as a guest, your comment is unpublished.
    Subramanian K · 3 years ago
    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.
      Thom H · 3 years ago
      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.
    Subramanian K · 3 years ago
    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 · 4 years ago
    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.
      Thom · 3 years ago
      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.
    Gatewarden · 4 years ago
    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.
    Jaspreet · 4 years ago
    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.
    Miranda Creed-Miles · 4 years ago
    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.
    Channing · 4 years ago
    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.
      Steve Konz · 4 years ago
      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.
    Atomicpetro · 4 years ago
    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.
      Steve Konz · 4 years ago
      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 · 4 years ago
      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
  • To post as a guest, your comment is unpublished.
    guru · 4 years ago
    It is Nice i think it good to learn to Excel 8) :roll: :P
  • To post as a guest, your comment is unpublished.
    Steve Konz · 4 years ago
    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.
    Steve Konz · 4 years ago
    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.
      Thom h · 4 years ago
      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.
        Steve Konz · 4 years ago
        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.
    Chris · 4 years ago
    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.
      Thom h · 4 years ago
      Just create a macro to take the selected cell, and dump the Function next to it, referencing that cell. You'll need to look in another thread to find macro guidance. It just drag the cell down
  • To post as a guest, your comment is unpublished.
    Shephard · 4 years ago
    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.
    raksmey · 4 years ago
    I really appreciate with this fomular.
    :D
  • To post as a guest, your comment is unpublished.
    KELSEY · 4 years ago
    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.
      Thom H · 4 years ago
      Hi Kelsey,

      Option B (DATEDIF()) only gives you the years as a number value - did you perhaps mean option D? I've added an IF statement to Option B, so that it only shows the months and yeards if the person is 2 or under:
      =IF(DATEDIF(A2,NOW(),"y")
  • To post as a guest, your comment is unpublished.
    Krista · 4 years ago
    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.
      Thom H · 4 years ago
      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.
        Krista · 4 years ago
        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.
          mario · 4 years ago
          Krista Check your E13 Column it must be blank, thats why you get 115 Years, change e13 to correct column number
  • To post as a guest, your comment is unpublished.
    Tanja · 4 years ago
    Hi,

    How do I convert fx 25102002 into a date and than age?
    • To post as a guest, your comment is unpublished.
      Thom H · 4 years ago
      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.
    Kimberley Woodward · 4 years ago
    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.
      Thom H · 4 years ago
      Hi Kimberley,
      Datedif would do that for you, Just use =DATEDIF(A1,TODAY(),"m") Assuming the DOB is in Cell A1.
      Thanks,
      Thom
  • To post as a guest, your comment is unpublished.
    rowena · 4 years ago
    Hi thanks! ;-)
    Its abig help for me having your tips....
  • To post as a guest, your comment is unpublished.
    B Kumar · 4 years ago
    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.
      Thom H · 4 years ago
      Hi B Kumar,
      I'm afraid I don't follow your question. Could you please clarify for me?
      Thanks, Thom
  • To post as a guest, your comment is unpublished.
    Carlos Somarriba · 4 years ago
    Great!!! All formulas works perfect. Thank you so much.
  • To post as a guest, your comment is unpublished.
    Manoj · 4 years ago
    Is there any way to convert age into date of birth. I have a sheet with name and age of customers as if age in years only. Is there any way to have DOB as result
    • To post as a guest, your comment is unpublished.
      Thom H · 4 years ago
      Hi Manoj,
      You wouldn't really be able to get the exact date of birth, unless you knew how old they were exactly i.e. 20 years, 50 days. You could calculate it to year by doing something like =YEAR(TODAY())-A1 assuming their current age was in Cell A1.
      Thanks,
      Thom
  • To post as a guest, your comment is unpublished.
    Pedro · 4 years ago
    Its was very easy to follow and all the possible options for me to decide which was the best that accommodate my needs. Thanks :-) ;-) :lol:
  • To post as a guest, your comment is unpublished.
    Tanmay Chanda · 4 years ago
    Its Very helpful many many thanks.....
  • To post as a guest, your comment is unpublished.
    David84* · 4 years ago
    Many thanks for this, just got me out of a tight spot at work! Stay classy, excel geniuses.