Để tính thâm niên trong Excel ta sẽ sử dụng hàm DATEIF với cấu trúc hàm như sau:
- Cú pháp chung hàm DATEDIF:
=DATEDIF(Firstdate, Enddate, Option)- Để tính số năm tròn:
=DATEDIF(Firstdate, Enddate, y) - Để tính số tháng lẻ chưa tròn năm:
=DATEDIF(Firstdate, Enddate, ym) - Để tính số ngày lẻ chưa tròn tháng:
=DATEDIF(Firstdate, Enddate, md)
- Để tính số năm tròn:
- Công thức tính thâm niên (ví dụ: 2 năm 3 tháng lẻ 5 ngày):
=TEXTJOIN(" ", TRUE,IF(DATEDIF(E2,F2,"y")>0, DATEDIF(E2,F2,"y")&" năm",""),IF(DATEDIF(E2,F2,"ym")>0, DATEDIF(E2,F2,"ym")&" tháng",""),IF(DATEDIF(E2,F2,"md")>0, DATEDIF(E2,F2,"md")&" ngày",""))
Dưới đây là hướng dẫn chi tiết các cách để tính thâm niên trong Excel:
1. Công thức tính thâm niên
Thâm niên là thời gian làm việc được tính từ ngày bắt đầu làm việc đến ngày kết thúc tính.
Trong thực tế, việc tính thâm niên sẽ gặp 3 trường hợp sau:
- Với nhân viên còn làm việc: Ngày kết thúc tính tương ứng với ngày hôm nay.
- Với nhân viên đã nghỉ việc: Ngày kết thúc tính tương ứng với ngày nghỉ việc.
- Ngoài ra, trong trường hợp thâm niên không tính thời gian thử việc, kỳ nghỉ dài => thì ta cần phải trừ thêm các ngày không được tính đó.
Tải ngay file excel tính thâm niên với công thức
2. Sử dụng hàm DATEDIF tính thâm niên cơ bản
Hàm DATEIF trả về giá trị là số ngày (hoặc số năm, số tháng) giữa 2 khoảng thời gian, với cú pháp:
=DATEDIF(Firstdate, Enddate, Option)
Trong đó:
- Firstdate là ngày bắt đầu.
- Enddate là ngày kết thúc tính.
- Option là tùy chọn kết quả trả về:
- “d“: trả về số ngày giữa hai khoảng thời gian.
- “m“: trả về số tháng giữa hai khoảng thời gian (chỉ lấy phần nguyên).
- “y“: trả về số năm giữa hai khoảng thời gian (chỉ lấy phần nguyên).
- “yd“: trả về số ngày lẻ của năm giữa hai khoảng thời gian.
- “ym“: trả về số tháng lẻ của năm giữa hai khoảng thời gian.
- “md“: trả về số ngày lẻ của tháng giữa hai khoảng thời gian (số ngày chưa tròn tháng).
Ví dụ, để sử dụng hàm DATEDIF tính thâm niên của một bảng dữ liệu nhân viên với thời gian làm việc như sau:
Dựa vào bảng trên, ta có thể sử dụng hàm DATEDIF để tính:
- Số năm tròn:
=DATEDIF(E3, F3, "y") - Tính số tháng không tròn năm:
=DATEDIF(E3, F3, "ym") - Tính số ngày lẻ chưa tròn tháng:
=DATEDIF(E3, F3, "md")
Khi đã có được số năm tròn, tháng lẻ, ngày lẻ, ta có thể tính thâm niên bằng cách cộng hoặc hiển thị dưới dạng chuỗi, hoặc có thể sử dụng kết hợp hàm DATEDIF với hàm TEXTJION như sau:
=@TEXTJOIN(" ", TRUE, IF(DATEDIF(E3, F3, "y") > 0, DATEDIF(E3, F3, "y") & " năm", ""), IF(DATEDIF(E3, F3, "ym") > 0, DATEDIF(E3, F3, "ym") & " tháng", ""), IF(DATEDIF(E3, F3, "md") > 0, DATEDIF(E3, F3, "md") & " ngày", ""))
Với hàm TEXTJION ở trên ta có thể loại bỏ các giá trị nếu bằng 0.
3. Cách tính thâm niên trong các trường hợp thực tế
Trong thực tế, việc tính thâm niên không đơn giản chỉ cần tính khoảng thời gian giữa ngày bắt đầu làm và ngày kết thúc, mà còn có các quy định như nghỉ giữa chừng, thời gian thử việc,… hoặc muốn tính thời điểm mốc 5 năm để tính bậc lương.
3.1. Tính thâm niên cho nhân viên còn làm và loại trừ thời gian thử việc
Áp dụng: khi doanh nghiệp tính thâm niên bắt đầu từ sau khi hết thử việc x tháng.
Với trường hợp tính thâm niên ngoại trừ thời gian thử việc, thì ta sẽ tạo thêm một cột phụ là số tháng thử việc, và chuyển dịch ngày bắt đầu tính thời gian làm việc với hàm EDATE như sau:
Sau khi đã chuyển được ngày bắt đầu tính thâm niên, thì ta áp dụng hàm DATEIF như bình thường để tính thâm niên (xem chi tiết ở mục 1).
Công thức tính như sau:
- Tính số năm tròn:
=DATEDIF(E2, $B$1, "y") - Tính số tháng lẻ:
=DATEDIF(E2, $B$1, "ym") - Tính số ngày lẻ:
=DATEDIF(E2, $B$1, "md") - Tính số năm ở dạng thập phân:
=YEARFRAC(E2, $B$1, 3) - Tính tổng số tháng làm việc:
=DATEDIF(E2, $B$1, "m")
Trong trường hợp chỉ tính 50% thời gian thử việc (rất ít gặp) thì ta có giảm trực tiếp 50% ở cột “Số tháng thử việc được trừ” hoặc tạo cột phụ.
3.2. Tính thâm niên với nhân viên đã nghỉ việc
Đối với các trường hợp nhân viên đã nghỉ việc nhưng vẫn cần tính thâm niên thì ta sẽ tiến hành tạo cột “thời gian nghỉ“, và tại cột ngày kết thúc tính thâm niên, ta sẽ sử dụng hàm IF để đặt điều kiện:
- Nếu không có giá trị tại cột “thời gian nghỉ” thì thời gian nghỉ sẽ là ngày hôm nay.
- Nếu có thì thời gian tính là “thời gian nghỉ”.
Như ví dụ trên, tại cột F ta sử dụng công thức: =IF(C3="", $B$1, C3)
3.3. Tính mốc kỷ niệm năm tới và mốc 5 năm làm việc
Trong quá trình tính thâm niên, sẽ phát sinh việc theo dõi xem thời điểm nào thì nhân viên đủ thâm niên 1 năm, 2 năm hoặc 5 năm. Lúc này ta có thể sử dụng hàm EDATE:
Để mốc thời gian kỷ niệm năm tiếp theo ta sẽ sử dụng hàm EDATE:
=EDATE([@[Ngày bắt đầu tính thâm niên]], (INT(DATEDIF([@[Ngày bắt đầu tính thâm niên]],[@[Ngày kết thúc tính]],"m")/12)+1)*12)
Ví dụ áp dụng với bảng dữ liệu sử dụng trong bài:
Để tính mốc thời gian đạt được x năm thâm niên ta sử dụng công thức:
=EDATE([@[Ngày bắt đầu tính thâm niên]], (INT(YEARFRAC([@[Ngày bắt đầu tính thâm niên]], [@[Ngày kết thúc tính]], 3)/x) + 1) * x * 12)
Ví dụ áp dụng vào bảng dữ liệu trong bài, ta muốn tính mốc thời gian 5 năm thâm niên:
Trên đây là hướng dẫn về cách tính thâm niên trong Excel. Mong rằng bài viết này hữu ích và giúp các bạn áp dụng thành công!
