Hướng dẫn how to calculate age from date of birth in excel 2022 - cách tính tuổi theo ngày sinh trong excel 2022

Hướng dẫn cho thấy những cách khác nhau để có được tuổi từ sinh nhật ở Excel. Bạn sẽ học được một số ít các công thức để tính tuổi là một số năm hoàn chỉnh, nhận được tuổi chính xác trong nhiều năm, tháng và ngày vào ngày hôm nay hoặc một ngày cụ thể.

Không có chức năng đặc biệt để tính tuổi ở Excel, tuy nhiên tồn tại một vài cách khác nhau để chuyển đổi ngày sinh đến tuổi. Hướng dẫn này sẽ giải thích những lợi thế và nhược điểm của mỗi cách, cho thấy cách tạo ra một công thức tính toán độ tuổi hoàn hảo trong Excel và điều chỉnh nó để giải quyết một số nhiệm vụ cụ thể.

Cách tính tuổi từ ngày sinh ở Excel

Trong cuộc sống hàng ngày, câu hỏi "Bạn bao nhiêu tuổi?" Thường ngụ ý một câu trả lời cho thấy bạn đã còn sống bao nhiêu năm. Trong Microsoft Excel, bạn có thể tạo ra một công thức để tính độ tuổi chính xác trong nhiều tháng, ngày, giờ và thậm chí vài phút. Nhưng hãy truyền thống và học cách tính tuổi từ DOB trong nhiều năm đầu tiên.

Công thức excel cơ bản cho tuổi trong năm

Làm thế nào để bạn thường tìm ra tuổi của ai đó? Đơn giản bằng cách trừ đi ngày sinh từ ngày hiện tại. Công thức tuổi thông thường này cũng có thể được sử dụng trong Excel.

Giả sử một ngày sinh là trong tế bào B2, công thức tính toán tuổi trong năm diễn ra như sau:

=(TODAY()-B2)/365

Phần đầu tiên của công thức (hôm nay ()-b2) trả về sự khác biệt giữa ngày và ngày sinh hiện tại là ngày, và sau đó bạn chia con số đó cho 365 để có được số năm.

Công thức là rõ ràng và dễ nhớ, tuy nhiên, có một vấn đề nhỏ. Trong hầu hết các trường hợp, nó trả về một số thập phân như được thể hiện trong ảnh chụp màn hình dưới đây.

Để hiển thị số năm hoàn chỉnh, hãy sử dụng hàm INT để làm tròn số thập phân xuống số nguyên gần nhất:

=INT((TODAY()-B2)/365)

Hạn chế: Sử dụng công thức tuổi này trong Excel tạo ra kết quả khá chính xác, nhưng không hoàn hảo. Chia cho số ngày trung bình trong một năm hoạt động tốt trong hầu hết thời gian, nhưng đôi khi nó bị sai độ tuổi. Ví dụ, nếu ai đó sinh ngày 29 tháng 2 và hôm nay là ngày 28 tháng 2, công thức sẽ làm cho một người lớn tuổi hơn. Using this age formula in Excel produces pretty accurate results, but not flawless. Dividing by the average number of days in a year works fine most of the time, but sometimes it gets the age wrong. For example, if someone was born on February 29 and today is February 28, the formula will make a person one day older.

Thay thế, bạn có thể chia cho 365,25 thay vì 365 vì mỗi năm thứ tư đều có 366 ngày. Tuy nhiên, cách tiếp cận này cũng không hoàn hảo. Ví dụ, nếu bạn đang tính toán tuổi của một đứa trẻ chưa sống qua một năm nhảy, chia cho 365,25 tạo ra kết quả sai.

Nhìn chung, trừ đi ngày sinh từ ngày hiện tại hoạt động rất tốt trong cuộc sống bình thường, nhưng không phải là cách tiếp cận lý tưởng trong Excel. Hơn nữa trong hướng dẫn này, bạn sẽ tìm hiểu một vài chức năng đặc biệt tính toán tuổi không có lỗi bất kể năm nào.

Tính tuổi từ ngày sinh với chức năng Yearfrac

Một cách đáng tin cậy hơn để chuyển đổi DOB thành Age in Excel là sử dụng hàm YearFrac trả về tỷ lệ của năm, tức là số lượng cả ngày giữa hai ngày.

Cú pháp của chức năng Yearfrac như sau:

Yearfrac (start_date, end_date, [cơ sở])

Hai đối số đầu tiên là rõ ràng và hầu như không yêu cầu bất kỳ lời giải thích bổ sung nào. Cơ sở là một lập luận tùy chọn xác định cơ sở số ngày để sử dụng.

Để tạo công thức tuổi hoàn toàn đúng, hãy cung cấp các giá trị sau cho hàm năm:

• Start_date - ngày sinh. - date of birth.
• End_date - hôm nay () hàm để trả về ngày hôm nay. - TODAY() function to return the today's date.
• Cơ sở - Sử dụng cơ sở 1 bảo Excel chia số ngày thực tế mỗi tháng cho số ngày thực tế mỗi năm.- use basis 1 that tells Excel to divide the actual number of days per month by the actual number of days per year.

Xem xét những điều trên, một công thức Excel để tính toán tuổi từ ngày sinh như sau:

Yearfrac (ngày sinh, hôm nay (), 1)

Giả sử ngày sinh là trong tế bào B2, công thức có hình dạng sau:

=YEARFRAC(B2, TODAY(), 1)

Giống như trong ví dụ trước, kết quả của hàm năm cũng là một số thập phân. Để khắc phục điều này, hãy sử dụng chức năng Rounddown với 0 trong đối số cuối cùng vì bạn không muốn bất kỳ vị trí thập phân nào.

Vì vậy, đây là một công thức năm được cải thiện để tính tuổi trong Excel:

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

Tính tuổi trong excel với ngày

Một cách nữa để chuyển đổi ngày sinh đến tuổi ở Excel là sử dụng chức năng Datedif:

Datedif (start_date, end_date, đơn vị)

Hàm này có thể trả về sự khác biệt giữa hai ngày trong các đơn vị thời gian khác nhau như năm, tháng và ngày, tùy thuộc vào giá trị bạn cung cấp trong đối số đơn vị:unit argument:

• Y - Trả về số năm hoàn chỉnh giữa ngày bắt đầu và ngày kết thúc.complete years between the start and end dates.
• M - Trả về số tháng hoàn chỉnh giữa các ngày.complete months between the dates.
• D - Trả về số ngày giữa hai ngày.days between the two dates.
• YM - Trả lại tháng, bỏ qua ngày và năm.months, ignoring days and years.
• MD - Trả về sự khác biệt trong ngày, bỏ qua tháng và năm.days, ignoring months and years.
• YD - Trả về sự khác biệt trong ngày, bỏ qua nhiều năm.days, ignoring years.

Vì chúng tôi nhằm mục đích tính toán tuổi tác, chúng tôi đang sử dụng đơn vị "Y":age in years, we are using the "y" unit:

Datedif (ngày sinh, hôm nay (), "y")

Trong ví dụ này, DOB nằm trong ô B2 và bạn tham chiếu tế bào này trong công thức tuổi của bạn:

=DATEDIF(B2, TODAY(), "y")

Không cần chức năng làm tròn bổ sung trong trường hợp này vì một công thức Datedif với đơn vị "Y" tính toán số năm đầy đủ:

Làm thế nào để có được tuổi từ sinh nhật trong các năm, tháng và ngày

Như bạn vừa thấy, tính toán tuổi là số năm đầy đủ mà người đó đã sống rất dễ dàng, nhưng nó không phải lúc nào cũng đủ. Nếu bạn muốn biết độ tuổi chính xác, tức là bao nhiêu năm, tháng và ngày có giữa ngày sinh của ai đó và ngày hiện tại, hãy viết 3 chức năng Datedif khác nhau:

1. Để có được số năm: =DATEDIF(B2, TODAY(), "Y")
2. Để có được số tháng: =DATEDIF(B2, TODAY(), "YM")
3. Để có được số ngày: =DATEDIF(B2,TODAY(),"MD")

Trong đó B2 là ngày sinh.

Và sau đó, kết hợp các chức năng trên trong một công thức duy nhất, như thế này:

=DATEDIF(B2,TODAY(),"Y") & DATEDIF(B2,TODAY(),"YM") & DATEDIF(B2,TODAY(),"MD")

Công thức trên trả về 3 số (năm, tháng và ngày) được nối với nhau trong một chuỗi văn bản, như thể hiện trong ảnh chụp màn hình dưới đây:

Không có nhiều ý nghĩa, uh? Để làm cho kết quả có ý nghĩa hơn, hãy tách các số bằng dấu phẩy và xác định mỗi giá trị có nghĩa là gì:

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

Kết quả có vẻ tốt hơn nhiều bây giờ:

Công thức hoạt động rất tốt, nhưng bạn có thể cải thiện nó hơn nữa bằng cách ẩn các giá trị bằng không. Đối với điều này, thêm 3 nếu các câu lệnh kiểm tra 0, một cho mỗi ngày DIETIF:

=INT((TODAY()-B2)/365)0

Ảnh chụp màn hình sau đây cho thấy công thức tuổi xuất sắc cuối cùng trong hành động - nó trả về tuổi trong nhiều năm, tháng và ngày, chỉ hiển thị các giá trị khác không:non-zero values:

Mẹo. Nếu bạn đang tìm kiếm một công thức Excel để tính tuổi theo năm và tháng, hãy lấy công thức trên và loại bỏ khối IF (datedif ()) cuối cùng tính toán ngày. If you are looking for an Excel formula to calculate age in years and months, take the above formula and remove the last IF(DATEDIF()) block that computes days.

Các công thức cụ thể để tính tuổi trong excel

Các công thức tính toán tuổi chung được thảo luận ở trên công việc tuyệt vời trong hầu hết các trường hợp. Tuy nhiên, trong một số tình huống, bạn có thể cần một cái gì đó rất cụ thể. Tất nhiên, không thể bao quát mọi kịch bản, nhưng các ví dụ sau đây sẽ cung cấp cho bạn một số ý tưởng về cách bạn có thể điều chỉnh một công thức độ tuổi tùy thuộc vào nhiệm vụ cụ thể của bạn.

Cách tính tuổi vào một ngày cụ thể trong Excel

Nếu bạn muốn biết độ tuổi của ai đó vào một ngày nhất định, hãy sử dụng công thức Age Datedif được thảo luận ở trên, nhưng thay thế hàm TODAY () trong đối số thứ 2 với ngày cụ thể.

Giả sử ngày sinh là ở B1, công thức sau sẽ trở lại tuổi vào ngày 1 tháng 1 năm 2020:

=INT((TODAY()-B2)/365)1

Để làm cho công thức tuổi của bạn linh hoạt hơn, bạn có thể nhập ngày vào một số ô và tham chiếu ô đó trong công thức của bạn:

=INT((TODAY()-B2)/365)2

Trong đó B1 là DOB và B2 là ngày mà bạn muốn tính tuổi.

Tính tuổi trong một năm nhất định

Công thức này có ích trong các tình huống khi ngày hoàn thành tính toán không được xác định và bạn chỉ biết cả năm.

Giả sử bạn đang làm việc với một cơ sở dữ liệu y tế và mục tiêu của bạn là tìm hiểu độ tuổi của bệnh nhân tại thời điểm họ trải qua kỳ thi y tế đầy đủ cuối cùng.

Giả sử ngày sinh là trong cột B bắt đầu với hàng 3 và năm kiểm tra y tế cuối cùng là trong cột C, công thức tính toán tuổi diễn ra như sau:

=INT((TODAY()-B2)/365)3

Vì ngày kiểm tra y tế chính xác không được xác định, bạn sử dụng chức năng ngày với một đối số ngày và tháng tùy ý, ví dụ: Ngày (C3, 1, 1).

Hàm ngày trích xuất năm từ ô B3, tạo ra một ngày hoàn chỉnh bằng số tháng và ngày mà bạn đã cung cấp (1-Jan trong ví dụ này) và chuyển ngày đó cho Datedif. Kết quả là, bạn có tuổi của bệnh nhân vào ngày 1 tháng 1 của một năm cụ thể:

Tìm ra một ngày khi một người đạt được n tuổi

Giả sử bạn của bạn sinh ngày 8 tháng 3 năm 1978. Làm sao bạn biết vào ngày nào anh ấy hoàn thành 50 tuổi? Thông thường, bạn chỉ cần thêm 50 năm vào ngày sinh của người đó. Trong Excel, bạn làm tương tự bằng cách sử dụng hàm ngày:

=INT((TODAY()-B2)/365)4

Trong đó B2 là ngày sinh.

Và sau đó, kết hợp các chức năng trên trong một công thức duy nhất, như thế này:

Công thức trên trả về 3 số (năm, tháng và ngày) được nối với nhau trong một chuỗi văn bản, như thể hiện trong ảnh chụp màn hình dưới đây:

Không có nhiều ý nghĩa, uh? Để làm cho kết quả có ý nghĩa hơn, hãy tách các số bằng dấu phẩy và xác định mỗi giá trị có nghĩa là gì:

• Kết quả có vẻ tốt hơn nhiều bây giờ:
  =INT((TODAY()-B2)/365)5
• Công thức hoạt động rất tốt, nhưng bạn có thể cải thiện nó hơn nữa bằng cách ẩn các giá trị bằng không. Đối với điều này, thêm 3 nếu các câu lệnh kiểm tra 0, một cho mỗi ngày DIETIF:
  

Ảnh chụp màn hình sau đây cho thấy công thức tuổi xuất sắc cuối cùng trong hành động - nó trả về tuổi trong nhiều năm, tháng và ngày, chỉ hiển thị các giá trị khác không:

Mẹo. Nếu bạn đang tìm kiếm một công thức Excel để tính tuổi theo năm và tháng, hãy lấy công thức trên và loại bỏ khối IF (datedif ()) cuối cùng tính toán ngày.

Các công thức cụ thể để tính tuổi trong excel

Cách tạo máy tính tuổi trong Excel

Bây giờ bạn đã biết cách tạo công thức tuổi trong Excel, bạn có thể xây dựng một máy tính tuổi tùy chỉnh, ví dụ như điều này:

Những gì bạn thấy ở trên là một bảng trực tuyến excel nhúng, vì vậy hãy thoải mái nhập ngày sinh của bạn vào ô tương ứng và bạn sẽ có được tuổi của mình trong giây lát.

Máy tính sử dụng các công thức sau để tính độ tuổi dựa trên ngày sinh trong ô A3 và ngày hôm nay.

• Công thức trong B5 tính toán tuổi theo năm, tháng và ngày: ________ 9
• Công thức trong B6 tính toán tuổi trong tháng: ________ 18
• Công thức trong B7 tính toán tuổi theo ngày: ________ 19

Nếu bạn có một số kinh nghiệm với các điều khiển biểu mẫu Excel, bạn có thể thêm một tùy chọn để tính toán tuổi vào một ngày cụ thể, như được hiển thị trong ảnh chụp màn hình sau:

Đối với điều này, thêm một vài nút tùy chọn (tab nhà phát triển> Chèn> Nút điều khiển biểu mẫu> nút tùy chọn) và liên kết chúng với một số ô. Và sau đó, hãy viết một công thức IF/datedif để có tuổi vào ngày hôm nay hoặc vào ngày được chỉ định bởi người dùng.

Công thức hoạt động với logic sau:

• Nếu hộp tùy chọn ngày hôm nay được chọn, giá trị 1 sẽ xuất hiện trong ô được liên kết (I5 trong ví dụ này) và công thức tuổi được tính toán dựa trên ngày hôm nay: ________ 20
• Nếu nút tùy chọn ngày cụ thể được chọn và ngày được nhập vào ô B7, tuổi được tính vào ngày được chỉ định: ________ 21

Cuối cùng, làm tổ các chức năng trên vào nhau và bạn sẽ có được công thức tính toán độ tuổi hoàn chỉnh (trong B9):

=YEARFRAC(B2, TODAY(), 1)2

Các công thức trong B10 và B11 hoạt động với cùng logic. Tất nhiên, chúng đơn giản hơn nhiều vì chúng chỉ bao gồm một hàm Datedif để trả về tuổi như số tháng hoàn thành hoặc ngày, tương ứng.

Để tìm hiểu các chi tiết, tôi mời bạn tải xuống máy tính excel Age này và điều tra các công thức trong các ô B9: B11.

Tải xuống máy tính tuổi cho Excel

Máy tính tuổi sẵn sàng sử dụng cho Excel

Người dùng của Ultimate Suite của chúng tôi không phải bận tâm đến việc tạo ra máy tính tuổi của họ trong Excel - nó chỉ là một vài cú nhấp chuột:

1. Chọn một ô nơi bạn muốn chèn một công thức tuổi, truy cập tab Công cụ ABleBits> Nhóm Ngày & Thời gian và nhấp vào nút Trình hướng dẫn ngày & giờ.Date & Time Wizard button.
   
