Note: The other languages of the website are Google-translated. Back to English
Oturum aç  \/ 
x
or
x
Kaydol  \/ 
x

or

Excel'de doğum tarihini hızlı bir şekilde yaşa dönüştürmek nasıl?

Örneğin, Excel'de bir dizi farklı doğum tarihi verisi alıyorsunuz ve bu doğum tarihlerini Excel'de tam yaş değerlerini gösterecek şekilde dönüştürmeniz gerekiyor, bunu nasıl anlamak istersiniz? Bu makale, doğum tarihini Excel'de kolayca yaşa dönüştürmek için bazı ipuçlarını listeler.

Yöntem A:
Formüller ile doğum tarihini yaşa dönüştürün

Yöntem B:
Formülleri hatırlamadan doğum tarihini kolayca yaşa dönüştürün


Formüller ile doğum tarihini yaşa dönüştürün

Aşağıdaki formüller, Excel'de doğum tarihine göre yaşın hesaplanmasına yardımcı olabilir.

INT işlevi ile doğum tarihini yaşa dönüştürün

INT işlevi, verilen doğum tarihine göre bir kişinin yaşını hesaplamaya yardımcı olabilir, lütfen aşağıdaki işlemleri gerçekleştirin.

1. Doğum tarihini ve geçerli tarihi ayrı ayrı içeren iki sütun olduğunu varsayalım.

2. Yaşın çıktısını almak için boş bir hücre seçin, aşağıdaki formülü içine girin ve Keşfet anahtar. Sonuç hücresini seçin ve ardından sürükleyin Doldurma Kolu tüm sonuçları almak için aşağı.

=INT((B2-A2)/365)

DATEDIF işleviyle doğum tarihini yaşa dönüştürün

Aşağıdaki DATEDIF işlevi de yardımcı olabilir.

Yaşın çıktısını almak için boş bir hücre seçin, aşağıdaki formülü içine girin ve Keşfet anahtar. Sonuç hücresini seçin ve ardından sürükleyin Doldurma Kolu tüm sonuçları almak için aşağı.

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

ROUNDDOWN işleviyle doğum tarihini yaşa dönüştürün

Aşağıdaki gibi doğum gününe göre yaşı hesaplamak için ROUNDDOWN işlevini deneyin.

Yaşın çıktısını almak için boş bir hücre seçin, aşağıdaki formülü içine girin ve Keşfet anahtar. Sonuç hücresini seçin ve ardından sürükleyin Doldurma Kolu tüm sonuçları almak için aşağı.

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

DATEDIF işlevi ile yaşı Yıl + Ay + Gün biçiminde görüntüleyin

Yaşı Yıl + Ay + Gün formatında görüntülemek istiyorsanız, lütfen aşağıdaki DATEDIF işlevini deneyin.

Yaşın çıktısını almak için boş bir hücre seçin, aşağıdaki formülü içine girin ve Keşfet anahtar. Sonuç hücresini seçin ve ardından sürükleyin Doldurma Kolu tüm sonuçları almak için aşağı.

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


Doğum tarihini kolayca yaşa dönüştürün formülleri hatırlamadan

Formülleri hatırlamadan doğum tarihini kolaylıkla yaşa dönüştürebilirsiniz. Tarih ve Saat Yardımcısı of Kutools for Excel. 

Başvurmadan önce Kutools for ExcelLütfen önce indirin ve kurun.

1. Yaşın çıktısını almak için boş bir hücre seçin. Sonra tıklayın Kutools > Formül Yardımcısı > Tarih ve Saat Yardımcısı

2. içinde Tarih ve Saat Yardımcısı iletişim kutusu, lütfen aşağıdaki gibi yapılandırın.

  • 2.1) Şuraya gidin: Yaş sekme;
  • 2.2) içinde Doğum tarihi kutusunda yaşa dönüştürebileceğiniz doğum tarihini içeren hücreyi seçin;
  • 2.3) Seçin Bugün seçeneği için Bölüm;
  • 2.4) şuradan bir sonuç türü belirtin: Çıktı sonuç türü açılır liste;
  • 2.5) OK düğmesine basın.

Daha sonra yaş, seçilen hücrede doldurulur. Sonuç hücresini seçin ve ardından tüm yaşları almak için Dolgu Tutamaçını sonuna kadar sürükleyin.

not: Yaşı Yıl + Ay + Gün formatında görüntülemek istiyorsanız lütfen seçiniz Yıl + Ay + Gün itibaren Çıktı sonuç türü açılır liste. Ve sonuç, aşağıda gösterilen ekran görüntüsü olarak gösterilecektir. İhtiyaçlarınıza göre yaşı ay, hafta veya gün olarak göstermeyi de destekler.

  Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.


İlgili Makaleler:


En İyi Ofis Üretkenliği Araçları

Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır

  • Yeniden: Hızlıca yerleştirin karmaşık formüller, grafikler ve daha önce kullandığınız her şey; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
  • Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
  • Hücreleri / Satırları / Sütunları Birleştirme Veri kaybetmeden; Bölünmüş Hücre İçeriği; Yinelenen Satırları / Sütunları Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
  • Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
  • Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
  • Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
  • Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
  • Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
  • 300'den fazla güçlü özellik. Office / Excel 2007-2019 ve 365'i destekler. Tüm dilleri destekler. Kuruluşunuzda veya kuruluşunuzda kolay dağıtım. Tam özellikli 30 günlük ücretsiz deneme. 60 günlük para iade garantisi.
kte sekmesi 201905

Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir

  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
ofis tabanı
Say something here...
symbols left.
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.
    srinu · 1 years ago
    How to convert age to date of birth
  • To post as a guest, your comment is unpublished.
    Chaim Lederfeind · 1 years ago
    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.
    sami muhammad · 1 years ago
    thanks you so much very very good formula
  • To post as a guest, your comment is unpublished.
    rg · 1 years ago
    Just wanted to say THANK YOU!
  • To post as a guest, your comment is unpublished.
    arbazalamkhan123456@gmail.com · 3 years ago
    Do not show the Detedif Formula in my Excel
    What to do Know?
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @elsie 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.
    elsie · 3 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.
    Usama · 3 years ago
    Thanks soooooo much dear....!
  • To post as a guest, your comment is unpublished.
    crystal · 3 years ago
    @Alfred 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.
    Alfred · 3 years ago
    please do i calculate the birth date from age
  • To post as a guest, your comment is unpublished.
    lep · 3 years ago
    Leap years! 365.25
  • To post as a guest, your comment is unpublished.
    Amelia · 3 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 · 3 years ago
    Thank you Catherine,
    for the whole-hearted appreciation of my solution!
  • To post as a guest, your comment is unpublished.
    Catherine · 3 years ago
    @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.
    SUBRAMANIAN K · 3 years ago
    @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.
    SUBRAMANIAN K · 3 years ago
    @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.
    Catherine · 3 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.
    Reuben Mkumbo · 3 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 · 3 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 · 4 years ago
    how can i get the year of birth??
  • To post as a guest, your comment is unpublished.
    Arun · 4 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.
    JENNIEJEN · 4 years ago
    @Thom H THOM H...
    thank you so much... you're the best!
  • To post as a guest, your comment is unpublished.
    Mubeen · 4 years ago
    @Rasel yes its very helpful for me. i easily understand. :)
  • To post as a guest, your comment is unpublished.
    MAJID · 4 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 · 4 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` · 4 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.
    SUBRAMANIAN K` · 4 years ago
    @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.
    Cedric · 4 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.
    Kishore Tholana · 4 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 · 4 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 · 4 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 · 5 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 · 5 years ago
    This function is great. Very easy to understand. Thanks!
  • To post as a guest, your comment is unpublished.
    Moon · 5 years ago
    =DATEDIF(B10,NOW(),"Y")

    Work great for me thank you!!!
  • To post as a guest, your comment is unpublished.
    # Subramanian K` · 5 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 · 5 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.
    Thom H · 5 years ago
    @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.
    Rasel · 5 years ago
    @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.
    Velmurugan rengaraja · 5 years ago
    It is amazing to understand and very very useful.
  • To post as a guest, your comment is unpublished.
    Subramanian K · 5 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.
    Subramanian K · 5 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.
    Thom · 5 years ago
    @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.
    Thom · 5 years ago
    @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.
    Steve Konz · 5 years ago
    @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.
    Steve Konz · 5 years ago
    @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.
    Steve Konz · 5 years ago
    @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.
    Narsing rao K · 5 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.
    Gatewarden · 5 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.
    mario · 5 years ago
    @Krista 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.
    Thom h · 5 years ago
    @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