Bộ phận nhân sự cần biết hợp đồng lao động nào sắp hết hạn để kịp gia hạn, còn bộ phận kho cần biết lô hàng nào sắp hết hạn sử dụng để xử lý trước. Bài viết này hướng dẫn bạn làm cảnh báo ngày hết hạn trong Excel, gồm tính ngày hết hạn, đếm số ngày còn lại, ghi trạng thái và tô màu các dòng cần chú ý.
Câu trả lời ngắn: bạn lấy ngày hết hạn trừ ngày hôm nay để có số ngày còn lại, ví dụ =E4-TODAY(). Sau đó, bạn dùng hàm IF để ghi "Đã hết hạn" khi số ngày âm và "Sắp hết hạn" khi số ngày từ 0 tới 30. Cuối cùng, bạn tô màu cả dòng bằng Conditional Formatting với công thức =AND($F4>=0,$F4<=30). Trong ví dụ của bài, hợp đồng của Nguyễn Thu Hà hết hạn ngày 30/11/2026, nên ngày 19/11/2026 hợp đồng này còn 11 ngày, được ghi "Sắp hết hạn" và được tô màu cam.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Trong ảnh, nút Conditional Formatting nằm ở đầu tab Home, còn Excel của bạn có thể đặt nút này ở nhóm Styles phía bên phải. Các ô màu vàng nhạt là nơi bạn gõ công thức. Ngày trong ảnh viết theo kiểu ngày/tháng/năm. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy.
Bạn chuẩn bị bảng theo dõi ngày hết hạn như thế nào?
Bảng ví dụ theo dõi 8 hợp đồng lao động ở vùng A3:G11. Cột C là ngày bắt đầu, cột D là thời hạn hợp đồng tính theo tháng. Hợp đồng của Đỗ Văn Khoa là hợp đồng không xác định thời hạn, nên ô D8 để trống. Bạn cần tính 3 cột: ngày hết hạn, số ngày còn lại và trạng thái.
Ô B1 chứa công thức =TODAY(). Hàm TODAY trả về ngày hôm nay và tự đổi theo ngày bạn mở file. Các công thức trong bài lấy ngày hôm nay từ ô B1, nên bạn chỉ cần đổi một ô khi muốn thử một ngày khác.
Ảnh trong bài được chụp ngày 19/11/2026, nên ô B1 hiện 19/11/2026. Khi bạn mở file vào ngày khác, các con số sẽ khác ảnh. Nếu bạn muốn thử lại đúng các con số trong bài, bạn gõ tay 19/11/2026 vào ô B1, thử xong thì gõ lại =TODAY().
Cột ngày phải chứa ngày thật của Excel. Ngày thật thường nằm sát phải ô, còn ngày dạng chữ thường nằm sát trái ô. Bạn có thể kiểm tra bằng cách gõ công thức =ISNUMBER(C4) vào một ô trống, ví dụ ô I4: kết quả TRUE nghĩa là ô C4 chứa ngày thật.
Bạn tính ngày hết hạn hợp đồng bằng hàm EDATE như thế nào?
Hàm EDATE trả về ngày cách một ngày cho trước một số tháng. Cú pháp của hàm là =EDATE(start_date, months), trong đó start_date là ngày bắt đầu và months là số tháng.
Bạn chọn ô E4, gõ công thức sau rồi nhấn Enter:
=IF(D4="","",EDATE(C4,D4)-1)

Công thức này có 2 phần:
Phần
EDATE(C4,D4)-1tính ngày hết hạn. Hợp đồng 12 tháng bắt đầu ngày 01/12/2025 thường ghi ngày kết thúc là 30/11/2026. Hàm EDATE trả về 01/12/2026, nên công thức trừ thêm 1 ngày. Nếu hợp đồng của công ty bạn ghi ngày kết thúc trùng ngày tương ứng, bạn bỏ phần-1.Hàm IF để trống ô kết quả khi ô thời hạn
D4trống. Nếu thiếu hàm IF, hợp đồng không xác định thời hạn của Đỗ Văn Khoa sẽ có ngày hết hạn là 09/03/2026, tức là trước ngày bắt đầu một ngày.
Tiếp theo, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô E4, khi con trỏ thành dấu cộng đen mảnh thì kéo xuống dòng 11.

Hàm EDATE trả về số thứ tự của ngày. Cột E trong ảnh đã được định dạng sẵn kiểu ngày. Nếu cột E của bạn hiện số như 46356 thay cho 30/11/2026, bạn chọn cột E, mở tab Home và chọn Short Date trong ô định dạng số.
Khi ngày bắt đầu là ngày cuối tháng, hàm EDATE trả về ngày cuối của tháng đích nếu tháng đó không đủ ngày. Ví dụ, khi bạn gõ =EDATE(DATE(2026,1,31),1) vào một ô trống có định dạng ngày, ô đó hiện 28/02/2026. Bạn tính ngày hết hạn bảo hành theo cùng cách, với ngày mua ở ô ngày bắt đầu và số tháng bảo hành ở ô thời hạn. Nếu bạn muốn tìm hiểu thêm về EDATE và các cách cộng trừ ngày khác, bạn có thể đọc bài cộng trừ ngày tháng năm trong Excel.
Nếu hợp đồng đã ghi sẵn ngày kết thúc, bạn gõ thẳng ngày đó vào cột E và bỏ qua bước này.
Bạn tính số ngày còn lại đến ngày hết hạn như thế nào?
Excel lưu mỗi ngày là một con số, nên bạn lấy ngày hết hạn trừ ngày hôm nay là ra số ngày còn lại.
Bạn chọn ô F4, gõ công thức sau rồi nhấn Enter:
=IF(E4="","",E4-$B$1)

Dấu $ trong $B$1 giữ cố định ô ngày hôm nay khi bạn kéo công thức xuống các dòng dưới. Bạn kéo công thức từ ô F4 xuống dòng 11 như ở cột E.

Bạn đọc cột số ngày còn lại theo 3 trường hợp:
Số dương cho biết hợp đồng còn bao nhiêu ngày. Hợp đồng của Nguyễn Thu Hà hết hạn ngày 30/11/2026 nên còn 11 ngày.
Số 0 cho biết hợp đồng hết hạn đúng hôm nay. Hợp đồng của Vũ Thị Lan hết hạn ngày 19/11/2026 nên ô
F9hiện 0.Số âm cho biết hợp đồng đã quá hạn. Hợp đồng của Phạm Ngọc Anh hết hạn ngày 31/10/2026 nên ô
F7hiện -19.
Nếu bảng của bạn không có ô ngày hôm nay riêng, bạn có thể viết thẳng =E4-TODAY(). Kết quả hai cách giống nhau khi ô B1 chứa =TODAY().
Một số bài hướng dẫn dùng hàm DATEDIF để đếm số ngày còn lại. Hàm DATEDIF báo lỗi #NUM! khi ngày hết hạn đã qua. Ví dụ, khi bạn gõ =DATEDIF($B$1,E7,"d") vào một ô trống như ô I7, ô đó báo #NUM! với hợp đồng của Phạm Ngọc Anh. Phép trừ thì trả về số âm, nên bạn vẫn biết hợp đồng đã quá hạn bao nhiêu ngày.
Bạn ghi trạng thái Đã hết hạn, Sắp hết hạn bằng hàm IF như thế nào?
Bài này chọn mốc 30 ngày: hợp đồng còn từ 0 tới 30 ngày là sắp hết hạn. Bạn chọn ô G4, gõ công thức sau rồi nhấn Enter:
=IF(F4="","",IF(F4<0,"Đã hết hạn",IF(F4<=30,"Sắp hết hạn","Còn hạn")))

Hàm IF xét lần lượt từng điều kiện:
Ô
F4trống thì hàm IF để trống ô trạng thái.Số ngày nhỏ hơn 0 thì hàm IF ghi "Đã hết hạn".
Số ngày từ 0 tới 30 thì hàm IF ghi "Sắp hết hạn". Điều kiện chỉ cần viết
F4<=30, vì các số âm đã được xét ở bước trước.Các trường hợp còn lại được ghi "Còn hạn".
Bạn kéo công thức từ ô G4 xuống dòng 11.

Bảng có 4 hợp đồng sắp hết hạn, gồm Nguyễn Thu Hà, Lê Hoàng Nam, Vũ Thị Lan và Bùi Mai Chi. Hợp đồng của Lê Hoàng Nam còn đúng 30 ngày nên vẫn được ghi "Sắp hết hạn". Hợp đồng của Hoàng Đức Huy còn 31 ngày nên được ghi "Còn hạn".
Nếu bạn muốn báo trước 60 ngày, bạn đổi số 30 thành 60.
Bạn tô màu cả dòng sắp hết hạn bằng Conditional Formatting như thế nào?
Cột trạng thái cho biết hợp đồng nào cần chú ý, còn màu giúp bạn thấy ngay các dòng đó khi lướt bảng. Bạn sẽ tạo 2 quy tắc tô màu: màu cam cho dòng sắp hết hạn và màu đỏ nhạt cho dòng đã hết hạn.
Bước 1: Bạn tạo quy tắc tô màu cam cho dòng sắp hết hạn
Đầu tiên, bạn quét chọn vùng A4:G11 bắt đầu từ ô A4. Sau đó, bạn mở tab Home và chọn Conditional Formatting, New Rule.

Hộp thoại New Formatting Rule hiện ra. Bạn chọn dòng Use a formula to determine which cells to format, rồi gõ công thức sau vào ô bên dưới:
=AND($F4>=0,$F4<=30)
=0,$F4<=30), con trỏ chỉ vào nút Format" loading="lazy">Công thức cần cả hai điều kiện trong hàm AND. Nếu bạn chỉ viết điều kiện nhỏ hơn hoặc bằng 30, Excel tô luôn các dòng đã hết hạn, vì số âm cũng nhỏ hơn 30.
Dấu $ trước chữ F giữ cố định cột F, còn số 4 không có dấu $ nên tự đổi theo từng dòng. Nhờ vậy, ô A4 tới ô G4 cùng xét ô F4, và Excel tô cả dòng. Bạn viết công thức theo dòng đầu của vùng đã chọn, tức là dòng 4.
Tiếp theo, bạn bấm nút Format, mở thẻ Fill, chọn màu cam nhạt rồi bấm OK.

Bạn bấm OK thêm một lần để đóng hộp thoại New Formatting Rule.

Bước 2: Bạn tạo quy tắc tô màu đỏ nhạt cho dòng đã hết hạn
Bạn quét chọn lại vùng A4:G11, chọn Conditional Formatting, New Rule, rồi chọn dòng Use a formula to determine which cells to format. Bạn gõ công thức sau:
=$F4<0

Bạn bấm Format, chọn màu đỏ nhạt ở thẻ Fill, rồi bấm OK hai lần.

Dòng của Đỗ Văn Khoa không được tô, vì ô F8 chứa chuỗi rỗng do công thức trả về. Excel xem chuỗi rỗng là chữ và xếp chữ sau mọi con số khi so sánh, nên điều kiện nhỏ hơn hoặc bằng 30 và điều kiện nhỏ hơn 0 đều sai.
Màu sẽ tự đổi theo ngày. Ví dụ, nếu bạn gõ 01/12/2026 vào ô B1, hợp đồng của Nguyễn Thu Hà còn -1 ngày và chuyển sang màu đỏ nhạt, còn hợp đồng của Hoàng Đức Huy còn 19 ngày và chuyển sang màu cam. Cách này cũng giúp bạn xem trước những hợp đồng cần xử lý vào một ngày trong tương lai.
Nếu bảng của bạn không có cột số ngày còn lại, bạn có thể tô màu trực tiếp theo cột ngày hết hạn bằng công thức =AND($E4>=$B$1,$E4<=$B$1+30). Nếu bảng cũng không có ô ngày hôm nay riêng, bạn thay $B$1 bằng TODAY(), tức là =AND($E4>=TODAY(),$E4<=TODAY()+30). Nếu bạn muốn tìm hiểu thêm các cách tô màu khác, bạn có thể đọc bài tô màu có điều kiện trong Excel.
Excel còn có quy tắc dựng sẵn Highlight Cells Rules, A Date Occurring. Quy tắc này chỉ có các khoảng cố định như Next week hoặc Next month, nên bạn không đặt được mốc 30 ngày như công thức ở trên.
Bạn đếm và lọc hàng hóa sắp hết hạn sử dụng như thế nào?
Sheet thứ hai theo dõi 10 mặt hàng trong kho ở vùng A3:F13, với hạn sử dụng ở cột D. Ô B1 của sheet này cũng chứa công thức =TODAY() giống sheet hợp đồng. Nếu ô B1 trống, cột số ngày còn lại ra số dương rất lớn và mọi mặt hàng bị ghi "Còn hạn" mà Excel không báo lỗi. Bạn tính cột số ngày còn lại và cột trạng thái giống bảng hợp đồng:
Ô
E4chứa công thức=D4-$B$1.Ô
F4chứa công thức=IF(E4<0,"Đã hết hạn",IF(E4<=30,"Sắp hết hạn","Còn hạn")).
Mặt hàng nào trong kho cũng có hạn sử dụng, nên hai công thức không cần phần xử lý ô trống. Bạn kéo hai công thức từ dòng 4 xuống dòng 13.

Bạn đếm số mặt hàng sắp hết hạn bằng COUNTIF
Ô H3 ghi nhãn "Số mặt hàng sắp hết hạn", còn ô I3 bên cạnh là nơi bạn gõ công thức. Bạn chọn ô I3, gõ công thức sau rồi nhấn Enter:
=COUNTIF(F4:F13,"Sắp hết hạn")

Kết quả là 5 mặt hàng. Nếu bảng của bạn không có cột trạng thái, bạn đếm theo số ngày còn lại bằng cách gõ =COUNTIFS(E4:E13,">=0",E4:E13,"<=30") vào một ô trống như ô I4, kết quả cũng là 5. Để đếm số mặt hàng đã hết hạn, bạn gõ =COUNTIF(E4:E13,"<0") vào ô I5, kết quả là 2.
Bạn lọc danh sách hàng sắp hết hạn bằng FILTER và SORT
Trước khi lọc, bạn chuẩn bị chỗ đặt danh sách. Dòng 16 ghi tên danh sách, dòng 17 là hàng tiêu đề chép từ 5 ô A3:E3. Kết quả lọc không mang theo định dạng ngày của bảng gốc, nên bạn định dạng sẵn vùng D18:D27 kiểu Short Date để cột hạn sử dụng hiện ngày thay cho số như 46347.
Bạn chọn ô A18, gõ công thức sau rồi nhấn Enter:
=SORT(FILTER(A4:E13,F4:F13="Sắp hết hạn"),5)

Hàm FILTER lấy 5 cột từ A tới E của các dòng có trạng thái "Sắp hết hạn". Hàm SORT xếp kết quả theo cột thứ 5, tức là cột số ngày còn lại, từ nhỏ tới lớn. Mặt hàng gần hạn nhất vì vậy nằm ở dòng đầu.

Hàm FILTER và SORT có trong Excel 365 và Excel 2021. Nếu Excel của bạn không có hai hàm này, bạn chọn vùng A3:F13, mở tab Data, bấm Filter, rồi lọc cột Trạng thái theo "Sắp hết hạn". Nếu bạn muốn tìm hiểu thêm về hàm FILTER, bạn có thể đọc bài hàm FILTER trong Excel.
Excel có tự gửi thông báo khi đến ngày hết hạn không?
Excel không tự gửi email hay hiện thông báo khi file đang đóng. Hàm TODAY, cột trạng thái và màu tô chỉ cập nhật khi bạn mở file. Vì vậy, bạn nên mở file theo dõi theo lịch cố định, ví dụ mỗi sáng thứ Hai. Nếu bạn cần nhắc việc qua email, bạn cần dùng thêm công cụ khác như lịch Outlook.
Nếu ngày ở ô B1 không đổi khi bạn mở file vào ngày mới, file có thể đang ở chế độ tính tay. Bạn mở File, Options, Formulas, rồi chọn Automatic ở mục Calculation options. Bạn cũng có thể nhấn phím F9 để Excel tính lại ngay.
Bạn hay gặp lỗi gì khi làm cảnh báo ngày hết hạn?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Các dòng trống bên dưới bảng bị tô màu cam | Vùng tô màu kéo xuống các dòng chưa có dữ liệu. Excel xem ô trống là số 0, và số 0 nằm trong khoảng 0 tới 30. | Bạn chọn đúng vùng có dữ liệu, hoặc thêm điều kiện ô ngày hết hạn không trống: |
Các dòng đã hết hạn cũng bị tô màu cam | Công thức tô màu chỉ có điều kiện nhỏ hơn hoặc bằng 30, nên số âm cũng thỏa điều kiện. | Bạn dùng hàm AND với cả hai điều kiện |
Hợp đồng không xác định thời hạn hiện số âm và bị ghi "Đã hết hạn" | Ô thời hạn trống được tính là 0 tháng. Với hợp đồng của Đỗ Văn Khoa, số ngày còn lại thành -255. | Bạn bọc công thức ngày hết hạn trong |
Cột số ngày còn lại hiện dạng ngày, ví dụ 11/01/1900 | Ô đang có định dạng ngày, thường do bạn chép định dạng từ cột ngày sang. | Bạn chọn cột, mở tab Home và chọn General trong ô định dạng số. |
Công thức báo #VALUE! hoặc ra ngày sai | Ngày được gõ dạng chữ, thường nằm sát trái ô. Tùy cách đặt ngày của máy, Excel có thể không đọc được hoặc đọc nhầm ngày và tháng. | Bạn kiểm tra bằng |
Hàm DATEDIF báo #NUM! | Ngày hết hạn đã qua, nên ngày kết thúc nhỏ hơn ngày bắt đầu của hàm. | Bạn dùng phép trừ |
Màu tô lệch lên dòng trên | Công thức tô màu viết theo dòng khác với dòng đầu của vùng đã chọn, ví dụ viết | Bạn sửa công thức theo dòng đầu, tức là |
Công thức FILTER báo #CALC! | Không có mặt hàng nào thỏa điều kiện lọc. | Bạn bọc công thức trong IFERROR, ví dụ |
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.