Cách tính thâm niên trong Excel bằng hàm DATEDIF cực chuẩn

Nếu bạn là kế toán của một cơ quan hoặc doanh nghiệp, việc tính toán thâm niên công tác và thời gian làm việc để tính hệ số lương và thưởng cho nhân viên mỗi đợt cuối năm rất quan trọng. Công việc này yêu cầu tính toán chính xác từng ngày, tháng và năm vì có ảnh hưởng lớn đến ngân sách công ty.

Nếu bạn đang loay hoay không biết làm thế nào để tính toán và thống kê chính xác dữ liệu này, hãy theo dõi bài viết này nhé! Chúng ta sẽ hướng dẫn bạn cách tính thâm niên trong Excel trong bài học về Excel này.

Số ngày, tháng, năm giữa 2 thời điểm là gì?

Trong ví dụ dưới đây, chúng ta sẽ thử tính thâm niên làm việc chính xác, đo lường số ngày, tháng và năm.

Cho bảng thông tin ngày vào làm của từng nhân viên trong phòng ban A. Tính tới ngày 31/12/2020 (là ngày đặt làm mốc), thâm niên là bao nhiêu năm, ngày và tháng của mỗi người?

Lưu ý: Chuyển dữ liệu ngày tháng năm thành định dạng M/D/YYYY để tránh gặp lỗi khi sử dụng hàm tính toán.

bài toán cách tính thâm niên trong Excel

Cách tính thâm niên trong Excel bằng hàm DATEDIF

Để tính thâm niên làm việc của nhân viên (tính số ngày, tháng, năm giữa 2 thời điểm cụ thể), chúng ta sử dụng hàm đếm số ngày, tháng, năm làm việc trong Excel, cụ thể là hàm DATEDIF.

Công thức tính số ngày, tháng, năm làm việc của hàm DATEDIF: =DATEDIF(ngày bắt đầu, ngày kết thúc, tùy chọn)

  • Ngày bắt đầu: là ngày bắt đầu khoảng thời gian cần tính (Ngày vào làm)
  • Ngày kết thúc: là ngày kết thúc khoảng thời gian cần tính (Ngày làm mốc)
  • Tùy chọn: để xác định kết quả trả về chính xác mong muốn:
TÙY CHỌN KẾT QUẢ TRẢ VỀ
Hệ số quy đổi 1 năm = 12 tháng
Ngày làm mốc luôn là ngày cuối tháng hoặc ngày cuối năm

Thành thạo hàm tính thời gian và các hàm Excel cơ bản đến nâng cao khác với khóa học sau tại Gitiho:

Cách tính số năm làm việc trong Excel

Để tính số năm giữa 2 thời điểm cụ thể (hay còn gọi là tính thâm niên làm việc theo số năm), chúng ta áp dụng hàm tính số năm làm việc trong Excel DATEDIF.

Công thức tính số năm làm việc như sau: =DATEDIF(Ngày vào làm, Ngày làm mốc, "y")

Trong đó:

  • Ngày vào làm: tính từ ô B4 đến ô B8 (tùy thuộc vào dữ liệu của bạn)
  • Ngày làm mốc: ô B2

Lưu ý: Trong trường hợp ngày vào làm lớn hơn ngày làm mốc (tức là nhân viên vào làm sau ngày đặt làm mốc để tính thâm niên), chúng ta sử dụng hàm IF để xử lý trường hợp này.

Công thức như sau: = IF(Ngày vào làm > Ngày làm mốc, 0, DATEDIF(Ngày vào làm, Ngày làm mốc, "y"))

Lúc này, kết quả sẽ trả về 0.

Áp dụng vào ví dụ:

cách tính thâm niên trong Excel theo năm

Cách tính số tháng làm việc trong Excel

Chúng ta tiếp tục sử dụng hàm tính số tháng làm việc trong Excel DATEDIF để đếm số tháng làm việc (tính thâm niên theo số tháng).

Công thức tính số tháng làm việc như sau: =DATEDIF(Ngày vào làm, Ngày làm mốc, "m")

Lưu ý: Đối với trường hợp ngày vào làm lớn hơn ngày làm mốc, chúng ta sử dụng kết hợp hàm IF để trả về 0 (tương tự như cách tính số năm).

Khi áp dụng hàm tính số tháng trong Excel DATEDIF, kết quả bạn nhận được là tổng số tháng giữa 2 thời điểm đó.

Để kết quả trả về cả số năm và tháng còn lại (các tháng lẻ), ta dùng công thức tính số tháng trong Excel sau: =DATEDIF(Ngày vào làm, Ngày làm mốc, "ym")

Áp dụng vào ví dụ cách tính số tháng làm việc trong Excel 2010, các phiên bản khác thực hiện tương tự, ta có:

Cách tính số tháng trong Excel với hàm đếm số tháng làm việc

Cách tính số ngày giữa 2 thời điểm cụ thể trong Excel

Tiếp tục sử dụng hàm DATEDIF để tính số ngày giữa 2 thời điểm cụ thể (tính thâm niên làm việc theo số ngày) trong Excel với công thức: =DATEDIF(Ngày vào làm, Ngày làm mốc, "d")

Lưu ý: Đối với trường hợp ngày vào làm lớn hơn ngày làm mốc, chúng ta sử dụng kết hợp hàm IF để trả về 0 (tương tự như cách tính số tháng và năm).

Tương tự khi sử dụng hàm DATEDIF để tính số tháng để tính số ngày, kết quả bạn nhận được chỉ là tổng số ngày giữa 2 thời điểm cụ thể. Vì vậy, để tính số ngày làm việc lẻ giữa 2 thời điểm đó, bạn áp dụng công thức sau: =DATEDIF(Ngày vào làm, Ngày làm mốc, "md")

Xem thêm: Hướng dẫn cách dùng hàm IF trong Excel qua bài tập

Áp dụng vào ví dụ, ta có:

Cách tính thâm niên trong Excel theo ngày

Như vậy, ta có bảng kết quả hoàn thiện cho yêu cầu tính thâm niên làm việc của nhân viên:

Sau khi áp dụng cách tính thâm niên trong Excel:

Sau khi áp dụng cách tính thâm niên trong Excel

Dễ dàng nhận thấy, nhân viên Vừ A Dính vào làm sau ngày làm mốc nên kết quả trả về cả 3 cột “Năm – Tháng – Ngày” đều bằng 0. Các nhân viên khác lần lượt có kết quả như trong ảnh.

Thật dễ dàng phải không! Chỉ với một hàm DATEDIF, chúng ta có thể áp dụng cho cách tính thâm niên trong Excel nói riêng và tính toán chính xác số lượng ngày, tháng và năm giữa 2 thời điểm cụ thể nói chung một cách nhanh chóng.

Mong rằng qua bài viết này, bạn đã nắm rõ cách dùng hàm DATEDIF giúp công việc tính toán trở nên nhanh gọn và dễ dàng hơn. Bạn có thể tải về tài liệu mẫu ở ngay bên dưới bài viết để tham khảo thêm và đừng quên cập nhật những kiến thức về tin học văn phòng thú vị khác về Excel tại Gitiho.com nhé!