Hàm điều kiện thời gian trong Excel - Ví dụ chi tiết

Hàm & Công ThứcHà Phạm0

Mục lục nội dung

Excel cung cấp nhiều hàm xử lý thời gian giúp bạn phân tích dữ liệu theo phút, giờ, ngày, tháng, năm... Bài viết này từ Excel.net.vn sẽ hướng dẫn cách tính theo điều kiện thời gian trong Excel một cách chi tiết, dễ hiểu và thực tế.

I. Cách dùng hàm DATE cơ bản trong Excel

Hàm DATE là hàm tính toán có sẵn trong Excel, hàm DATE trả về kết quả là số seri liên tiếp đại diện cho một ngày cụ thể.

Lưu ý: Excel lưu trữ ngày tháng dưới dạng số sêri liên tiếp để có thể sử dụng trong tính toán. 01/01/1900 có số sê-ri là 1 và 01/01/2008 có số sê-ri là 39448 vì ngày đó ở sau 01/01/1900 39.447 ngày.

Cú pháp:

DATE(year,month,day)

Trong đó:

year: số chỉ số năm, Giá trị của year có thể bao gồm từ một đến bốn chữ số, năm đầu tiên mà hệ thống Excel hiểu là 1900. Nếu year từ 0 (không) đến 1899 (gồm cả 0 và 1899), Excel sẽ thêm giá trị đó vào 1900 để tính năm. Vì vậy để Excel trả về kết quả chính xác, bạn nên sử dụng bốn số thể hiện năm;
month: là số chỉ số tháng, Số nguyên dương hoặc âm đại diện cho tháng trong năm từ 1 đến 12;
day: là số chỉ số ngày, Số nguyên dương hoặc âm đại diện cho ngày của tháng từ 1 đến 31. Nếu như bạn gõ số ngày không có trong tháng đó thì hàm DATE tự động chuyển sang ngày sau đó.

Ví dụ bạn gõ công thức vào một ô bất kì =DATE(2023,1,34) hàm sẽ trả về kết quả là 3/2/2023.

Nhập công thức hàm DATE

Một số trường hợp, nếu hàm DATE trả về kết quả là định dạng số Sêri thì bạn chỉ cần điều chỉnh lại định dạng ô để thể hiện về giá trị mà bạn muốn xem. Bằng cách nhấn chuột vào ô cần chỉnh định dạng, chọn mũi tên thả xuống trên thanh công cụ Number để lựa chọn định dạng ngày tháng (Long Date hoặc Short Date) hoặc mở rộng More Number Format… để lựa chọn nhiều kiểu định dạng khác nhau.

Chọn định dạng trong thanh công cụ Number

1. Dùng hàm DATE để cộng trừ ngày trong khoảng thời gian

Ví dụ bạn có bảng dữ liệu sau, cần tìm ngày mà các thành viên đạt 50 tuổi để công ty có chính sách tới thành viên:

Dùng hàm DATE để cộng trừ ngày trong khoảng thời gian

Dựa vào cấu trúc của hàm DATE, ở ô đầu tiên =DATE(F5+50,E5,D5) và chúng ta thu được kết quả:

Nhập công thức =DATE(F5+50,E5,D5)

Tương tự nếu bạn muốn dùng công thức DATE để xác định ngày kỷ niệm như 5 năm vào công ty, năm đến tuổi làm căn cước công dân, …

2. Dùng hàm DATE để định dạng có điều kiện

Bạn có thể kết hợp hàm DATE và công cụ Conditional Formatting để định dạng các dòng có giá trị ngày theo điều kiện. Tiếp tục với ví dụ trên, chúng ta cần bôi màu ô tính có giá trị ngày dưới năm 01/01/2025.

Bước 1: Bôi đen vùng dữ liệu cần so sánh => Nhấn chuột vào mũi tên kéo thả ở mục Conditional formating (1) => Chọn New Rule…(2).

Chọn New Rule

Bước 2: Cửa sổ New Formatting Rule hiện ra => Nhấn chuột chọn Use a formula to determine which cells to format (1) => Mục Format values where this formula is true (2) bạn gõ công thức như ảnh dưới => Nhấn chuột chọn nút Format… (3) để lựa chọn định dạng mà bạn muốn.

Chọn Format

Bước 3: Trong Format Cells tìm đến phần Fill bạn chọn định dạng cần thể hiện rồi nhấn nút OK.

Chọn định dạng cần thể hiện

Bước 4: Quay trở lại cửa sổ Edit Formatting Rule nhấn OK để xác nhận.

Chọn OK để xác nhận

II. Cách dùng hàm DATEDIF trong Excel

Hàm DATEIF là hàm dùng để xác định khoảng cách giữa 2 mốc thời gian, hàm sẽ trả về giá trị lỗi #NUM! nếu như mốc thời gian sau nhỏ hơn mốc thời gian trước.

Cú pháp:

=DATEDIF(start_date,end_date,unit)

Trong đó:

start_date: Ngày bắt đầu của khoảng thời gian
end_date: Ngày cuối cùng của khoảng thời gian
unit: Loại thông tin mà bạn muốn trả về.
- "Y": Số năm hoàn tất trong khoảng thời gian.
- "M": Số tháng hoàn tất trong khoảng thời gian.
- "D": Số ngày trong khoảng thời gian.
- "MD": Sự khác biệt giữa các ngày trong start_date và end_date. Đã bỏ qua tháng và năm của ngày.
- "YM": Sự khác biệt giữa các tháng trong start_date và end_date. Đã bỏ qua ngày và năm của ngày
- "YD": Sự khác biệt giữa các ngày trong start_date và end_date. Đã bỏ qua năm của ngày.

Mỗi lựa chọn của unit khác nhau sẽ trả về kết quả khác nhau, sau đây là một số ví dụ về cách sử dụng hàm DATEIF:

1. Dùng hàm DATEDIF để tính số ngày trong khoảng thời gian

Ví dụ bạn có bảng tính dưới, bạn cần tính số ngày từ ngày sinh tới ngày 10/01/2024.

Công thức áp dụng như sau:

Ô E5 =DATEDIF(D5,"10/01/2024","D"), sao chép công thức cho toàn bộ các ô còn lại trong cột.

Áp dụng hàm DATEDIF

Lưu ý: Một số trường hợp Excel sẽ tự động chuyển sang định dạng ngày tháng. Nếu như bạn nhận được kết quả là định dạng ngày tháng thì chỉnh lại định dạng ô tính ở mục Number trên thanh công cụ và chọn General hoặc Number.

Chọn General hoặc Number

2. Dùng hàm DATEDIF để tính số tuần trong khoảng thời gian

Để tính số tuần trong khoảng thời gian, trước tiên chúng ta cần tính số ngày trong khoảng thời gian đó, và lấy số ngày chia cho 7 (1 tuần có 7 ngày) và thu được số tuần trong khoảng thời gian.

Dùng hàm DATEDIF để tính số tuần trong khoảng thời gian