Hướng dẫn cách xác định tuổi dựa vào ngày sinh trong Excel

Siêu Marketing sẽ hướng dẫn bạn cách để tính ra số tuổi dựa trên ngày sinh của một người sử dụng Excel qua bài viết này. Bạn sẽ học được cách sử dụng các công thức để xác định tuổi một cách chi tiết theo ngày, tháng, năm tới ngày hiện tại hoặc một ngày cụ thể khác, điều này sẽ hỗ trợ bạn tự học excel cho công việc văn phòng tại nhà hiệu quả nhất.

Mặc dù không có hàm riêng biệt để tính tuổi trong Excel, nhưng bạn vẫn có thể áp dụng nhiều phương pháp khác nhau. Bài viết này sẽ phân tích ưu điểm và hạn chế của các phương pháp đó và đề xuất giải pháp tối ưu cũng như cách áp dụng chúng vào thực tế công việc.

Cách tính tuổi trong Excel theo số năm đầy đủ

Mọi người thường quen với việc xác định tuổi qua số năm đã sống. Excel cũng có thể giúp bạn làm việc đó, không chỉ giới hạn ở số năm mà cả ngày, tháng, thậm chí là giờ và phút. Tuy nhiên, bây giờ chúng ta cùng nhau tìm hiểu cách truyền thống là tính tuổi theo số năm.

Công thức cơ bản trong Excel để tính số tuổi:

Cách bạn thường tính tuổi là gì? Chắc hẳn là lấy năm hiện tại trừ đi năm sinh. Chính xác là công thức này cũng được áp dụng tương tự trong Excel.

Giả sử B2 là ô chứa ngày tháng năm sinh, công thức bạn sẽ sử dụng là:

=(TODAY()-B2)/365

Công thức (TODAY()-B2) sẽ tính ra khoảng cách giữa ngày hiện tại và ngày sinh, sau đó bạn sẽ chia khoảng cách này cho 365 để thu về tuổi dưới dạng số năm.

Công thức trên tương đối đơn giản và dễ nhớ, nhưng vẫn có một nhược điểm nhỏ. Trong đa số trường hợp nó sẽ đưa ra kết quả dưới dạng số thập phân như minh họa sau đây.

Để biểu thị kết quả thành số năm tròn, bạn nên sử dụng hàm INT để loại bỏ phần thập phân.

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

Giới hạn của phương pháp:

Khi áp dụng công thức này, Excel mang lại kết quả gần đúng nhưng không hoàn toàn chính xác. Việc chia cho số ngày trung bình của một năm thường chính xác, tuy nhiên có thể xuất hiện sai số. Ví dụ, nếu ai đó sinh vào ngày 29/2 và ngày hôm nay là 28/2, công thức này sẽ khiến họ trở nên “già” thêm một tuổi.

Do đó, bạn cũng có thể chia cho 365,25 thay vì chỉ 365 để tính toán cho năm nhuận với 366 ngày. Tất nhiên, cách này cũng chưa phải là giải pháp tối ưu nhất. Chẳng hạn, với trẻ em chưa trải qua năm nhuận, việc chia cho 365,25 sẽ dẫn đến kết quả không đúng.

Kết luận là, việc lấy ngày tháng hiện tại trừ cho ngày sinh có thể chính xác trong cuộc sống thực, nhưng lại mang đến một số vấn đề khi thực hiện trong Excel. Phần tiếp theo sẽ giới thiệu với bạn các hàm chuyên biệt giúp tính tuổi chính xác cho mọi trường hợp.

ĐẠI DIỆN HÀM YEARFRAC ĐỂ TÍNH TUỔI:

Sử dụng hàm YEARFRAC trong Excel là cách đáng tin cậy hơn để xác định tuổi.

Hàm YEARFRAC có cú pháp như sau:

YEARFRAC(ngày_bắt_đầu, ngày_kết_thúc, [basis])

Từ định nghĩa:

  • Ngày_bắt_đầu: Là ngày khởi đầu
  • Ngày_kết_thúc: Là ngày chấm dứt
  • Basis: Là số chỉ định phương thức tính toán
  • Để tính tuổi, bổ sung thông tin sau vào hàm:
  • Ngày_bắt_đầu: Ngày sinh của bạn
  • Ngày_kết_thúc: Ngày đang xét
  • Basis: Đặt là 1 (nhằm yêu cầu Excel sử dụng số ngày thực tế của tháng và năm)

Chính vì thế, công thức để tính tuổi ra là:

YEARFRAC(ngày sinh, TODAY(), 1)

Đặt giả thiết ngày sinh được lưu ở ô B2, bạn sẽ thu được kết quả như sau:

Từ ví dụ đã xem xét, chúng ta thấy rằng YEARFRAC trả về một con số dạng thập phân. Chúng ta có thể chuyển đổi nó sang số nguyên bằng cách sử dụng hàm ROUNDDOWN và bổ sung số 0 vào cuối công thức như đã nói.

CÁCH DÙNG HÀM DATEIF ĐỂ TÍNH TUỔI TRONG EXCEL:

Một phương pháp khác để tính toán tuổi chính là sử dụng hàm DATEIF như sau:

DATEDIF(start_date, end_date, unit)

Hàm này sẽ cung cấp kết quả là khoảng thời gian giữa hai ngày theo đơn vị là ngày, tháng hay năm tùy bạn chọn định dạng nào.

  • Y – Khoảng thời gian tính bằng năm tròn giữa hai ngày.
  • M – Khoảng thời gian tính bằng tháng tròn giữa hai ngày.
  • D – Khoảng thời gian tính bằng ngày tròn giữa hai ngày.
  • YM – Số tháng lệch, không tính ngày và năm.
  • MD – Số ngày lệch, không tính tháng và năm.
  • YD – Số ngày lệch, không tính theo năm.

Với việc tính tuổi theo năm, chúng ta nên chọn đơn vị “Y”.

DATEDIF(date of birth, TODAY(), “y”)

Cụ thể, nếu ngày sinh nhật ở trong ô B2, ta sẽ nhận được kết quả như sau:

Không cần phép làm tròn nào ở đây vì hàm DATEIF đã tính toán theo năm tròn.

CÁCH TÍNH CHÍNH XÁC TUỔI THEO NGÀY, THÁNG, NĂM:

Như bạn có thể đã biết, để tính số tuổi của một người theo năm tròn thì khá đơn giản nhưng đôi khi lại không đủ. Nếu muốn xác định chính xác đến từng ngày, tháng, năm tuổi, bạn có thể sử dụng ba hàm DATEIF riêng biệt:

  1. Để lấy số năm: =DATEDIF(B2, TODAY(), “Y”)
  2. Để lấy số tháng: =DATEDIF(B2, TODAY(), “YM”)
  3. Để lấy số ngày: =DATEDIF(B2,TODAY(),”MD”)

Trong đó B2 là ô chứa ngày sinh.

Bạn có thể kết hợp những hàm này vào một công thức như sau:

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

Công thức này sẽ tạo ra một chuỗi số cho ngày, tháng, năm như hình dưới đây.

Để kết quả được trình bày rõ ràng hơn, bạn nên phân tách các giá trị bằng dấu phẩy và ghi rõ từng số liệu tương ứng:

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

Xem thêm: Tài liệu tổng hợp hướng dẫn sử dụng Excel cấp độ nâng cao mới nhất

Công thức có thể cải thiện hơn nữa khi chúng ta giấu đi các giá trị bằng 0. Áp dụng ba điều kiện IF để kiểm tra từng hàm DATEIF:

=IF(DATEDIF(B2, TODAY(),”y”)=0,””,DATEDIF(B2, TODAY(),”y”)&” years, “)&

IF(DATEDIF(B2, TODAY(),”ym”)=0,””,DATEDIF(B2, TODAY(),”ym”)&” months, “)&

IF(DATEDIF(B2, TODAY(),”md”)=0,””,DATEDIF(B2, TODAY(),”md”)&” days”)

Ảnh dưới đây minh họa kết quả của công thức đã loại bỏ những giá trị không có:

Một mẹo nhỏ: Nếu bạn muốn tính tuổi chỉ theo ngày và tháng, sử dụng công thức trên nhưng loại bỏ điều kiện IF(DATEDIF()) cuối cùng.

THÍ DỤ MINH HỌA TÍNH TUỔI TRONG EXCEL:

Công thức

Phần lớn các trường hợp có thể sử dụng cách tính tuổi như đã nêu trong ví dụ trước. Tuy nhiên, đôi khi chúng ta cần thông tin dựa trên điều kiện cụ thể, dù không lúc nào cũng áp dụng được. Dưới đây là các ví dụ để hỗ trợ bạn tính toán tuổi dựa vào công thức đã cho.

Xác định tuổi theo ngày sinh cụ thể đến một thời điểm xác định

Khi bạn muốn tính tuổi của một người từ ngày tháng năm sinh của họ cho đến một ngày nhất định chứ không phải ngày hiện tại, hãy áp dụng hàm DATEDIF như đã được hướng dẫn, nhưng thay “TODAY()” thành ngày bạn cần.

