Cách Tính Thâm Niên Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Hàm Tính Bình Quân Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

Giới thiệu chung về thâm niên và tầm quan trọng trong quản trị nhân sự

Thâm niên (seniority) là một trong những chỉ số quan trọng nhất mà các doanh nghiệp, tổ chức sử dụng để đánh giá mức độ gắn bó, kinh nghiệm và đóng góp của nhân viên đối với công ty. Thông thường, thâm niên được tính dựa trên thời gian làm việc thực tế của nhân viên kể từ ngày ký hợp đồng hoặc ngày bắt đầu làm việc. Việc xác định chính xác thâm niên không chỉ giúp doanh nghiệp thực hiện các chính sách lương thưởng, thăng tiến, và phúc lợi một cách công bằng, mà còn hỗ trợ trong việc lập kế hoạch nhân sự, dự báo chi phí và xây dựng môi trường làm việc ổn định.

Trong thời đại số, Excel là công cụ phổ biến, linh hoạt và mạnh mẽ để xử lý dữ liệu nhân sự, trong đó có việc tính toán thâm niên. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từng bước, từ những kiến thức cơ bản nhất đến các kỹ thuật nâng cao, giúp bạn nhanh chóng và chính xác tính toán thâm niên trong Excel.

Lưu ý: Toàn bộ nội dung dưới đây được viết cho phiên bản Excel 2026 trở lên, nhưng hầu hết các công thức và thủ thuật cũng tương thích với Excel 2026, 2026 và các phiên bản Office 365. Nếu bạn đang dùng Google Sheets, hầu hết công thức cũng hoạt động tương tự.

Có thể bạn quan tâm: Tìm Kiếm Trên Excel 2026: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Chuẩn bị dữ liệu: Cấu trúc bảng tính chuẩn

Để tính thâm niên một cách hiệu quả, trước tiên bạn cần chuẩn bị dữ liệu nguồn. Dưới đây là một mẫu bảng nhân sự cơ bản:

1.1. Định dạng ngày tháng

• Đảm bảo cột Ngày bắt đầu làm việc (Cột D) và Ngày kết thúc (Cột E) được định dạng là Date (Ngày). Bạn có thể chọn cột → nhấn Ctrl + 1 → chọn Date → chọn định dạng dd/mm/yyyy (hoặc mm/dd/yyyy tùy khu vực).
• Nếu ngày kết thúc để trống, nghĩa là nhân viên vẫn đang làm việc. Khi tính thâm niên, chúng ta sẽ sử dụng ngày hiện tại (TODAY()) làm ngày kết thúc tạm thời.

1.2. Thêm cột “Thâm niên (năm)”

Sau khi đã chuẩn bị dữ liệu, chúng ta sẽ thêm một cột mới (cột F) để tính thâm niên dưới dạng số năm (có thể kèm phần thập phân để hiển thị tháng). Đặt tiêu đề Thâm niên (năm).

Có thể bạn quan tâm: Trắc Nghiệm Word, Excel, Powerpoint: Hướng Dẫn Toàn Diện Đánh Giá Kiến Thức Văn Phòng

2. Công thức cơ bản: Tính thâm niên bằng hàm DATEDIF

2.1. Giới thiệu hàm DATEDIF

DATEDIF(start_date, end_date, unit) là hàm ẩn trong Excel, không xuất hiện trong danh sách hàm, nhưng rất hữu ích để tính khoảng thời gian giữa hai ngày. Tham số unit quyết định đơn vị trả về:

• "y" – số năm đầy đủ.
• "ym" – số tháng còn lại sau khi đã tính năm.
• "md" – số ngày còn lại sau khi đã tính năm và tháng.
• "d" – tổng số ngày.

2.2. Công thức tính thâm niên (đơn vị năm, không thập phân)

Giả sử ngày bắt đầu ở D2, ngày kết thúc ở E2 (có thể để trống). Công thức:

=DATEDIF(D2, IF(E2="", TODAY(), E2), "y")

Giải thích:

• IF(E2="", TODAY(), E2) → nếu ô E2 trống, dùng ngày hiện tại (TODAY()); nếu không, dùng ngày kết thúc thực tế.
• "y" → trả về số năm đầy đủ.

Bạn sao chép công thức này xuống toàn bộ cột F.

2.3. Công thức tính thâm niên có thập phân (năm + phần tháng)

Nếu muốn hiển thị thâm niên dưới dạng năm.tháng (ví dụ: 3.5 năm = 3 năm 6 tháng), chúng ta sẽ tính số tháng còn lại và chuyển thành phần thập phân:

=DATEDIF(D2, IF(E2="", TODAY(), E2), "y") + DATEDIF(D2, IF(E2="", TODAY(), E2), "ym")/12

• DATEDIF(..., "ym") trả về số tháng còn lại (0‑11). Chia cho 12 để chuyển thành phần thập phân.

Mẹo: Định dạng ô F dưới dạng Number với 2 hoặc 3 chữ số thập phân để hiển thị rõ ràng.

2.4. Công thức tính thâm niên chi tiết (năm, tháng, ngày)

Nếu muốn hiển thị dạng “X năm Y tháng Z ngày”, bạn có thể kết hợp ba hàm DATEDIF:

=DATEDIF(D2, IF(E2="", TODAY(), E2), "y") & " năm " &
DATEDIF(D2, IF(E2="", TODAY(), E2), "ym") & " tháng " &
DATEDIF(D2, IF(E2="", TODAY(), E2), "md") & " ngày"

Công thức này trả về một chuỗi văn bản, phù hợp cho báo cáo.

3. Xử lý các trường hợp đặc biệt

3.1. Nhân viên chưa hoàn thành 1 tháng

Khi ngày bắt đầu và ngày kết thúc (hoặc ngày hiện tại) cách nhau chưa đủ một tháng, hàm DATEDIF(...,"ym") sẽ trả về 0, và DATEDIF(...,"y") cũng 0. Nếu bạn muốn hiển thị “< 1 tháng”, bạn có thể dùng công thức:

=IF(DATEDIF(D2, IF(E2="", TODAY(), E2), "d")<30, "< 1 tháng", DATEDIF(D2, IF(E2="", TODAY(), E2), "y") & " năm " & DATEDIF(D2, IF(E2="", TODAY(), E2), "ym") & " tháng")

3.2. Nhân viên nghỉ tạm thời (nghỉ không lương)

Nếu công ty có chính sách không tính thời gian nghỉ không lương vào thâm niên, bạn cần một danh sách ngày nghỉ và trừ chúng ra. Giải pháp:

1. Tạo bảng “Ngày nghỉ không lương” (cột A: ngày bắt đầu nghỉ, cột B: ngày kết thúc nghỉ). Ví dụ:

1. Sử dụng hàm SUMPRODUCT để tính tổng số ngày nghỉ không lương nằm trong khoảng làm việc của mỗi nhân viên.

Giả sử bảng ngày nghỉ nằm trong Sheet2!A:B, công thức tính ngày làm việc thực tế:

=DATEDIF(D2, IF(E2="", TODAY(), E2), "d") - SUMPRODUCT( (Sheet2!$A$2:$A$1000>=D2)(Sheet2!$A$2:$A$1000<=IF(E2="", TODAY(), E2)) (Sheet2!$B$2:$B$1000-Sheet2!$A$2:$A$1000+1) )

Sau khi có tổng ngày làm việc thực tế, bạn có thể chuyển lại sang năm/tháng bằng công thức chia cho 365 hoặc dùng INT để lấy năm, MOD để lấy tháng.

3.3. Nhân viên chuyển phòng ban, thâm niên tổng công ty vs thâm niên phòng

Nếu muốn tính thâm niên tổng công ty (từ ngày vào công ty) và thâm niên phòng ban (từ ngày chuyển phòng), bạn cần hai cột ngày bắt đầu:

• Ngày vào công ty (cột D)
• Ngày chuyển phòng (cột G)

Công thức thâm niên công ty: =DATEDIF(D2, IF(E2="", TODAY(), E2), "y") + DATEDIF(D2, IF(E2="", TODAY(), E2), "ym")/12

Công thức thâm niên phòng ban:

=IF(G2="","", DATEDIF(G2, IF(E2="", TODAY(), E2), "y") + DATEDIF(G2, IF(E2="", TODAY(), E2), "ym")/12)

Bạn có thể đặt điều kiện nếu ô G2 trống (chưa chuyển phòng) thì trả về “-”.

4. Tự động cập nhật thâm niên hàng ngày với bảng động (Dynamic Table)

Khi dữ liệu nhân sự thay đổi (thêm mới, sửa ngày), bạn muốn thâm niên tự động cập nhật mà không cần kéo công thức. Sử dụng Table trong Excel:

1. Chọn toàn bộ dữ liệu (cột A‑E) → Ctrl + T → Đánh dấu “My table has headers”.
2. Đặt tên cho Table, ví dụ tblNhanSu.
3. Trong cột F (Thâm niên), nhập công thức như trước, nhưng thay vì tham chiếu ô cụ thể, dùng cấu trúc bảng:

=DATEDIF(@Ngày bắt đầu làm việc, IF(@Ngày kết thúc="", TODAY(), @Ngày kết thúc), "y") + DATEDIF(@Ngày bắt đầu làm việc, IF(@Ngày kết thúc="", TODAY(), @Ngày kết thúc), "ym")/12

Khi bạn thêm một dòng mới, công thức sẽ tự động được áp dụng cho dòng đó.

4.1. Định dạng ngày tự động

Trong Table, bạn có thể đặt Data Validation để chắc chắn ngày nhập vào luôn hợp lệ:

• Chọn cột “Ngày bắt đầu” → Data → Data Validation → Allow: Date → Data: greater than → nhập 1/1/1900.

5. Trình bày báo cáo thâm niên: PivotTable và biểu đồ

Có thể bạn quan tâm: Cách Tính Tổng Hàng Dọc Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Sau khi tính toán xong, bạn thường cần báo cáo thâm niên cho bộ phận nhân sự hoặc lãnh đạo. Hai công cụ mạnh mẽ trong Excel là PivotTable và PivotChart.

5.1. Tạo PivotTable tổng hợp thâm niên

1. Chọn bất kỳ ô trong Table → Insert → PivotTable.
2. Đặt PivotTable ở một sheet mới, đặt tên PT_ThamNien.
3. Kéo Phòng ban (nếu có) vào Rows.
4. Kéo Thâm niên (năm) vào Values. Mặc định là Sum, thay đổi thành Average để lấy thâm niên trung bình.
5. Thêm Mã nhân viên vào Values và đổi thành Count để biết số lượng nhân viên mỗi phòng.

Kết quả: Bảng hiển thị số lượng nhân viên, thâm niên trung bình, tối đa, tối thiểu (bằng cách nhấp chuột phải → Value Field Settings → Show Values As → Running Total hoặc Max, Min).

5.2. Biểu đồ thâm niên

• Chọn PivotTable → Insert → Column Chart → Clustered Column.
• Định dạng: Đặt tiêu đề “Thâm niên trung bình theo phòng ban”, trục Y là “Năm”, trục X là “Phòng ban”.
• Thêm Data Labels để hiển thị giá trị số.

Bạn cũng có thể tạo Histogram (biểu đồ tần suất) để xem phân bố thâm niên:

1. Tạo một cột mới “Thâm niên (năm) – làm tròn” bằng công thức =ROUND(@Thâm niên (năm),0).
2. Chọn cột này → Insert → Histogram.

5.3. Slicer để lọc nhanh

Nếu bạn muốn người dùng cuối tự do lọc dữ liệu theo Năm vào công ty, Phòng ban, Giới tính, hãy thêm Slicer:

• Chọn PivotTable → Analyze → Insert Slicer.
• Chọn các trường cần lọc và bố trí slicer ở vị trí thuận tiện.

6. Tự động hoá quy trình: Macro VBA tính thâm niên

Đối với các doanh nghiệp có quy mô lớn, việc tính thâm niên bằng công thức có thể gây chậm trễ khi dữ liệu lên tới hàng chục ngàn bản ghi. VBA (Macro) cho phép bạn tính thâm niên một lần và ghi kết quả dưới dạng giá trị tĩnh, giảm tải tính toán.

6.1. Mở môi trường VBA

• Nhấn Alt + F11 → Insert → Module.

6.2. Mã VBA mẫu

