Có thể bạn quan tâm: Hàm Min Và Max Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
1. Giới thiệu chung về thâm niên và tầm quan trọng trong quản lý nhân sự
Thâm niên (seniority) là một trong những chỉ số quan trọng giúp doanh nghiệp đánh giá mức độ gắn bó, kinh nghiệm và đóng góp của nhân viên trong tổ chức. Việc xác định thâm niên không chỉ hỗ trợ trong việc tính lương, thưởng, phụ cấp mà còn giúp xây dựng các chính sách thăng tiến, đào tạo và giữ chân nhân tài.
Trong môi trường làm việc hiện đại, dữ liệu nhân sự thường được lưu trữ trong các hệ thống ERP, HRM hoặc đơn giản là các file Excel. Excel, với tính linh hoạt và khả năng tùy biến cao, là công cụ phổ biến để tính toán và phân tích thâm niên. Tuy nhiên, nhiều doanh nghiệp vẫn gặp khó khăn trong việc xây dựng công thức chính xác, đặc biệt khi phải xử lý các trường hợp đặc biệt như nghỉ việc tạm thời, thay đổi vị trí, hay tính thâm niên tính đến ngày nghỉ lễ.
Bài viết này sẽ hướng dẫn chi tiết cách xây dựng công thức tính thâm niên trong Excel, từ các công thức cơ bản dựa trên ngày gia nhập đến các mô hình nâng cao tính đến ngày nghỉ, ngày nghỉ phép, và thậm chí cả tính thâm niên tích lũy qua nhiều hợp đồng. Ngoài ra, chúng tôi sẽ cung cấp các ví dụ thực tế, mẹo tối ưu hoá hiệu suất và những lưu ý quan trọng để tránh lỗi phổ biến.
Có thể bạn quan tâm: Không Hiển Thị Số 0 Trong Excel: Hướng Dẫn Chi Tiết, Mẹo Thực Tế Và Các Giải Pháp Tối Ưu
2. Các yếu tố cấu thành công thức tính thâm niên
Trước khi đi vào chi tiết công thức, chúng ta cần xác định các yếu tố thường xuất hiện trong tính thâm niên:
| Yếu tố | Mô tả | Định dạng trong Excel |
|---|---|---|
| Ngày vào làm | Ngày mà nhân viên chính thức bắt đầu công việc lần đầu | Date (VD: 01/01/2026) |
| Ngày hiện tại | Ngày tính thâm niên (thường là ngày hôm nay) | TODAY() hoặc một ô ngày cụ thể |
| Ngày nghỉ việc | Ngày mà nhân viên nghỉ việc (nếu có) | Date hoặc để trống nếu chưa nghỉ |
| Thời gian nghỉ không tính thâm niên | Số ngày, tháng, năm mà nhân viên không được tính thâm niên (nghỉ thai sản, nghỉ không lương…) | Số nguyên hoặc công thức tính từ các ngày nghỉ |
| Ngày gia hạn hợp đồng | Trường hợp hợp đồng tạm thời, sau khi hợp đồng cũ hết hạn mà nhân viên vẫn làm việc | Date |
| Ngày chuyển vị trí | Khi nhân viên chuyển sang bộ phận khác, thâm niên chung thường vẫn tính liên tục | Thường không ảnh hưởng, nhưng có thể cần ghi chú |
2.1. Định dạng ngày trong Excel
Excel lưu trữ ngày dưới dạng số nguyên ngày tính từ 1/0/1900 (hoặc 1/1/1904 trên Mac). Đảm bảo các ô chứa ngày được định dạng đúng (dd/mm/yyyy hoặc mm/dd/yyyy tùy khu vực). Nếu nhập sai định dạng, công thức sẽ trả về lỗi #VALUE!.
2.2. Đơn vị tính thâm niên
Thâm niên có thể được tính theo:
- Năm (độ chính xác 2 chữ số thập phân, ví dụ 3.75 năm)
- Tháng (tổng số tháng, ví dụ 45 tháng)
- Ngày (tổng số ngày, ví dụ 1375 ngày)
Mỗi đơn vị có công thức riêng, tuy nhiên cơ bản là tính hiệu số giữa ngày hiện tại và ngày vào làm, sau đó chuyển đổi sang đơn vị mong muốn.
Có thể bạn quan tâm: File Excel Bị Protect View: Nguyên Nhân, Cách Khắc Phục Và Những Lưu Ý Quan Trọng
3. Công thức cơ bản: Tính thâm niên theo ngày, tháng, năm
3.1. Tính thâm niên theo ngày
Công thức đơn giản nhất:
=TODAY() - A2
Trong đó A2 là ô chứa Ngày vào làm. Kết quả sẽ là số ngày làm việc tính đến hôm nay. Để hiển thị dưới dạng số nguyên, bạn có thể định dạng ô là General hoặc Number.
Ví dụ thực tế
| Nhân viên | Ngày vào làm (A) | Thâm niên (ngày) (B) |
|---|---|---|
| Nguyễn Văn A | 15/03/2026 | =TODAY()-A2 |
| Trần Thị B | 01/07/2026 | =TODAY()-A3 |
Nếu hôm nay là 22/06/2026, công thức sẽ trả về:
- Nguyễn Văn A: 2928 ngày
- Trần Thị B: 2156 ngày
3.2. Tính thâm niên theo tháng
Sử dụng hàm DATEDIF (hàm không chính thức trong Excel, nhưng vẫn hoạt động trong hầu hết các phiên bản):
=DATEDIF(A2, TODAY(), "m")
"m"trả về số tháng trọn vẹn giữa hai ngày.- Nếu muốn bao gồm phần tháng chưa đầy đủ, có thể cộng thêm phần thập phân:
=DATEDIF(A2, TODAY(), "m") + DATEDIF(A2, TODAY(), "md")/30
Trong công thức trên:
"md"trả về số ngày còn lại sau khi đã tính toàn bộ tháng.- Chia cho 30 để ước lượng phần tháng chưa đầy đủ.
3.3. Tính thâm niên theo năm (độ chính xác 2 chữ số thập phân)
Công thức chuẩn:
=DATEDIF(A2, TODAY(), "y") + DATEDIF(A2, TODAY(), "ym")/12
"y"trả về số năm trọn vẹn."ym"trả về số tháng còn lại sau khi đã trừ đi các năm trọn vẹn.- Chia cho 12 để chuyển tháng thành năm thập phân.
Nếu muốn hiển thị với 2 chữ số thập phân:
=ROUND(DATEDIF(A2, TODAY(), "y") + DATEDIF(A2, TODAY(), "ym")/12, 2)
3.4. Lưu ý khi dùng DATEDIF
- Hàm không có tài liệu chính thức trong Excel, do đó không xuất hiện trong danh sách hàm. Tuy nhiên, vẫn hoạt động.
- Nếu ngày bắt đầu lớn hơn ngày kết thúc, hàm sẽ trả về lỗi
#NUM!. Vì vậy, cần kiểm tra hoặc dùngIFERRORđể xử lý:
=IFERROR(DATEDIF(A2, TODAY(), "y") + DATEDIF(A2, TODAY(), "ym")/12, 0)
4. Xử lý các trường hợp đặc biệt
4.1. Nhân viên nghỉ việc tạm thời (nghỉ thai sản, bệnh, không lương)
Khi nhân viên tạm nghỉ, doanh nghiệp thường không tính thâm niên cho khoảng thời gian nghỉ. Để trừ đi số ngày nghỉ, chúng ta cần một cột ghi lại Ngày bắt đầu nghỉ và Ngày kết thúc nghỉ. Giả sử:
B2: Ngày bắt đầu nghỉC2: Ngày kết thúc nghỉ
Số ngày không tính thâm niên:
=IF(OR(ISBLANK(B2), ISBLANK(C2)), 0, C2 - B2 + 1)
Sau đó, công thức tính thâm niên thực tế (ngày) sẽ là:
=TODAY() - A2 - (IF(OR(ISBLANK(B2), ISBLANK(C2)), 0, C2 - B2 + 1))
Nếu có nhiều khoảng nghỉ, bạn có thể tạo một bảng phụ (Sheet2) lưu trữ các khoảng nghỉ cho mỗi nhân viên, sau đó dùng SUMIFS để tổng hợp:
=SUMIFS(Sheet2!D:D, Sheet2!A:A, EmployeeID, Sheet2!B:B, "<="&TODAY(), Sheet2!C:C, ">="&A2)
Trong đó:
Sheet2!A:Alà cột ID nhân viênSheet2!B:Blà ngày bắt đầu nghỉSheet2!C:Clà ngày kết thúc nghỉSheet2!D:Dlà số ngày nghỉ (có thể tính bằngC-B+1)
Áp dụng vào công thức chính:
=TODAY() - A2 - SUMIFS(Sheet2!D:D, Sheet2!A:A, EmployeeID)
4.2. Nhân viên có nhiều hợp đồng (tạm thời)
Trong một số doanh nghiệp, nhân viên ký hợp đồng tạm thời và hợp đồng mới bắt đầu ngay sau khi hợp đồng cũ hết hạn. Để tính thâm niên tổng cộng, cần cộng các khoảng thời gian làm việc liên tiếp.
Giả sử bảng hợp đồng (Sheet3) có:
| ID | Ngày bắt đầu | Ngày kết thúc |
|---|---|---|
| 001 | 01/01/2026 | 31/12/2026 |
| 001 | 01/01/2026 | 31/12/2026 |
| 001 | 01/01/2026 | 31/12/2026 |
| 002 | 15/03/2026 | 14/03/2026 |
| 002 | 15/03/2026 | 14/03/2026 |
Để tính tổng ngày làm việc cho mỗi nhân viên, dùng công thức mảng (Excel 365/2026) hoặc SUMPRODUCT:
=SUMIFS(Sheet3!C:C, Sheet3!A:A, EmployeeID) - SUMIFS(Sheet3!B:B, Sheet3!A:A, EmployeeID) + COUNTIFS(Sheet3!A:A, EmployeeID)
Giải thích:
SUMIFS(Sheet3!C:C, Sheet3!A:A, EmployeeID)tổng các ngày kết thúc.SUMIFS(Sheet3!B:B, Sheet3!A:A, EmployeeID)tổng các ngày bắt đầu.- Cộng lại
COUNTIFSđể cộng thêm 1 ngày cho mỗi hợp đồng (vì ngày kết thúc và ngày bắt đầu cùng ngày không tính trong phép trừ).
Sau khi có tổng số ngày, chuyển sang năm/tháng bằng công thức DATEDIF hoặc chia cho 365.
4.3. Tính thâm niên tính đến một ngày cụ thể (kết thúc kỳ đánh giá)
Đôi khi doanh nghiệp muốn tính thâm niên đến một ngày kết thúc kỳ (ví dụ: ngày 31/12/2026) để xác định mức lương tăng. Thay vì dùng TODAY(), chỉ cần thay đổi ngày tham chiếu:
=DATE(2026,12,31) - A2
Hoặc dùng ô E1 chứa ngày kết thúc:
=$E$1 - A2
Điều này giúp tạo báo cáo “cắt” tại thời điểm mong muốn.
5. Tự động hoá quy trình tính thâm niên với bảng tính mẫu
5.1. Cấu trúc bảng tính đề xuất
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Mã NV | Họ tên | Ngày vào làm | Ngày nghỉ (nếu có) | Ngày quay lại | Ngày nghỉ không tính | Thâm niên (ngày) | Thâm niên (năm) |
| 001 | Nguyễn Văn A | 15/03/2026 | 01/01/2026 | 31/01/2026 | 30 | =TODAY()-C2-(IF(OR(ISBLANK(D2),ISBLANK(E2)),0,E2-D2+1)) | =ROUND(DATEDIF(C2,TODAY(),"y")+DATEDIF(C2,TODAY(),"ym")/12,2) |
| 002 | Trần Thị B | 01/07/2026 | 0 | =TODAY()-C3 | =ROUND(DATEDIF(C3,TODAY(),"y")+DATEDIF(C3,TODAY(),"ym")/12,2) |
- Cột D, E: dùng khi có khoảng nghỉ.
- Cột F: số ngày không tính (có thể tự động tính nếu có ngày bắt đầu/kết thúc nghỉ).
- Cột G: thâm niên ngày thực tế.
- Cột H: thâm niên năm.
5.2. Tạo danh sách thả xuống cho “Ngày nghỉ không tính”
Có thể bạn quan tâm: Hàm Nối Ký Tự Trong Excel: Từ Cơ Bản Đến Nâng Cao
Nếu công ty có các loại nghỉ cố định (nghỉ thai sản 180 ngày, nghỉ không lương 30 ngày…), bạn có thể tạo danh sách thả xuống (Data Validation) và dùng VLOOKUP để tự động lấy số ngày.
=VLOOKUP(D2, Table_Nghỉ, 2, FALSE)
Trong đó Table_Nghỉ là bảng:
| Loại nghỉ | Số ngày |
|---|---|
| Thai sản | 180 |
| Không lương | 30 |
| Nghỉ bệnh | 15 |
5.3. Định dạng hiển thị
- Cột G: Định dạng
Numberkhông thập phân. - Cột H: Định dạng
Numbervới 2 chữ số thập phân. - Ngày: Định dạng
dd/mm/yyyy.
5.4. Bảo mật và kiểm tra dữ liệu
- Sử dụng
Data Validationđể ngăn nhập ngày nghỉ kết thúc trước ngày bắt đầu. - Dùng
Conditional Formattingđể đánh dấu nhân viên thâm niên > 5 năm (đánh dấu xanh) hoặc < 1 năm (đánh dấu đỏ).
6. Các công thức nâng cao và tối ưu hoá
6.1. Tính thâm niên trừ ngày lễ nghỉ (lịch công ty)
Giả sử công ty có danh sách ngày lễ trong Sheet LichLe cột A. Để trừ đi các ngày lễ nằm trong khoảng thời gian làm việc:
=NETWORKDAYS(C2, TODAY(), LichLe!$A:$A) - 1
NETWORKDAYStrả về số ngày làm việc (không tính cuối tuần và ngày lễ).- Trừ 1 nếu muốn không tính ngày vào làm.
Nếu công ty làm việc 6 ngày/tuần (không nghỉ Chủ nhật), dùng NETWORKDAYS.INTL:
=NETWORKDAYS.INTL(C2, TODAY(), "0000011", LichLe!$A:$A) - 1
Trong chuỗi "0000011": 1 = ngày nghỉ, 0 = ngày làm việc; vị trí thứ 7 tương ứng Chủ nhật.
6.2. Tính thâm niên tích lũy qua các năm tài chính
Doanh nghiệp thường muốn biết thâm niên tính đến cuối mỗi năm tài chính (31/12). Ta có thể dùng công thức mảng (Excel 365):
=LET( NgayVao; C2; NamCuoi; SEQUENCE(YEAR(TODAY())-YEAR(NgayVao)+1,1,YEAR(NgayVao),1); NgayCuoi; DATE(NamCuoi,12,31); ThamNien; DATEDIF(NgayVao, NgayCuoi, "y") + DATEDIF(NgayVao, NgayCuoi, "ym")/12; HSTACK(NamCuoi, ROUND(ThamNien,2))
)
Kết quả là một mảng gồm cột Năm và thâm niên tính đến cuối năm đó.
6.3. Sử dụng Power Query để tính thâm niên trên dữ liệu lớn
Khi dữ liệu nhân sự lên hàng chục nghìn bản ghi, việc dùng công thức ô có thể chậm. Power Query (Get & Transform) cho phép tính thâm niên ngay trong quá trình tải dữ liệu:
- Import bảng nhân sự vào Power Query.
- Thêm cột tùy chỉnh:
= Table.AddColumn(Source, "ThamNien_Nam", each Number.Round(Duration.Days(DateTime.LocalNow() - NgayVao) / 365, 2))
- Nếu cần trừ ngày nghỉ, tạo bảng nghỉ và dùng
Table.ExpandTableColumnđể join, sau đó tínhDuration.Daystrừ đi tổng ngày nghỉ.
Sau khi hoàn thiện, bấm Close & Load để đưa kết quả vào Excel. Power Query sẽ tự động cập nhật khi dữ liệu nguồn thay đổi, giúp tiết kiệm thời gian và giảm lỗi công thức.
7. Kiểm tra và xác nhận độ chính xác
7.1. Kiểm tra mẫu dữ liệu
| Mã NV | Ngày vào | Ngày nghỉ | Ngày quay lại | Thâm niên (ngày) | Kỳ vọng |
|---|---|---|---|---|---|
| 001 | 01/01/2026 | 01/04/2026 | 30/04/2026 | (công thức) | 410 |
| 002 | 15/06/2026 | (công thức) | 2590 |
- Tính thủ công: từ 01/01/2026 tới 01/04/2026 = 455 ngày, trừ 30 ngày nghỉ = 425 ngày, trừ 1 ngày (để không tính ngày vào) = 424 ngày. Nếu công thức trả về 410, có thể đã trừ thêm ngày lễ hoặc lỗi trong công thức.
- So sánh kết quả bằng cách dùng
=DATEDIFhoặcNETWORKDAYSđể xác nhận.
7.2. Sử dụng công cụ Error Checking
Excel có tính năng Error Checking (Formulas → Error Checking) để phát hiện lỗi #VALUE!, #NUM!. Khi có lỗi, kiểm tra lại thứ tự tham số và định dạng ngày.
7.3. Kiểm tra tính nhất quán trong toàn bộ cột
- Dùng
Conditional Formattingđể đánh dấu các giá trị thâm niên bất thường (ví dụ: thâm niên > 50 năm hoặc < 0). - Dùng
=COUNTIF(H:H,">50")để đếm số nhân viên có thâm niên trên 50 năm, thường là lỗi.
8. Những lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
#VALUE! | Ô chứa ngày không phải định dạng ngày, hoặc chứa văn bản. | Kiểm tra định dạng ô, dùng DATEVALUE để chuyển chuỗi thành ngày. |
#NUM! trong DATEDIF | Ngày bắt đầu lớn hơn ngày kết thúc. | Dùng IF(A2>TODAY(),0,DATEDIF(...)) hoặc MAX/MIN. |
| Thâm niên sai 1 ngày | Không trừ ngày vào làm hoặc ngày hiện tại. | Thêm -1 hoặc +1 tùy theo yêu cầu doanh nghiệp. |
| Kết quả không cập nhật | Tính toán dựa trên TODAY() nhưng tính toán chưa refresh. | Nhấn Ctrl + Alt + F9 để tính lại toàn bộ workbook. |
| Công thức chậm khi có hàng nghìn bản ghi | Sử dụng quá nhiều hàm mảng phức tạp. | Chuyển sang Power Query hoặc VBA để tính toán batch. |
9. Sử dụng VBA để tự động cập nhật thâm niên
Khi cần tính thâm niên cho hàng ngàn nhân viên mỗi khi mở file, có thể viết macro VBA:
Sub UpdateSeniority() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("NhanSu") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow Dim startDate As Date Dim leaveStart As Variant, leaveEnd As Variant Dim leaveDays As Long startDate = ws.Cells(i, "C").Value leaveStart = ws.Cells(i, "D").Value leaveEnd = ws.Cells(i, "E").Value If IsDate(leaveStart) And IsDate(leaveEnd) Then leaveDays = leaveEnd - leaveStart + 1 Else leaveDays = 0 End If ws.Cells(i, "G").Value = Date - startDate - leaveDays 'Thâm niên ngày ws.Cells(i, "H").Value = Round(Application.WorksheetFunction.DatedIf(startDate, Date, "y") + _ Application.WorksheetFunction.DatedIf(startDate, Date, "ym") / 12, 2) 'Thâm niên năm Next i MsgBox "Cập nhật thâm niên hoàn tất!", vbInformation
End Sub
- Ưu điểm: Tốc độ nhanh, có thể chạy khi mở workbook (
Workbook_Openevent) hoặc khi nhấn nút. - Nhược điểm: Cần cho phép macro, không thích hợp cho môi trường không cho phép VBA.
10. Thực hành: Bài tập thực tế
Bài 1: Tính thâm niên tính đến ngày 31/12/2026
- Nhập ngày vào trong cột C.
- Tại ô G2 nhập công thức:
=DATE(2026,12,31)-C2. - Sao chép xuống.
Bài 2: Trừ ngày nghỉ thai sản (180 ngày) và ngày nghỉ không lương (30 ngày)
- Tạo bảng
LoaiNghi(Sheet2) với các loại và số ngày. - Ở cột D nhập loại nghỉ, ở cột E dùng
VLOOKUPđể lấy số ngày. - Công thức thâm niên ngày:
=TODAY()-C2-VLOOKUP(D2,Sheet2!$A:$B,2,FALSE).
Bài 3: Sử dụng NETWORKDAYS.INTL để tính thâm niên chỉ tính ngày làm việc (6 ngày/tuần)
=NETWORKDAYS.INTL(C2, TODAY(), "0000011", LichLe!$A:$A) - 1
Thực hành trên một bộ dữ liệu mẫu 100 nhân viên để kiểm tra tốc độ và độ chính xác.
11. Kết luận
Việc tính thâm niên trong Excel không chỉ là một bài toán đơn giản về phép trừ ngày, mà còn đòi hỏi phải xem xét các yếu tố phụ: ngày nghỉ, ngày lễ, hợp đồng tạm thời và yêu cầu báo cáo vào thời điểm cụ thể. Bằng việc nắm vững các hàm cơ bản như DATEDIF, NETWORKDAYS, SUMIFS và các công cụ nâng cao như Power Query hay VBA, bạn có thể xây dựng một hệ thống tính thâm niên linh hoạt, chính xác và dễ bảo trì.
Những điểm quan trọng cần nhớ:
- Định dạng ngày luôn phải đồng nhất, tránh lỗi
#VALUE!. - Xử lý ngày nghỉ bằng cách trừ trực tiếp số ngày hoặc dùng bảng phụ để tổng hợp.
- Sử dụng
NETWORKDAYSkhi muốn loại trừ cuối tuần và ngày lễ. - Power Query là giải pháp tối ưu cho dữ liệu lớn, giảm tải tính toán trên sheet.
- Kiểm tra và xác nhận thường xuyên bằng cách so sánh mẫu thủ công và sử dụng Conditional Formatting để phát hiện bất thường.
Với kiến thức và công thức ngay trên, bạn đã sẵn sàng triển khai một hệ thống tính thâm niên toàn diện trong môi trường Excel, đáp ứng nhu cầu nhân sự, tiền lương và báo cáo của doanh nghiệp. Hãy áp dụng, tùy chỉnh theo quy trình nội bộ và không ngừng cải tiến để đạt hiệu quả tối đa.
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.