Dừng Chia 365 Để Tính Tuổi (Dùng YEARFRAC Thay Vì)

Công thức =(TODAY()-B2)/365 xuất hiện trong hầu hết các hướng dẫn tính tuổi trên Excel. Cách này nhanh và dễ nhớ, nhưng cho kết quả sai khi gặp năm nhuận hoặc tháng 2. Một hàm ít người biết có thể tính chính xác hơn mà không phức tạp hơn.

Mục Lục

Tại sao chia cho 365 cho kết quả sai

Phương pháp phổ biến nhất là lấy số ngày sống rồi chia cho 365. Công thức trông đơn giản: lấy ngày hiện tại trừ ngày sinh, sau đó chia cho 365 để ra số năm.

Vấn đề với năm nhuận: Cứ 4 năm có 1 năm 366 ngày. Nếu tính tuổi cho người sinh năm 2000 đến 2024, trong 24 năm này có 6 năm nhuận. Chia cho 365 sẽ thiếu 6 ngày, tương đương sai lệch 0.016 năm hay khoảng 6 ngày.

Vấn đề với tháng 2: Người sinh ngày 29/2 chỉ có sinh nhật thực 1 lần trong 4 năm. Chia 365 không tính được trường hợp này đúng. Excel sẽ cho kết quả khác nhau tùy thuộc vào năm hiện tại là năm nhuận hay không.

Một số người khuyên chia cho 365.25 thay vì 365. Cách này tốt hơn một chút nhưng vẫn không chính xác với trẻ em chưa sống qua năm nhuận hoặc khoảng thời gian ngắn.

YEARFRAC tính theo số ngày thực tế

Hàm YEARFRAC được thiết kế để tính phần năm giữa hai ngày. Thay vì dùng số 365 cố định, hàm này tính dựa trên số ngày thực tế của từng tháng và năm.

Cú pháp cơ bản:

=YEARFRAC(start_date, end_date, [basis])

start_date: Ngày bắt đầu (ngày sinh)
end_date: Ngày kết thúc (thường dùng TODAY())
basis: Phương pháp tính (0 đến 4)

XEM THÊM: Return_Type 1, 2 Hay 3? Hướng Dẫn Chọn Đúng Cho Hàm WEEKDAY

Các giá trị basis:

0 hoặc bỏ trống: US 30/360 (giả định mỗi tháng 30 ngày)
1: Actual/Actual (số ngày thực tế – khuyên dùng)
2: Actual/360 (dùng trong tài chính)
3: Actual/365 (giả định năm 365 ngày)
4: European 30/360 (chuẩn châu Âu)

Với việc tính tuổi, basis 1 cho kết quả chính xác nhất vì nó tính theo số ngày thực tế của từng tháng và xử lý năm nhuận đúng cách.

Cách sử dụng YEARFRAC để tính tuổi

Giả sử ngày sinh nằm ở ô B2 và bạn muốn tính tuổi hiện tại.

Công thức đầy đủ:

=YEARFRAC(B2,TODAY(),1)

Các bước thực hiện:

Chọn ô trống để hiển thị kết quả
Nhập công thức =YEARFRAC(B2,TODAY(),1)
Nhấn Enter

Kết quả sẽ là số thập phân, ví dụ: 33.7264 có nghĩa là 33 năm 8 tháng 21 ngày.

Để hiển thị chỉ số năm nguyên:

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

Hàm ROUNDDOWN làm tròn xuống về số nguyên gần nhất. Khác với ROUND làm tròn thông thường, ROUNDDOWN luôn bỏ phần thập phân, đúng với cách tính tuổi trong đời sống (33.9 năm vẫn là 33 tuổi).

Để tính cả năm và tháng:

=ROUNDDOWN(YEARFRAC(B2,TODAY(),1),0) & " năm " & ROUNDDOWN((YEARFRAC(B2,TODAY(),1)-ROUNDDOWN(YEARFRAC(B2,TODAY(),1),0))*12,0) & " tháng"

Công thức này tách phần năm và phần tháng, sau đó ghép lại thành chuỗi văn bản dễ đọc.

So sánh kết quả giữa các phương pháp

Lấy ví dụ người sinh ngày 29/02/2000, tính tuổi đến ngày 15/10/2024:

Phương pháp TODAY/365:

=(TODAY()-DATE(2000,2,29))/365

Kết quả: 24.6301 năm

Phương pháp YEAR:

=YEAR(TODAY())-YEAR(DATE(2000,2,29))

Kết quả: 24 năm (sai vì chưa đến tháng 2 năm 2024)

Phương pháp YEARFRAC:

=YEARFRAC(DATE(2000,2,29),TODAY(),1)

Kết quả: 24.6274 năm

Chênh lệch tưởng nhỏ nhưng khi áp dụng cho hàng nghìn dòng dữ liệu trong quản lý nhân sự hoặc tính lãi suất, sai số này cộng dồn lên đáng kể.

Ứng dụng ngoài tính tuổi

YEARFRAC không chỉ dùng để tính tuổi. Hàm này xuất hiện nhiều trong các bảng tính tài chính và kế toán.

XEM THÊM: Cách Dùng Hàm DAYS Trong Excel 2013 Để Tính Khoảng Cách Ngày Chính Xác

Tính thâm niên làm việc:

=YEARFRAC(ngày_vào_làm,TODAY(),1)

Kết quả chính xác giúp tính phụ cấp thâm niên, thưởng theo năm công tác, hoặc xét nghỉ phép.

