Bạn đã bao giờ cần phải tính tuổi của một ai đó trong Excel chưa? Trong bài viết này, chúng ta sẽ tìm hiểu về cách tính số tuổi trong Excel từ ngày tháng năm sinh và giải quyết các lỗi phổ biến trong quá trình tính toán. Hãy cùng khám phá từng phần dưới đây.
TÓM TẮT
Tính tuổi trong Excel là gì?
🔎 Xem thêm
Trước khi đi vào việc tính tuổi trong Excel, hãy hiểu rõ khái niệm “tính tuổi trong Excel” là gì. Đơn giản, tính tuổi trong Excel là để xác định số tuổi của một người dựa trên ngày, tháng và năm sinh. Excel cung cấp các hàm tính toán và công thức để giúp bạn thực hiện việc này một cách dễ dàng và nhanh chóng.
Cách tính số tuổi trong Excel theo năm
Đầu tiên, chúng ta sẽ tìm hiểu cách tính tuổi trong Excel theo năm. Dưới đây là hai phương pháp để xác định số tuổi của một người trong Excel theo năm.
Đếm số tuổi theo năm bằng hàm YEAR
Phương pháp này nhanh chóng với công thức đơn giản. Tuy nhiên, cách này sẽ không tính được tuổi chính xác theo ngày tháng.
Công thức tính tuổi bằng hàm YEAR:
=YEAR(NOW())-YEAR()Trong đó:
- YEAR(NOW()): Là năm hiện tại.
- YEAR(): Là năm của ngày tháng năm sinh.
Bạn có thể xem ví dụ sau để hiểu rõ hơn về cách tính tuổi trong Excel bằng hàm Year:
- Đầu tiên, bạn cần có một bảng dữ liệu để tính toán. Tại vị trí cần tính tuổi, bạn nhập công thức
=YEAR(NOW())-YEAR(B2).
- Sau khi nhập xong, bạn nhấn Enter và kiểm tra kết quả trả về.
Nếu bạn thắc mắc vì sao kết quả tuổi không phải là số nguyên mà hiển thị dưới dạng chuỗi, thì đó là do ô đã được định dạng sẵn thành kiểu “Date”. Bạn cần chuyển về dạng số nguyên theo định dạng “Number”:
- Bạn bôi đen vùng chứa kết quả sau đó nhấp chuột phải chọn “Format Cells…”
- Trong hộp thoại “Format Cells”, bạn click vào “Number”. Tại mục “Decimal places” bạn nhập số “0” (vì tuổi là số nguyên) rồi ấn “OK”.
- Sau khi định dạng xong, bạn nhập lại công thức như trên rồi ấn Enter là xong.
Để không phải nhập lại công thức cho các ô khác, bạn chỉ cần đặt con trỏ chuột vào góc dưới của ô chứa kết quả sao cho xuất hiện dấu cộng. Sau đó bạn kéo chuột xuống đến hết vị trí cần tính tuổi là được.
Đếm số tuổi theo năm bằng hàm YEARFRAC
Một cách khác để chuyển từ năm sinh sang tuổi trong Excel là sử dụng hàm YEARFRAC. Hàm này sẽ cho ra kết quả chính xác hơn so với hàm YEAR. Tuy nhiên, nó ít được biết đến và chưa thực sự phổ biến.
Công thức tính tuổi bằng hàm YEARFRAC:
=YEARFRAC([start_date],[end_date],[basis])Trong đó:
- Start_date: Ngày tháng năm sinh cần xác định tuổi.
- End_date: Thường sử dụng hàm TODAY() để trả về ngày hôm nay.
- Basis: Một con số quy định kiểu tính, thường sẽ sử dụng đối số là 1.
Bạn có ví dụ về cách sử dụng hàm YEARFRAC như sau:
- Đầu tiên, bạn cần tính tuổi của ô B2, nên Start date sẽ là dữ liệu trong ô B2 và End day là dữ liệu trả về ngày hôm nay. Từ đó ta có công thức cụ thể là:
=B2-TODAY()
Cách tính số tuổi trong Excel theo ngày
Bên cạnh tính tuổi theo năm, chúng ta cũng có thể tính tuổi theo ngày với 2 công thức hàm TODAY và DATEDIF.
Tính số tuổi trong Excel bằng hàm TODAY
Hàm TODAY là một trong những công thức tính tuổi đơn giản nhất trong Excel. Hàm tính tuổi này được tính bằng cách lấy thời gian hiện tại trừ đi ngày sinh trong một ô dữ liệu.
Công thức tính tuổi bằng hàm TODAY:
=(TODAY()-X)/[số ngày trong năm]Trong đó:
- TODAY()-X: Lấy ngày hôm nay trừ đi ngày tháng năm sinh trong ô X.
- /số ngày trong năm: Chia cho 365 ngày hay 1 năm.
Áp dụng vào dữ liệu thực tế ta tính được tuổi như sau:
- Bạn nhập công thức vào ô cần tính
(TODAY()-B3)/365.
- Bạn nhấn Enter và nhận kết quả.
Lưu ý: Công thức hàm TODAY này sẽ đúng trong hầu hết trường hợp, nhưng cũng có trường hợp ngoại lệ cho kết quả sai:
- Nếu người cần tính tuổi sinh ngày 29/2 và hôm nay là 28/2 thì sẽ cho ra kết quả khiến người đó già hơn 1 tuổi.
- Bạn có thể chia cho 365,25 thay vì 365 ngày như thông thường. Bởi vì theo lịch thì cứ 4 năm sẽ có 1 năm nhuận 366. Tuy vậy, cách chia cho 365,25 cũng có thể cho kết quả sai nếu bạn tính tuổi của em bé chưa qua 1 kỳ năm nhuận.
Tính tổng số tuổi theo ngày bằng hàm DATEDIF
DATEDIF là một hàm giúp bạn tính chính xác số tuổi một cách nhanh chóng. Tuy nhiên, hàm này có công thức tính khá phức tạp.
Công thức tính tuổi bằng hàm DATEDIF:
=DATEDIF(start_date, end_date, unit)Khi dùng DATEDIF, hàm trả về kết quả là sự chênh lệch giữa 2 giá trị thời gian được tính theo ngày, tháng, năm hoặc tùy thuộc vào đơn vị bạn chọn. Có các đơn vị tính thường được sử dụng bao gồm:
- Y: Thời gian được xác định theo năm tròn giữa 2 ngày
- M: Thời gian được xác định theo tháng tròn giữa 2 ngày
- D: Thời gian được xác định theo ngày tròn giữa 2 ngày
- YM: Trả về số tháng chênh lệch, không tính ngày và năm
- MD: Trả về số ngày chênh lệch, không tính tháng và năm
- YD: Trả về số ngày chênh lệch, không tính năm
Trong phần này, mình cần tính tuổi theo năm nên sẽ chọn đơn vị là Y.
Ví dụ, trong bảng tính ta có ngày tháng năm sinh nằm ở ô B3.
- Bạn nhập công thức tại ô cần xác định tuổi:
=DATEDIF(B3,TODAY(), 'y').
- Bạn ấn Enter để nhận về kết quả.
Như vậy, với 2 hàm đơn giản ở trên, bạn đã nhanh chóng tính được tuổi của một người theo năm rồi.
Một số trường hợp tính số tuổi trong Excel khác
Ngoài các phương pháp trên, ta còn có một số trường hợp đặc biệt khi tính tuổi trong Excel.
Công thức tính số tuổi ra ngày tháng năm sinh chính xác
Đôi khi, bạn muốn biết chính xác số tuổi, số ngày, tháng, năm của một người trong Excel. Bạn cần sử dụng hàm DATEDIF với 3 đối số khác nhau:
- Để có số năm:
=DATEDIF(B3,TODAY(),“Y”) - Để có số tháng:
=DATEDIF(B3,TODAY(),“YM”) - Để có số ngày:
=DATEDIF(B3,TODAY(),”MD”)
Trong đó, B3 là ô chứa ngày tháng năm sinh cần xác định tuổi.
Áp dụng vào ví dụ thực tế, bạn thực hiện như sau:
- Bạn nối 3 hàm trên và nhập vào ô tính thành một công thức:
=DATEDIF(B3,TODAY(),“Y”) & DATEDIF(B3,TODAY(),“YM”) & DATEDIF(B3,TODAY(),“MD”).
- Bạn nhấn Enter, kết quả sẽ hiển thị ở dạng chuỗi như hình.
Để kết quả rõ nghĩa, bạn cần định nghĩa các con số đó theo công thức sau:
=DATEDIF(B3,TODAY(),“Y”) & ” Years, ” & DATEDIF(B3,TODAY(),“YM”) & ” Months, ” & DATEDIF(B3,TODAY(),“MD”) & ” Days”
Bạn ấn Enter để nhận tuổi chính xác theo ngày tháng năm sinh.
Nếu bạn chỉ cần xác định tuổi theo ngày, tháng, bạn vẫn dùng công thức trên nhưng cần bỏ mệnh đề DATEDIF cuối cùng đi.
Thời gian tính tuổi không cụ thể
Công thức tính tuổi trong Excel vẫn có thể áp dụng ngay cả khi thời gian kết thúc chỉ có năm mà không rõ ngày, tháng.
Cách tính này, bạn sẽ thường thấy trong trường hợp:
- Bạn làm việc với cơ sở dữ liệu chứa thông tin của người bệnh. Bạn cần tìm ra số tuổi của bệnh nhân ở thời điểm họ đến bệnh viện khám, thông qua ngày sinh và năm sinh của bệnh nhân.
Ví dụ trong bảng dưới đây, ngày sinh của bệnh nhân ở cột B, bắt đầu từ hàng 5, và năm bệnh nhân khám lần cuối ở cột C. Ta có công thức tính tuổi như sau: =DATEDIF(B5,DATE(C5,1,1),”Y”)
Trong đó:
- B5: Là ô dữ liệu chứa ngày sinh của bệnh nhân.
- DATE(C5,1,1): Số ngày, tháng do bạn cung cấp ở ô C5 với số năm có sẵn. Ví dụ ở đây là ngày 1 tháng 1 của một năm 2015.
- Y: Thời gian được xác định theo năm tròn giữa 2 ngày.
Với ví dụ trên, bạn sẽ tính ra được tuổi của bệnh nhân đầu tiên tính đến ngày 1/1/2015.
Tính tuổi với ngày tháng năm sinh ở nhiều ô khác nhau
Trong trường hợp 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ần kết hợp 2 hàm DATE và DATEVALUE để lấy ngày tháng năm sinh trước.
Bước 1: Bạn sử dụng các hàm dưới đây để tính ngày, tháng, năm:
- Xác định ngày:
=DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&”1″)),D3),TODAY(),”MD”) & “Day, “ - Xác định tháng:
=DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&”1″)),D3),TODAY(),”YM”) & “Month, “ - Xác định năm:
=DATEDIF(DATE(B3,MONTH(DATEVALUE(C3&”1″)),D3),TODAY(),”Y”) &“Year, “
Bước 2: Bạn nối 3 hàm trên lại với nhau bằng ký tự ‘&’ thành 1 công thức tính tuổi hoàn chỉnh
=DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2),TODAY(),”Y”) & 'Years, “ & DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2),TODAY(),”YM”) & “Months, “ & DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2),TODAY(),”MD”) & “Days'
Như vậy, bạn đã hoàn thành công thức tính tuổi trong Excel với dữ liệu ở 3 ô khác nhau. Với một công thức dài như trên, bạn cần nhập cẩn thận để không bỏ sót bất kỳ ký tự nào nhé.
Cách tìm ngày sinh khi biết số tuổi
Với cách tính này, bạn có thể xác định được chính xác thời gian mà 1 người đạt tới 1 số tuổi cụ thể.
Ví dụ, 1 người sinh ngày 25/7/1971 thì khi người đó 50 tuổi sẽ là ngày bao nhiêu?
Bước 1: Bạn nhập hàm =DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2)).
- B2 là ô dữ liệu chứa ngày tháng năm sinh.
- YEAR(B2) + 50: Năm sinh của người đó + 50 năm nữa.
- MONTH(B2) và DAY(B2): Lần lượt là tháng sinh và ngày sinh.
Bước 2: Bạn nhấn phím Enter và kiểm tra kết quả.
Không chỉ 50 mà bất kỳ tuổi nào, bạn cũng có thể xác định được thời điểm của 1 người khi đạt tới số tuổi đó.
Các lỗi thường gặp khi tính tuổi
Trong quá trình tính tuổi trong Excel, có một số lỗi thường gặp mà bạn cần lưu ý:
- Lỗi sai định dạng: Kết quả tính toán có thể không hiển thị đúng định dạng tuổi. Hãy chắc chắn rằng ô chứa kết quả đã được định dạng là số nguyên hoặc định dạng tuổi tùy chọn.
- Lỗi số tuổi không chính xác: Với một số cách tính sẽ cần quy đổi về số ngày do đó kết quả tuổi sẽ có sự chênh lệch. Bạn cần kiểm tra lại công thức và đảm bảo rằng bạn đã chọn phương pháp tính tuổi phù hợp.
- Lỗi hiển thị: Việc tính tuổi có thể gặp lỗi khi các trường dữ liệu chính không đồng nhất một dữ liệu. Ví dụ, nếu ngày sinh không được nhập hoặc có ô trống, công thức tính tuổi có thể không hoạt động.
Để tránh các lỗi này, hãy kiểm tra kỹ công thức, định dạng dữ liệu và đảm bảo rằng các giá trị đầu vào đầy đủ và chính xác.
Kết luận
Trên đây là tổng hợp cách tính tuổi trong Excel có ngày tháng năm sinh bằng các công thức hàm khác nhau. Có nhiều cách để tính tuổi trong Excel, nhưng bạn có thể chọn phương pháp phù hợp với yêu cầu và ưu tiên của mình.
Hy vọng rằng thông tin trong bài viết sẽ giúp ích cho bạn trong công việc tính tuổi. Nếu thấy bài viết này hữu ích và cần thiết thì đừng quên chia sẻ cho nhiều người khác nữa nhé.
Bài viết liên quan: