Trong bài viết này, Blog học Excel Online sẽ giúp bạn tính tuổi từ ngày sinh trong Excel. Bạn sẽ được học các công thức tính tuổi dựa vào ngày sinh cũng như biết được tuổi chính xác theo ngày, tháng, năm tính đến ngày hôm nay hoặc 1 ngày khác giúp bạn có thể tự học excel văn phòng tại nhà một cách hiệu quả nhất.
Tuy không có 1 hàm đặc biệt để tính tuổi, nhưng có nhiều cách khác nhau để thực hiện yêu cầu. Bài viết sẽ giải thích ưu, khuyết điểm của những cách này, đồng thời đưa ra phương án tốt nhất và cách ứng dụng vào các công việc cụ thể.
Tính tuổi trong Excel (theo năm tròn)
Trong cuộc sống hằng ngày, câu hỏi “Bạn bao nhiêu tuổi?” ám chỉ số năm bạn đã sống trên đời. Nhưng với Excel, bạn có thể tạo công thức để tính chính xác số tuổi ra ngày, tháng, năm, thậm chí là giờ, phút. Nhưng chúng ta sẽ theo cách truyền thống, tính tuổi ra số năm trước.
Công thức Excel đơn giản để tính số năm tuổi:
Bình thường, bạn tính số tuổi như thế nào? Đơn giản là trừ năm hiện tại cho năm sinh. Công thức này cũng dùng cho Excel.
Giả sử ngày tháng năm sinh ở ô B2, công thức tính tuổi như sau:
=(TODAY()-B2)/365
Phần đầu của công thức (TODAY()-B2) trả hiệu giữa ngày tháng năm hiện tại và ngày tháng năm sinh, rồi bạn chia con số cho 365 để có số năm tuổi.
Công thức trên rất rõ ràng và dễ nhớ, tuy nhiên, có 1 vấn đề nhỏ. Với phần lớn trường hợp, nó sẽ trả kết quả dạng thập phân như trong hình dưới.
Để thể hiện kết quả ra năm tròn, dùng hàm INT để làm tròn số thập phân.
=INT((TODAY()-B2)/365) Hạn chế:
Dùng công thức này trong Excel cho ra kết quả tương đối chính xác, nhưng không phải không sai. Chia cho số ngày trung bình trong 1 năm đúng trong hầu hết mọi trường hợp, nhưng đôi khi cho ra kết quả sai. Ví dụ, nếu có người sinh ngày 29/2 và hôm nay là 28/2, công thức sẽ khiến người đó già hơn 1 tuổi.
Do đó, bạn có thể chia cho 365,25 thay vì 365 vì cứ 4 năm lại có 1 năm nhuận 366 ngày. Tuy nhiên, cách này cũng chưa phải tốt nhất. Ví dụ, nếu bạn tính tuổi 1 em bé chưa sống qua năm nhuận, chia cho 365,25 sẽ cho ra kết quả sai.
Tóm lại, lấy ngày tháng năm sinh hiện tại trừ đi ngày tháng năm sinh đúng với cuộc sống bình thường, nhưng gây ra vấn đề trong Excel. Ở các phần tiếp theo, bạn sẽ học các hàm đặc biệt giúp tính tuổi chính xác với bất kì năm nào.
TÍNH TUỔI VỚI HÀM YEARFRAC:
Một cách tính tuổi đáng tin cậy hơn trong Excel là dùng hàm YEARFRAC.
Cú pháp hàm YEARFRAC:
YEARFRAC(start_date, end_date, [basis])
Trong đó:
Start_date: Ngày bắt đầu
End_date: Ngày kết thúc
Basis: Một con số quy định kiểu tính
Để tính tuổi, thêm thông tin sau vào hàm:
Start_date: Ngày sinh
End_date: Ngày hiện tại
Basis: 1 (yêu cầu Excel tính theo số ngày thực tế của tháng và số ngày thực tế của năm).
Từ đó, có thể suy ra công thức tính tuổi như sau:
YEARFRAC(date of birth, TODAY(), 1)
Giả sử ngày sinh ở trong ô B2, kết quả sẽ như sau:
Như ở trong ví dụ trước, kết quả hàm YEARFRAC là số thập phân. Để chuyển thành số nguyên, dùng hàm ROUNDDOWN, thêm số 0 vào cuối công thức trên.
TÍNH TUỔI TRONG EXCEL VỚI HÀM DATEIF:
Một cách khác để tính tuổi là dùng hàm DATEIF:
DATEDIF(start_date, end_date, unit)
Hàm này trả kết quả là thời gian giữa 2 ngày tính theo ngày, tháng hoặc năm tuỳ theo đơn vị mà bạn chọn.
Y – thời gian tính theo năm tròn giữa 2 ngày.
M – thời gian tính theo tháng tròn giữa 2 ngày.
D – thời gian tính theo ngày tròn giữa 2 ngày.
YM – số tháng chênh lệch, bỏ qua ngày, năm.
MD – Số ngày chênh lệch, bỏ qua tháng, năm.
YD – Số ngày chênh lệch, bỏ qua năm.
Vì chúng ta tính tuổi theo năm, dùng “Y”.
DATEDIF(date of birth, TODAY(), “y”)
Ví dụ, ngày sinh trong ô B2, kết quả như sau:
Không cần làm tròn vì trong hàm DATEIF, đơn vị năm tính theo năm tròn.
CÁCH TÍNH TUỔI RA NGÀY THÁNG NĂM CHÍNH XÁC:
Như bạn vừa thấy, tính tuổi của 1 người ra năm tròn rất dễ nhưng không phải lúc nào cũng đủ. Nếu bạn muốn biết chính xác số tuổi, bao nhiêu ngày, tháng, năm, hãy viết ra 3 hàm DATEIF khác nhau:
Để có số năm: =DATEDIF(B2, TODAY(), “Y”)
Để có số tháng: =DATEDIF(B2, TODAY(), “YM”)
Để có số ngày: =DATEDIF(B2,TODAY(),”MD”)
Trong đó B2 là ô có chứa ngày tháng năm sinh.
Và nối các hàm vào trong 1 công thức, như sau:
=DATEDIF(B2,TODAY(),”Y”) & DATEDIF(B2,TODAY(),”YM”) & DATEDIF(B2,TODAY(),”MD”)
Công thức trên sẽ đưa các con số ngày, tháng, năm về 1 chuỗi như hình dưới.
Để kết quả rõ ràng hơn, tách chúng bằng dấu phẩy và định nghĩa các con số đó là gì:
Công thức trên có thể hoàn hảo hơn nếu các giá trị bằng 0 được giấu đi. Thêm 3 mệnh đề IF để kiểm tra cho mỗi công thức 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”)
Hình dưới minh hoạ kết quả công thức trên – không còn các giá trị 0.
Mẹo nhỏ. Nếu bạn chỉ cần tính tuổi theo ngày, tháng, dùng công thức trên và bỏ mệnh đề IF(DATEDIF()) cuối cùng.
VÍ DỤ TÍNH TUỔI TRONG EXCEL:
Công thức tính tuổi ở ví dụ trên đúng với hầu hết các trường hợp. Nhưng đôi khi bạn cần thông tin rất cụ thể, tuy không phải trong mọi trường hợp. Các ví dụ sau sẽ giúp bạn dựa trên công thức tính tuổi, thực hiện các bài toán tương tự.
Tính tuổi từ ngày tháng năm sinh đến một ngày cụ thể
Nếu bạn cần tính tuổi từ ngày tháng năm sinh đến một ngày cụ thể, không phải đến ngày hôm nay, dùng hàm DATEIF như trên, thay TODAY() bằng ngày cụ thể.
Giả sử ngày sinh trong ô B1, cần tính tuổi đến ngày 1/1/2000:
=DATEDIF(B1, “1/1/2020″,”Y”) & ” Years, ” & DATEDIF(B1, “1/1/2020″,”YM”) & ” Months, ” & DATEDIF(B1, “1/1/2020”, “MD”) & ” Days”
Để công thức tính tuổi linh hoạt hơn, bạn có thể nhập ngày vào 1 ô và thêm ô đó vào công thức:
=DATEDIF(B1, B2,”Y”) & ” Years, “& DATEDIF(B1,B2,”YM”) & ” Months, “&DATEDIF(B1,B2, “MD”) & ” Days”
Khi B1 chứa ngày sinh, B2 chứa ngày mà bạn muốn tính tuổi đến:
THỜI GIAN TÍNH TUỔI KHÔNG CỤ THỂ:
Công thức tính tuổi vẫn có thể áp dụng ngay cả khi thời gian kết thúc chỉ có năm, không rõ ngày tháng.
Giả sử bạn làm việc với cơ sở dữ liệu y tế, mục tiêu của bạn là tìm ra số tuổi của bệnh nhân vào lần cuối họ khi khám bệnh.
Giả sử ngày sinh của họ ở cột B, bắt đầu từ hàng 3, và năm bệnh nhân khám lần cuối ở cột C, công thức tính tuổi như sau:
=DATEDIF(B3,DATE(C3, 1, 1),”y”)
Vì ngày khám bệnh lần cuối không rõ, bạn dùng hàm DATE với cách tính ngày tháng mặc định, ví dụ: DATE(C3, 1, 1).
Hàm DATE trừ năm từ ô B3, mặc định thêm ngày, tháng cho năm bạn cung cấp (ví dụ: 1/1) và đưa ngày tháng năm cụ thể vào hàm DATEIF. Kết quả là bạn có tuổi bệnh nhân tính đến ngày 1/1 của năm nào đó.
TÌM NGÀY KHI 1 NGƯỜI ĐẠT BAO NHIÊU TUỔI:
Giả sử 1 người sinh năm 8/3/1978, làm thế nào để biết đến ngày nào anh ta 50 tuổi? Thông thường, bạn thêm 50 năm vào ngày tháng năm sinh của người đó. Trong Excel, bạn dùng hàm DATE.
=DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2))
A2 là ô chứa ngày sinh.
Thay vì phải nhập năm chính xác vào công thức, bạn có thể dẫn tới 1 ô bất kì có chứa ngày tháng năm đó.
TÍNH TUỔI VỚI NGÀY, THÁNG, NĂM SINH Ở CÁC Ô KHÁC NHAU:
Khi ngày tháng năm sinh ở 3 ô khác nhau (ví dụ năm ở ô B3, tháng ở ô C3 và ngày ở D3), bạn có thể tính tuổi theo cách này:
Lấy ngày tháng năm sinh bằng cách dùng hàm DATE và DATEVALUE
DATE(B3,MONTH(DATEVALUE(C3&”1″)),D3)
Thêm công thức trên vào hàm DATEIF để tính tuổi:
TẠO MÁY TÍNH TUỔI TRONG EXCEL
Khi bạn đã biết công thức tính tuổi, bạn có thể tạo máy tính tuổi của riêng mình như sau.
Với máy tính này, chúng ta dùng những công thức sau để tính tuổi dựa trên ngày sinh ở ô A3 và ngày hiện nay.
Công thức ở ô B5 tính tuổi theo ngày, tháng, năm:
=DATEDIF(B2,TODAY(),”Y”) & ” Years, ” & DATEDIF(B2,TODAY(),”YM”) & ” Months, ” & DATEDIF(B2,TODAY(),”MD”) & ” Days”
Công thức ở ô B6 tính tháng:
=DATEDIF($B$3,TODAY(),”m”)
Công thức ở ô B7 tính ngày:
=DATEDIF($B$3,TODAY(),”d”)
Nếu bạn đã làm Excel Form, bạn có thể cải thiện máy tính tuổi đơn giản này bằng cách cho phép người sử dụng tính tuổi đến 1 ngày cụ thể, như hình dưới:
Nếu hộp Today’s date được chọn, giá trị 1 xuất hiện ở ô được liên kết (ví dụ I5), và công thức tính tuổi như sau:
IF($I$5=1, DATEDIF($B$3,TODAY(),”Y”) & ” Years, ” & DATEDIF($B$3,TODAY(),”YM”) & ” Months, ” & DATEDIF($B$3,TODAY(),”MD”) & ” Days”)
Nếu hộp Specific date được chọn, và 1 ngày cụ thể được nhập vào ô B7, tuổi được tính đến ngày đó:
IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,”Y”) & ” Years, ” & DATEDIF($B$3, $B$7,”YM”) & ” Months, ” & DATEDIF($B$3, $B$7,”MD”) & ” Days”, “”))
Cuối cùng, bạn lồng các hàm vào nhau, và công thức tính tuổi được hoàn thành (trong ô B9):
=IF($I$5=1,DATEDIF($B$3,TODAY(),”Y”) & ” Years, ” & DATEDIF($B$3,TODAY(),”YM”) & ” Months, ” & DATEDIF($B$3,TODAY(),”MD”) & ” Days”, IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,”Y”) & ” Years, ” & DATEDIF($B$3, $B$7,”YM”) & ” Months, ” & DATEDIF($B$3, $B$7,”MD”) & ” Days”, “”))
Công thức ở ô B10 và B11 cũng hoạt động tương tự. Dĩ nhiên sẽ đơn giản hơn vì hàm DATEIF chỉ cần trả giá trị là tháng tròn, ngày tròn.
Trong một số trường hợp, bạn không chỉ cần tính tuổi mà cần đánh dấu ô chứa số tuổi trên hoặc dưới 1 mức nào đó.
Nếu công thức tính tuổi cho ra số năm tuổi tròn, bạn có thể thêm công thức điều kiện như thế này:
Đánh dấu số tuổi nhỏ hơn 18: =$C2<18
C2 là ô trên cùng trong cột Age
Nhưng khi công thức cho ra tuổi ở dạng tháng, năm hoặc ngày, tháng, năm? Trong trường hợp đó, bạn phải tạo điều kiện dựa trên công thức DATEIF (để tính tuổi ra số năm).
Giả sử ngày tháng năm sinh ở cột B từ hàng 2, công thức như sau:
Đánh dấu số tuổi nhỏ hơn 18 (vàng)
=DATEDIF($B2, TODAY(),”Y”)<18
Đánh dấu số tuổi từ 18 đến 65 (xanh)
Để biết thêm thông tin chi tiết về khóa học excel cơ bản tại trung tâm blog.hocexcel.online với các chuyên gia hàng đầu Việt Nam đã có nhiều năm kinh nghiệm sẽ hướng dẫn cho các bạn từng chi tiết về Excel giúp chúng ta có thể sử dụng thành thạo Excel để áp dụng trong học tập cũng như trong công việc. Với mức giá phải chăng thì chúng tôi đã và đang đào tạo rất nhiều học viên đăng kí với những khóa học về Excel, vậy còn trừng trừ gì nữa mà không đăng ký ngay cho mình những khóa học bổ ích.