Tính lãi suất ngân hàng: Ngân hàng thường tính lãi theo số ngày thực tế. YEARFRAC với basis 1 cho kết quả khớp với cách ngân hàng tính toán.

=số_tiền_gốc * lãi_suất_năm * YEARFRAC(ngày_gửi,ngày_rút,1)

Phân bổ chi phí theo thời gian: Khi cần phân bổ chi phí hoặc khấu hao tài sản trong một khoảng thời gian cụ thể:

=tổng_chi_phí * YEARFRAC(ngày_bắt_đầu,ngày_kết_thúc,1)

Tính hợp đồng ngắn hạn: Với hợp đồng tính theo ngày nhưng cần quy đổi ra năm để so sánh, YEARFRAC cho kết quả chính xác hơn chia thô cho 365.

Xử lý lỗi thường gặp

Lỗi #VALUE! Xuất hiện khi ngày không hợp lệ. Excel không nhận diện được giá trị trong ô là ngày tháng.

Kiểm tra bằng cách đổi định dạng ô thành kiểu số. Nếu hiển thị số (ví dụ: 44,927), ngày hợp lệ. Nếu vẫn là text, dùng hàm DATE để tạo ngày đúng cách:

=YEARFRAC(DATE(2000,2,29),TODAY(),1)

Lỗi #NUM! Xảy ra khi basis nằm ngoài phạm vi 0-4. Kiểm tra lại giá trị basis, đảm bảo chỉ dùng các số từ 0 đến 4.

Kết quả âm: Nếu start_date sau end_date, YEARFRAC trả về số âm. Đảm bảo thứ tự đúng: ngày cũ trước, ngày mới sau.

Kết quả quá lớn: Với ngày sinh trước năm 1900, Excel không xử lý được. Hệ thống ngày của Excel bắt đầu từ 01/01/1900. Ngày trước đó không tính được.

Kết hợp với các hàm khác

YEARFRAC hoạt động tốt khi kết hợp với hàm khác để tạo báo cáo phức tạp.

Với hàm IF:

=IF(YEARFRAC(B2,TODAY(),1)>=18,"Trưởng thành","Vị thành niên")

Tự động phân loại dựa vào tuổi.

Với hàm VLOOKUP: Tính tuổi rồi tra cứu mức lương theo thâm niên:

=VLOOKUP(ROUNDDOWN(YEARFRAC(ngày_vào_làm,TODAY(),1),0),bảng_lương,2,TRUE)

Với hàm TEXT: Hiển thị kết quả dạng phần trăm hoặc định dạng đẹp hơn:

=TEXT(YEARFRAC(B2,TODAY(),1),"0.00") & " năm"

Với hàm SUMIFS: Tính tổng lương cho nhân viên có thâm niên trên 5 năm:

=SUMIFS(cột_lương,cột_ngày_vào,">=" & DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))

Khi nào vẫn dùng phương pháp khác

YEARFRAC không phải lúc nào cũng là lựa chọn tốt nhất. Một số trường hợp nên dùng hàm khác.

XEM THÊM: Tôi Tính Ngày Đáo Hạn Nhanh Gấp 10 Lần Nhờ Hàm EDATE Trong Excel

Dùng DATEDIF khi: Cần tách biệt năm, tháng, ngày thành các giá trị riêng. DATEDIF cho phép lấy chỉ phần năm tròn, chỉ phần tháng dư, hoặc chỉ phần ngày dư.

=DATEDIF(B2,TODAY(),"Y") & " năm " & DATEDIF(B2,TODAY(),"YM") & " tháng"

Dùng YEAR khi: Chỉ cần năm tròn không quan tâm chính xác. Ví dụ: phân nhóm theo thập kỷ sinh, lọc theo năm.

Dùng TODAY/365 khi: Làm việc với dữ liệu không quan trọng (demo, test) và cần công thức đơn giản nhất.

Tương thích và phiên bản

YEARFRAC có sẵn trong tất cả phiên bản Excel từ Excel 2007 trở đi. Hàm này cũng hoạt động trên Excel Online, Excel for Mac, và Google Sheets.

Google Sheets dùng cùng cú pháp và cho kết quả giống hệt Excel. LibreOffice Calc cũng hỗ trợ hàm này với các tham số tương tự.

Các phiên bản Excel cũ hơn (2003 trở về trước) không có YEARFRAC. Với các file cần tương thích ngược, dùng DATEDIF hoặc công thức thủ công.

Nên bắt đầu từ đâu

Thay thế công thức cũ dần dần thay vì sửa toàn bộ file một lúc. Bắt đầu với các bảng tính quan trọng nhất: bảng lương, danh sách nhân viên, hợp đồng khách hàng.

Với file đã có công thức TODAY/365, tìm kiếm toàn bộ file bằng Ctrl+F, nhập “/365”, rồi thay thế từng công thức. Kiểm tra kết quả sau khi thay để đảm bảo không ảnh hưởng đến các công thức liên kết.

Basis 1 phù hợp với hầu hết trường hợp. Chỉ thay đổi basis khi có yêu cầu đặc biệt từ tài chính hoặc kế toán. Đọc tài liệu hướng dẫn từ ngân hàng hoặc cơ quan thuế để biết basis nào họ yêu cầu.

YEARFRAC là một trong những hàm ít được nhắc đến nhưng giải quyết vấn đề chính xác hơn các phương pháp phổ biến. Hàm này đặc biệt hữu ích trong môi trường chuyên nghiệp nơi độ chính xác quan trọng hơn tốc độ nhập công thức.