2. Trình hướng dẫn ngày & giờ sẽ bắt đầu, và bạn trực tiếp đến tab tuổi.Age tab.
3. Trên tab Age, có 3 điều để bạn chỉ định:
   • Dữ liệu sinh là tham chiếu tế bào hoặc ngày ở định dạng MM/DD/YYYY.
   • Tuổi vào ngày hôm nay hoặc ngày cụ thể.
   • Chọn có tính tuổi theo ngày, tháng, năm hoặc tuổi chính xác.
4. Nhấp vào nút Chèn công thức.Insert formula button.

Done!

Công thức được chèn vào ô đã chọn trong giây lát và bạn nhấp đúp vào tay cầm điền để sao chép nó xuống cột.

Như bạn có thể nhận thấy, công thức được tạo ra bởi máy tính tuổi Excel của chúng tôi phức tạp hơn so với các công thức chúng tôi đã thảo luận cho đến nay, nhưng nó phục vụ cho các đơn vị thời gian số ít và số nhiều như "ngày" và "ngày".

Nếu bạn muốn loại bỏ các đơn vị bằng không như "0 ngày", hãy chọn hộp kiểm DO NotShow Zero đơn vị:Do not show zero units check box:

Nếu bạn tò mò kiểm tra máy tính tuổi này cũng như khám phá thêm 60 tiện ích bổ sung tiết kiệm thời gian cho Excel, bạn có thể tải xuống phiên bản dùng thử của bộ cuối cùng của chúng tôi vào cuối bài đăng này.

Làm thế nào để làm nổi bật một số độ tuổi nhất định (dưới hoặc trong một độ tuổi cụ thể)

Trong một số tình huống, bạn có thể không chỉ cần tính độ tuổi trong Excel, mà còn làm nổi bật các tế bào có độ tuổi dưới hoặc trong một độ tuổi cụ thể.

Nếu công thức tính toán tuổi của bạn trả về số năm hoàn chỉnh, thì bạn có thể tạo quy tắc định dạng có điều kiện thường xuyên dựa trên một công thức đơn giản như những thứ này:

• Để làm nổi bật các độ tuổi bằng hoặc lớn hơn 18: = $ c2> = 18
• Để làm nổi bật độ tuổi dưới 18: = $ C2

Trong đó C2 là ô trên cùng trong cột tuổi (không bao gồm tiêu đề cột).

Nhưng điều gì sẽ xảy ra nếu công thức của bạn hiển thị tuổi theo năm và tháng, hoặc trong nhiều năm, tháng và ngày thì sao? Trong trường hợp này, bạn sẽ phải tạo ra một quy tắc dựa trên một công thức Datedif tính tuổi từ ngày sinh trong các năm.

Giả sử các ngày sinh nằm trong cột B bắt đầu với hàng 2, các công thức như sau:

• Để làm nổi bật độ tuổi dưới 18 (Vàng): ________ 23under 18 (yellow):=YEARFRAC(B2, TODAY(), 1)3
• Để làm nổi bật độ tuổi từ 18 đến 65 (màu xanh lá cây): ________ 24between 18 and 65 (green):=YEARFRAC(B2, TODAY(), 1)4
• Để làm nổi bật độ tuổi trên 65 (màu xanh): ________ 25over 65 (blue):=YEARFRAC(B2, TODAY(), 1)5

Để tạo các quy tắc dựa trên các công thức trên, chọn các ô hoặc toàn bộ hàng mà bạn muốn làm nổi bật, hãy truy cập nhóm Tab Home> Styles và nhấp vào định dạng có điều kiện> Quy tắc mới> Sử dụng công thức để xác định định dạng ô nào.

Các bước chi tiết có thể được tìm thấy ở đây: Cách thực hiện quy tắc định dạng có điều kiện dựa trên công thức.

Đây là cách bạn tính toán tuổi trong Excel. Tôi hy vọng các công thức dễ dàng học hỏi và bạn sẽ thử chúng trong bảng tính của bạn. Cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Tải xuống có sẵn

Các ví dụ tính toán tuổi excel (tệp .xlsx) Ultimate Suite phiên bản đầy đủ chức năng 14 ngày (tệp .exe)
Ultimate Suite 14-day fully-functional version (.exe file)

Bạn cũng có thể quan tâm