Sub TinhThamNien() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim startDate As Date Dim endDate As Date Dim years As Long Dim months As Long Dim result As Double Set ws = ThisWorkbook.Sheets("NhanSu") 'Tên sheet chứa dữ liệu lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow 'Bắt đầu từ dòng 2 (bỏ tiêu đề) If IsDate(ws.Cells(i, "D").Value) Then startDate = ws.Cells(i, "D").Value Else ws.Cells(i, "F").Value = "Ngày bắt đầu không hợp lệ" GoTo NextIter End If If IsDate(ws.Cells(i, "E").Value) Then endDate = ws.Cells(i, "E").Value Else endDate = Date 'Nếu ô E trống, dùng ngày hôm nay End If If endDate < startDate Then ws.Cells(i, "F").Value = "Ngày kết thúc < ngày bắt đầu" GoTo NextIter End If years = WorksheetFunction.DatedIf(startDate, endDate, "y") months = WorksheetFunction.DatedIf(startDate, endDate, "ym") result = years + months / 12 ws.Cells(i, "F").Value = Round(result, 2) 'Kết quả 2 chữ số thập phân
NextIter: Next i MsgBox "Hoàn thành tính thâm niên cho " & lastRow - 1 & " nhân viên.", vbInformation
End Sub

Giải thích nhanh:

• WorksheetFunction.DatedIf là cách gọi hàm DATEDIF trong VBA.
• Kết quả được ghi vào cột F (Thâm niên).
• Macro sẽ bỏ qua các bản ghi có ngày không hợp lệ và đưa thông báo lỗi vào ô F.

6.3. Gán macro cho nút “Tính Thâm Niên”

1. Vào Insert → Shapes → Chọn một hình (ví dụ: hình chữ nhật) → Vẽ trên sheet.
2. Click chuột phải → Assign Macro… → Chọn TinhThamNien.
3. Đổi tên nút thành “Tính Thâm Niên”.

Bây giờ, mỗi khi dữ liệu thay đổi, người dùng chỉ cần nhấn nút là tính xong.

7. Kiểm tra và xác nhận độ chính xác

7.1. Kiểm tra mẫu ngẫu nhiên

• Lọc 10-20 bản ghi ngẫu nhiên.
• Dùng công thức =DATEDIF(start,end,"d")/365 để so sánh với kết quả thâm niên đã tính.
• Nếu chênh lệch > 0.01 (khoảng 3-4 ngày), kiểm tra lại công thức hoặc dữ liệu ngày.

7.2. Sử dụng “Conditional Formatting” để đánh dấu bất thường

• Chọn cột “Thâm niên (năm)”.
• Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
• Công thức: =OR(F2<0, F2>50) → Định dạng màu đỏ. (Giả sử không có nhân viên nào có thâm niên > 50 năm).

7.3. Kiểm tra tính toàn vẹn dữ liệu ngày

• Tạo một cột phụ “Kiểm tra ngày” với công thức:

=IF(AND(ISNUMBER(D2), OR(ISNUMBER(E2), E2="")), "OK", "Lỗi")

• Lọc các ô “Lỗi” để rà soát.

8. Ứng dụng thực tiễn: Các chính sách dựa trên thâm niên

Sau khi đã có thâm niên chính xác, doanh nghiệp có thể triển khai nhiều chính sách:

Bạn có thể tạo công thức tự động trong Excel để xác định liệu một nhân viên có đủ điều kiện hay không, ví dụ:

=IF(F2>=5, "Đủ", "Không đủ")

Hoặc sử dụng Lookup Table để gán mức thưởng dựa trên thâm niên.

9. Tối ưu hoá hiệu suất khi dữ liệu quy mô lớn

Khi số bản ghi lên tới hàng chục hoặc hàng trăm nghìn, các hàm DATEDIF lặp lại có thể làm chậm workbook. Dưới đây là một số gợi ý:

1. Giảm độ phức tạp của công thức: Thay vì dùng IF(E2="", TODAY(), E2) trong mỗi ô, tạo một cột “Ngày kết thúc thực tế” (cột G) chứa công thức một lần, sau đó dùng G trong các tính toán tiếp theo.
2. Sử dụng Power Query: Nhập dữ liệu vào Power Query, thực hiện tính toán thâm niên trong môi trường M (ngôn ngữ Power Query) và tải lại kết quả. Power Query xử lý dữ liệu nhanh hơn và không gây tính toán lại liên tục.
3. Chuyển sang dạng giá trị tĩnh: Khi tính xong, sao chép toàn bộ cột thâm niên → Paste Special → Values. Điều này sẽ ngừng việc Excel tái tính công thức mỗi khi có thay đổi.
4. Tắt tính toán tự động: Formulas → Calculation Options → Manual. Khi dữ liệu thay đổi, nhấn F9 để tính lại chỉ khi cần.

10. Tổng kết và các bước thực hiện nhanh (Checklist)

Bạn đã có một quy trình hoàn chỉnh, từ thu thập dữ liệu đến báo cáo và ứng dụng. Hãy áp dụng ngay vào file Excel hiện tại của công ty để nâng cao tính chính xác, minh bạch và hiệu quả trong việc quản lý thâm niên nhân viên.

Phụ lục: Mã VBA hoàn chỉnh và ví dụ Power Query

A. Mã VBA tính thâm niên (phiên bản nâng cao)

Option Explicit Sub CalculateSeniority() Dim ws As Worksheet Dim rng As Range, cell As Range Dim startDt As Date, endDt As Date Dim yrs As Long, mos As Long Dim result As Double Set ws = ThisWorkbook.Sheets("NhanSu") Set rng = ws.Range("D2:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row) 'cột ngày bắt đầu Application.ScreenUpdating = False For Each cell In rng If IsDate(cell.Value) Then startDt = cell.Value If IsDate(cell.Offset(0, 1).Value) Then 'cột E: ngày kết thúc endDt = cell.Offset(0, 1).Value Else endDt = Date End If If endDt >= startDt Then yrs = WorksheetFunction.DatedIf(startDt, endDt, "y") mos = WorksheetFunction.DatedIf(startDt, endDt, "ym") result = Round(yrs + mos / 12, 2) cell.Offset(0, 3).Value = result 'cột F Else cell.Offset(0, 3).Value = "Lỗi ngày" End If Else cell.Offset(0, 3).Value = "Ngày bắt đầu không hợp lệ" End If Next cell Application.ScreenUpdating = True MsgBox "Hoàn tất tính thâm niên!", vbInformation
End Sub

B. Power Query – Tính thâm niên bằng M Language

1. Import dữ liệu: Data → Get Data → From Table/Range.
2. Thêm cột tùy chỉnh:

= Table.AddColumn(PreviousStep, "ThamNien", each let start = Ngày bắt đầu làm việc, finish = if Ngày kết thúc = null then Date.From(DateTime.LocalNow()) else Ngày kết thúc, years = Duration.TotalDays(finish - start) / 365, rounded = Number.Round(years, 2) in rounded)

1. Load kết quả trở lại Excel → Bạn sẽ có cột thâm niên đã tính sẵn, không cần công thức.

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ề ngày tháng, mà còn là nền tảng để doanh nghiệp xây dựng các chính sách nhân sự công bằng, dự báo chi phí và duy trì môi trường làm việc ổn định. Bằng cách nắm vững các hàm cơ bản như DATEDIF, kết hợp với các kỹ thuật nâng cao như Table, PivotTable, Power Query và VBA, bạn có thể:

• Tiết kiệm thời gian: Tự động hoá tính toán cho hàng ngàn bản ghi.
• Đảm bảo độ chính xác: Xử lý các trường hợp đặc biệt, ngày nghỉ không lương.
• Trực quan hoá dữ liệu: Báo cáo thâm niên qua biểu đồ, slicer, giúp lãnh đạo nhanh chóng đưa ra quyết định.
• Mở rộng linh hoạt: Thêm các cột thâm niên phòng ban, thâm niên tổng công ty, hoặc tích hợp vào hệ thống HRIS.

Hãy áp dụng ngay các bước trong bài viết để nâng cao năng suất làm việc của bộ phận nhân sự và tạo nền tảng dữ liệu vững chắc cho chiến lược phát triển dài hạn của doanh nghiệp. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

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ẻ.