Xét ngày sinh được ghi tại ô B1 và bạn muốn tính tuổi đến ngày 1/1/2020:

=DATEDIF(B1, “1/1/2020″,”Y”) & ” tuổi, ” & DATEDIF(B1, “1/1/2020″,”YM”) & ” tháng, ” & DATEDIF(B1, “1/1/2020”, “MD”) & ” ngày”

Bạn cũng có thể nhập ngày cần thiết vào một ô rồi gắn ô đó vào công thức, để tính tuổi trở nên linh động hơn:

=DATEDIF(B1, B2,”Y”) & ” tuổi, “& DATEDIF(B1,B2,”YM”) & ” tháng, “&DATEDIF(B1,B2, “MD”) & ” ngày”

Trong trường hợp này, ô B1 chứa ngày sinh và ô B2 chứa ngày bạn muốn tính tuổi tới:

 

THỜI ĐIỂM CUỐI KHÔNG RÕ RÀNG KHI TÍNH TUỔI:

Công thức được áp dụng để tính tuổi ngay cả khi bạn chỉ biết năm kết thúc mà không cần ngày, tháng cụ thể.

Trong tình huống bạn đang làm việc với dữ liệu của cơ sở y tế và mục tiêu là xác định tuổi của bệnh nhân tại thời điểm họ thăm viện lần cuối.

Ví dụ, ngày sinh được lưu tại cột B từ dòng 3 trở đi và năm khám bệnh lần cuối được ghi chép ở cột C, công thức để tính tuổi sẽ là:

=DATEDIF(B3,DATE(C3, 1, 1),”y”)

Bởi vì không rõ ngày tháng của việc khám cuối, hàm DATE được sử dụng để giả định ngày và tháng mặc định, với vẻ ngoài như sau: DATE(C3, 1, 1).

Chức năng của hàm DATE là lấy năm từ ô B3 và thêm ngày, tháng mặc định cho năm được cung cấp, chẳng hạn 1/1, sau đó đưa vào hàm DATEDIF. Từ đó ta có thể tính được tuổi của bệnh nhân tới ngày đầu tiên của một năm cụ thể.

TÍNH NGÀY ĐẠT TUỔI CỤ THỂ CHO MỘT NGƯỜI:

Giả định một người có ngày sinh là 8/3/1978, làm sao để biết ngày họ tròn 50 tuổi? Cách thông thường là cộng thêm 50 năm vào ngày sinh của họ. Trong Excel, chúng ta thực hiện điều này bằng hàm DATE.

=DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2))

Với ô A2 chứa thông tin ngày sinh.

Thay vì phải tính toán và nhập số năm chính xác, bạn có thể chỉ cần tham chiếu đến một ô bất kỳ chứa ngày tháng đó.

KHI NGÀY, THÁNG, NĂM SINH ĐƯỢC CHỨA TẠI CÁC Ô KHÁC NHAU VÀ BẠN CẦN TÍNH TUỔI:

Trong trường hợp ngày, tháng và năm sinh được ghi ở ba ô khác nhau, ví dụ như năm ở ô B3, tháng ở ô C3 và ngày tại ô D3, bạn có thể tiến hành tính tuổi như sau:

Bạn kết hợp ngày tháng năm sinh sử dụng hàm DATE cùng với DATEVALUE

DATE(B3,MONTH(DATEVALUE(C3&”1″)),D3)

Sử dụng công thức này làm phần của hàm DATEDIF để có thể tính tuổi:

THIẾT LẬP CÁC TÍNH NĂNG MÁY TÍNH TUỔI TRONG EXCEL:

Nắm vững cách thức công thức tính tuổi, bạn có thể xây dựng một máy tính tuổi cho riêng mình như thế này.

Máy tính này được tạo nên từ việc sử dụng những công thức dưới đây để tính tuổi dựa trên ngày sinh sinh ở ô A3 và ngày hiện tại.

Ô B5 được sử dụng để tính tuổi dựa trên ngày, tháng và năm với công thức sau:

=DATEDIF(B2,TODAY(),”Y”) & ” tuổi,“`

Đoạn mã sau đóng vai trò tính số năm, tháng và ngày:

” & DATEDIF(B2,TODAY(),”YM”) & ” Tháng, ” & DATEDIF(B2,TODAY(),”MD”) & ” Ngày”

Ở ô B6, công thức dùng để thực hiện tính tháng:

=DATEDIF($B$3,TODAY(),”m”)

Đối với ô B7, đây là công thức dùng để tính ra ngày:

=DATEDIF($B$3,TODAY(),”d”)

Đối với bạn nào đã có kinh nghiệm với Bảng Tính Excel, việc hoàn thiện một máy tính tuổi đơn giản bằng cách cho phép người dùng tính toán tuổi đến một ngày cụ thể là điều có thể làm được, như trong hình minh họa dưới đây:

Để xây dựng một máy tính tuổi thông minh bằng Excel, hãy vào Developer tab > Insert > Form controls > Option Button và kết nối nó với một ô. Sau đó, bạn tạo một công thức kết hợp các hàm IF và DATEIF để có thể tính tuổi cho tới ngày hiện tại hoặc một ngày khác tuỳ ý. Công thức sẽ hoạt động theo cách sau:

Nếu lựa chọn Today’s date được chọn, 1 sẽ được hiển thị ở ô liên kết (chẳng hạn như I5), công thức sẽ tính tuổi như sau:

IF($I$5=1, DATEDIF($B$3,TODAY(),”Y”) & ” Năm, ” & DATEDIF($B$3,TODAY(),”YM”) & ” Tháng, ” & DATEDIF($B$3,TODAY(),”MD”) & ” Ngày”)

Ngược lại, nếu lựa chọn là Specific date và một ngày cụ thể đã được nhập vào ô B7, tuổi sẽ được tính cho tới ngày đó:

IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,”Y”) & ” Năm, ” & DATEDIF($B$3, $B$7,”YM”) & ” Tháng, ” & DATEDIF($B$3, $B$7,”MD”) & ” Ngày”, “”))

Ở giai đoạn cuối cùng, bạn sẽ lồng các hàm này vào với nhau, và công thức hoàn chỉnh để tính tuổi sẽ được đặt trong ô B9:

=IF($I$5=1,DATEDIF($B$3,TODAY(),”Y”) & ” Năm, ” & DATEDIF($B$3,TODAY(),”YM”) & ” Tháng, ” & DATEDIF($B$3,TODAY(),”MD”) & ” Ngày”, IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,”Y”) & ” Năm, ” & DATEDIF($B$3, $B$7,”YM”) & ” Tháng, ” & DATEDIF($B$3, $B$7,”MD”) & ” Ngày”, “”))

Các công thức đặt trong ô B10 và B11 cũng sẽ hoạt động theo cách tương tự nhưng đơn giản hơn do chúng chỉ cần tính toàn bộ tháng hoặc ngày.

TÔ MÀU TUỔI :

Ví dụ trong một số trường hợp, ngoài việc tính tuổi, bạn còn muốn làm nổi bật hoặc đánh dấu những cá nhân có độ tuổi ở một ngưỡng nào đó.

Lấy một ví dụ, nếu bạn muốn đánh dấu tuổi từ 18 trở lên, bạn có thể sử dụng công thức sau:

Tô màu những ai từ 18 tuổi trở lên: =$C2>=18

Tô màu những ai dưới 18 tuổi: =$C2<18

Ở đây C2 chính là ô chứa thông tin tuổi ở cột Age.

Nhưng nếu tuổi được xuất ra dưới dạng kết hợp năm, tháng hoặc năm, tháng, ngày thì sao? Trường hợp này yêu cầu bạn cần tạo các điều kiện dựa trên hàm DATEIF giúp tính tuổi ra số năm.

Giả định thông tin ngày sinh nằm tại cột B từ dòng 2, công thức cho việc này sẽ là:

Tô màu vàng cho những ai dưới 18 tuổi:

=DATEDIF($B2, TODAY(),”Y”)<18

Tô màu xanh cho những ai từ 18 đến 65 tuổi:

=AND(DATEDIF($B2, TODAY(),”Y”)>=18, DATEDIF($B2, TODAY(),”Y”)<=65)

Tô màu lam cho những ai trên 65 tuổi:

=DATEDIF($B2, TODAY(),”Y”)>65

Để thiết lập các điều kiện sử dụng các công thức như trên, bạn chọn ô hoặc dãy ô muốn đánh dấu, truy cập Home tab > Styles group > Conditional Formatting > New Rule… > Use a formula to determine which cells to format.

Để áp dụng thành thạo Excel trong công việc, chúng ta không chỉ cần biết cách sử dụng các hàm phức tạp như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH mà còn phải thành thạo cách dùng các công cụ khác của Excel như Data validation, Conditional formatting, Pivot table…

Mọi kiến thức này bạn hoàn toàn có thể học được trong khóa học SM90 – Excel từ cơ bản đến chuyên gia của Siêu Marketing. Hiện tại, đang có ưu đãi lớn cho những người đăng ký khóa học. Xem chi tiết tại địa chỉ: sieumarketing.com

“`

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop