Có thể bạn quan tâm: Excel Cho Quản Lý Chuỗi Cung Ứng: Công Cụ Đắc Lực Đưa Doanh Nghiệp Lên Tầm Cao Mới

Mục lục bài viết

Mở Đầu

Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ tính toán đơn thuần mà còn là một nền tảng mạnh mẽ để xử lý và phân tích dữ liệu thời gian. Việc làm việc với khoảng thời gian (date range) là một trong những nhu cầu phổ biến nhất: tính số ngày giữa hai ngày, lọc dữ liệu trong một khoảng thời gian, tạo báo cáo tuần, tháng, quý, hoặc so sánh doanh thu theo các chu kỳ thời gian khác nhau. Tuy nhiên, nhiều người dùng vẫn gặp khó khăn khi phải xây dựng các công thức (formula) để xử lý các yêu cầu này một cách chính xác và hiệu quả.

Bài viết này sẽ cung cấp hướng dẫn toàn diện về các công thức Excel liên quan đến khoảng thời gian, từ các hàm cơ bản như DATEDIF, NETWORKDAYS, EDATE, EOMONTH cho tới các công thức phức tạp sử dụng hàm ARRAY, SUMPRODUCT, FILTER (trong Excel 365) và POWER QUERY. Mục tiêu là giúp bạn nắm vững cách tính toán, lọc, và phân tích dữ liệu theo ngày một cách nhanh chóng, giảm thiểu lỗi và tối ưu thời gian làm việc.

Lưu ý: Toàn bộ ví dụ trong bài sẽ sử dụng chuẩn ngày tháng dd/mm/yyyy (ngày/tháng/năm) – bạn có thể thay đổi định dạng theo cài đặt hệ thống của mình (mm/dd/yyyy, yyyy-mm-dd, …). Các công thức vẫn hoạt động tương tự, chỉ cần chú ý tới cách nhập ngày trong ô.

Có thể bạn quan tâm: Cách Tự Động Điều Chỉnh Kích Thước Ô Trong Excel Để Nội Dung Hiển Thị Đầy Đủ (fit Cell To Text)

1. Kiến Thức Cơ Bản Về Ngày Tháng Trong Excel

1.1. Excel Lưu Trữ Ngày Dưới Dạng Số Thứ Tự

Ngày 1/1/1900 được lưu là 1.
Ngày 2/1/1900 là 2, và cứ tiếp tục như vậy.
Vì vậy, số ngày giữa hai ngày là hiệu số của các giá trị số này.

Ví dụ: =DATE(2026,6,26) - DATE(2026,6,1) trả về 25, tức là có 25 ngày chênh lệch.

1.2. Các Hàm Ngày Tháng Cơ Bản

Hàm	Mô tả	Cú pháp
`DATE(year, month, day)`	Tạo ngày từ các thành phần	`=DATE(2026,12,31)`
`TODAY()`	Trả về ngày hiện tại	`=TODAY()`
`NOW()`	Trả về ngày và giờ hiện tại	`=NOW()`
`YEAR(date)`, `MONTH(date)`, `DAY(date)`	Trích xuất thành phần	`=YEAR(A2)`
`TEXT(date, "dd/mm/yyyy")`	Định dạng ngày thành chuỗi	`=TEXT(A2,"dd/mm/yyyy")`

Có thể bạn quan tâm: Cách Tìm Ngày Đầu Tháng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Tính Số Ngày Giữa Hai Ngày

2.1. Công Thức Cơ Bản: Hiệu Trừ Hai Ngày

= B2 - A2

A2: ngày bắt đầu
B2: ngày kết thúc
Kết quả: số ngày (không bao gồm ngày cuối nếu muốn bao gồm, cộng thêm 1).

2.2. Hàm `DATEDIF` – Đo Khoảng Cách Thời Gian Linh Hoạt

DATEDIF không xuất hiện trong danh sách hàm của Excel nhưng vẫn hoạt động. Cú pháp:

=DATEDIF(start_date, end_date, unit)

unit có thể là:
"Y" – số năm hoàn chỉnh
"M" – số tháng hoàn chỉnh
"D" – số ngày
"MD" – ngày còn lại sau khi trừ năm và tháng
"YM" – tháng còn lại sau khi trừ năm
"YD" – ngày còn lại sau khi trừ năm

Ví dụ: Tính số tháng và ngày giữa 01/01/2026 và 15/04/2026:

=DATEDIF(A2, B2, "M") & " tháng " & DATEDIF(A2, B2, "MD") & " ngày"

2.3. Tính Số Ngày Làm Việc (Không tính cuối tuần) – `NETWORKDAYS`

=NETWORKDAYS(start_date, end_date, holidays)

holidays là phạm vi chứa ngày lễ (có thể bỏ qua).
Trả về số ngày làm việc (Monday‑Friday) trong khoảng.

Ví dụ: 01/05/2026 – 31/05/2026, có ngày lễ 01/05 và 03/05:

=NETWORKDAYS(DATE(2026,5,1), DATE(2026,5,31), {DATE(2026,5,1), DATE(2026,5,3)})

Kết quả: 21 ngày làm việc.

2.4. Tính Số Ngày Làm Việc Với Thứ Hai‑Thứ Sáu + Lịch Nghỉ (cụ thể) – `NETWORKDAYS.INTL`

=NETWORKDAYS.INTL(start_date, end_date, weekend, holidays)

weekend là chuỗi 7 ký tự “0” (làm việc) hoặc “1” (ngày nghỉ), bắt đầu từ Monday.
Ví dụ: "0000011" – chỉ nghỉ Thứ Bảy và Chủ Nhật (mặc định).

Nếu công ty nghỉ thứ Bảy, Chủ Nhật và Thứ Hai, sử dụng "1110011".

3. Tạo Danh Sách Ngày Trong Một Khoảng Thời Gian

3.1. Sử Dụng Công Thức Động (Dynamic Array) – Excel 365/2026

Giả sử muốn liệt kê tất cả các ngày từ 01/03/2026 đến 10/03/2026:

=SEQUENCE(end_date - start_date + 1, 1, start_date, 1)

SEQUENCE(rows, columns, start, step)
Đặt công thức trong ô C2, kết quả sẽ tự động tràn (spill) ra cột C.

3.2. Sử Dụng `FILTER` Để Lọc Ngày Theo Điều Kiện

Nếu có bảng dữ liệu A2:D100 với cột B là ngày, muốn lấy các bản ghi trong tháng 4/2026:

=FILTER(A2:D100, (MONTH(B2:B100)=4)(YEAR(B2:B100)=2026))

3.3. Sử Dụng `ROW` Kết Hợp `INDEX` Đối Với Phiên Bản Cũ (Excel 2026‑)

=IF(ROW(A1) <= $B$2-$A$2+1, $A$2 + ROW(A1)-1, "")

Đặt công thức trong C2, kéo xuống đủ số dòng.
$A$2 là ngày bắt đầu, $B$2 là ngày kết thúc.

4. Tổng Hợp Dữ Liệu Theo Khoảng Thời Gian

4.1. Tổng Giá Trị Trong Khoảng Ngày – `SUMIFS`

Giả sử cột A chứa ngày, cột B chứa doanh thu, muốn tính tổng doanh thu từ 01/01/2026 đến 31/01/2026:

=SUMIFS(B:B, A:A, ">=01/01/2026", A:A, "<=31/01/2026")

Lưu ý: Khi nhập ngày trong công thức, nên dùng hàm DATE để tránh lỗi định dạng:

=SUMIFS(B:B, A:A, ">= "&DATE(2026,1,1), A:A, "<= "&DATE(2026,1,31))

4.2. Đếm Số Bản Ghi Trong Khoảng Thời Gian – `COUNTIFS`

=COUNTIFS(A:A, ">= "&DATE(2026,1,1), A:A, "<= "&DATE(2026,1,31))

4.3. Trung Bình, Min, Max Trong Khoảng Thời Gian

=AVERAGEIFS(B:B, A:A, ">= "&DATE(2026,1,1), A:A, "<= "&DATE(2026,1,31))
=MINIFS(B:B, A:A, ">= "&DATE(2026,1,1), A:A, "<= "&DATE(2026,1,31))
=MAXIFS(B:B, A:A, ">= "&DATE(2026,1,1), A:A, "<= "&DATE(2026,1,31))

4.4. Sử Dụng `SUMPRODUCT` Khi Không Có `SUMIFS` (Phiên Bản Cũ)

=SUMPRODUCT((A2:A100>=DATE(2026,1,1))(A2:A100<=DATE(2026,1,31))B2:B100)

5. Tính Khoảng Thời Gian Theo Tuần, Tháng, Quý

5.1. Xác Định Tuần Năm (ISO Week Number)

=ISOWEEKNUM(date)

Đối với Excel 2026 trở lên. Trả về số tuần trong năm (1‑53).

5.2. Tính Ngày Đầu Và Cuối Của Tuần

Có thể bạn quan tâm: Excel Formula Does Not Calculate: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết

=DATE(YEAR(date),1,1) - WEEKDAY(DATE(YEAR(date),1,1),2) + (week_no-1)7 + 1 // ngày đầu tuần
=DATE(YEAR(date),1,1) - WEEKDAY(DATE(YEAR(date),1,1),2) + week_no7 // ngày cuối tuần

Hoặc dùng A2 là ngày bất kỳ:

= A2 - WEEKDAY(A2,2) + 1 // Thứ Hai của tuần chứa A2
= A2 + (7 - WEEKDAY(A2,2)) // Chủ Nhật của tuần chứa A2

5.3. Tính Ngày Đầu Và Cuối Tháng

=EOMONTH(date,0)-DAY(EOMONTH(date,0))+1 // ngày đầu tháng
=EOMONTH(date,0) // ngày cuối tháng

5.4. Tính Ngày Đầu Và Cuối Quý

=DATE(YEAR(date),INT((MONTH(date)-1)/3)3+1,1) // đầu quý
=EOMONTH(DATE(YEAR(date),INT((MONTH(date)-1)/3)3+3,1),0) // cuối quý

5.5. Tổng Hợp Dữ Liệu Theo Quý – Ví Dụ Pivot Table

Thêm cột Quarter trong bảng dữ liệu:
excel = "Q" & INT((MONTH(A2)-1)/3)+1 & " " & YEAR(A2)
Sử dụng Pivot Table → Rows: Quarter, Values: Sum of Sales.

6. Xử Lý Các Định Dạng Ngày Không Đồng Nhất

Trong thực tế, dữ liệu thường đến từ nhiều nguồn, ngày tháng có thể ở dạng text (“2026‑04‑01”, “01/04/2026”, “April 1, 2026”). Để chuẩn hoá:

6.1. Chuyển Text Thành Date

=DATEVALUE(A2) // nếu dạng dd/mm/yyyy hoặc mm/dd/yyyy tùy locale
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) // nếu dạng "dd/mm/yyyy"
=DATE(MID(A2,4,2), LEFT(A2,2), RIGHT(A2,4)) // nếu dạng "mm/dd/yyyy"

6.2. Sử Dụng `TEXT` Để Định Dạng Lại

=TEXT(A2, "dd/mm/yyyy")

6.3. Kiểm Tra Giá Trị Hợp Lệ

=IF(ISNUMBER(A2), "OK", "Invalid")

7. Các Tình Huống Thực Tế Và Giải Pháp Công Thức

7.1. Tính Tuổi Từ Ngày Sinh

=DATEDIF(B2, TODAY(), "Y") & " tuổi"

B2 chứa ngày sinh.

7.2. Kiểm Tra Hợp Đồng Hết Hạn Trong 30 Ngày

=IF(AND(A2>=TODAY(), A2<=TODAY()+30), "Sắp hết hạn", "OK")

7.3. Đếm Số Giao Dịch Trong 7 Ngày Gần Nhất

=COUNTIFS(A:A, ">= "&TODAY()-6, A:A, "<="&TODAY())

7.4. Tạo Lịch Tháng Tự Động Dựa Trên Năm Và Tháng Đầu Vào

Giả sử ô B1: năm, ô C1: tháng.

=SEQUENCE(DAY(EOMONTH(DATE(B1,C1,1),0)),1,DATE(B1,C1,1),1)

7.5. Tính Số Ngày Giữa Hai Ngày Đúng Ngày Làm Việc (Không tính cuối tuần & ngày lễ)

=NETWORKDAYS.INTL(start_date, end_date, "0000011", holidays_range)

Trong đó "0000011" = nghỉ Thứ Bảy & Chủ Nhật.

7.6. Lấy Giá Trị Gần Nhất Trước Ngày X (Lookup Ngày)

=LOOKUP(date, A:A, B:B)

A:A là cột ngày đã sắp xếp tăng dần, B:B là giá trị cần lấy.

7.7. Kiểm Tra Nếu Hai Khoảng Ngày Giao Nhau

=IF(AND(end1>=start2, end2>=start1), "Giao nhau", "Không giao")

start1, end1 là khoảng 1; start2, end2 là khoảng 2.

8. Tự Động Cập Nhật Báo Cáo Hàng Tháng Với `OFFSET` và `COUNTA`

Giả sử dữ liệu ngày trong cột A, giá trị trong cột B, muốn tính tổng trong tháng hiện tại:

=SUM(OFFSET(B1, MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1), A:A,0)-1, 0, COUNTIFS(A:A, ">= "&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A:A, "<=" &EOMONTH(TODAY(),0))))

9. Sử Dụng Power Query Để Xử Lý Khoảng Thời Gian

Power Query (Get & Transform) cung cấp giao diện mạnh mẽ:

Import Dữ Liệu → Transform Data.
Đổi kiểu cột ngày thành Date.
Thêm cột Custom → công thức Date.AddDays(StartDate, 0) để tạo danh sách ngày.
Sử dụng Group By → Month, Quarter để tổng hợp.

Power Query đặc biệt hữu ích khi:

Nạp dữ liệu hàng triệu bản ghi.
Cần tái sử dụng quy trình chuyển đổi.

10. Những Sai Lầm Thường Gặp Khi Làm Việc Với Date Range

Sai Lầm	Nguyên Nhân	Cách Khắc Phục
Nhập ngày dưới dạng text và tính trực tiếp	Excel coi là chuỗi, không tính được	Dùng `DATEVALUE` hoặc chuyển sang kiểu Date
Sử dụng `TODAY()` trong công thức cố định và không cập nhật	Khi sao chép công thức, giá trị thay đổi	Sử dụng một ô chứa ngày cố định (ví dụ: `=TODAY()` trong ô D1) và tham chiếu
Không tính ngày lễ trong `NETWORKDAYS`	Kết quả sai lệch	Tạo danh sách ngày lễ và truyền vào đối số thứ ba
Dùng `=` để so sánh ngày trong `IF` mà ngày ở dạng text	Luôn trả về FALSE	Đảm bảo cả hai bên đều là kiểu Date
Không khóa ô khi kéo công thức (`$A$2`)	Tham chiếu sai khi sao chép	Sử dụng tham chiếu tuyệt đối cho ngày cố định

11. Tối Ưu Hóa Công Thức Đối Với Dữ Liệu Lớn

Tránh dùng toàn bộ cột (A:A) trong các hàm tính tổng nếu dữ liệu lớn; dùng phạm vi thực tế (A2:A10000).
Sử dụng bảng (Table): Khi dữ liệu được định dạng thành Table (Ctrl+T), công thức sẽ tự mở rộng và dễ bảo trì.
Sử dụng SUMIFS/COUNTIFS thay cho ARRAYFORMULA trong các phiên bản cũ vì chúng nhanh hơn.
Kết hợp Power Pivot: Khi cần tính toán phức tạp, tạo mô hình dữ liệu và dùng DAX (Data Analysis Expressions) cho phép tính toán nhanh hơn hàng triệu bản ghi.

12. Tổng Kết

Xử lý khoảng thời gian trong Excel không chỉ là việc trừ ngày mà còn bao gồm:

Xác định số ngày làm việc, ngày lễ, tuần, tháng, quý.
Liệt kê các ngày trong khoảng.
Tổng hợp, đếm, trung bình, min/max dữ liệu theo ngày.
Kiểm tra giao nhau, hợp lệ, độ trễ.
Tự động hoá báo cáo bằng công thức động, Power Query, và Power Pivot.

Với các công thức và kỹ thuật được trình bày ở trên, bạn có thể:

Tạo báo cáo thời gian chính xác, nhanh chóng.
Lọc và phân tích dữ liệu theo bất kỳ khoảng thời gian nào.
Giảm thiểu lỗi do định dạng ngày không đồng nhất.
Mở rộng giải pháp cho dữ liệu lớn và đa dạng nguồn.

Hãy áp dụng ngay các mẫu công thức này vào công việc của bạn để nâng cao năng suất và độ chính xác trong phân tích dữ liệu thời gian. Nếu bạn gặp bất kỳ trường hợp đặc biệt nào (ví dụ: lịch làm việc theo ca, ngày nghỉ không cố định), đừng ngần ngại kết hợp các hàm CHOOSE, VLOOKUP/XLOOKUP cùng NETWORKDAYS.INTL để tùy chỉnh phù hợp.

Chúc bạn thành công trong việc điều khiển thời gian bằng Excel!

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