Có thể bạn quan tâm: In Excel Trong 1 Trang: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mở Đầu
Excel là một trong những công cụ phổ biến nhất để xử lý dữ liệu, và tính số ngày giữa các ngày là một trong những nhu cầu thường gặp trong công việc kinh doanh, kế toán, quản lý dự án, cũng như trong đời sống cá nhân. Bài viết này sẽ cung cấp cho bạn một hướng dẫn toàn diện, chi tiết và thực tế về cách tính số ngày trong Excel, bao gồm:
- Các hàm cơ bản:
DATEDIF,NETWORKDAYS,NETWORKDAYS.INTL,WORKDAY,WORKDAY.INTL,YEARFRAC,EDATE,EOMONTH,DATE,TODAY,NOW. - Cách tính ngày làm việc vs ngày thường.
- Xử lý ngày lễ, ngày nghỉ cuối tuần tùy chỉnh.
- Các ví dụ thực tế trong các lĩnh vực như kế toán, quản lý dự án, nhân sự.
- Mẹo tối ưu công thức, tránh lỗi phổ biến.
- Sử dụng bảng dữ liệu (Table) và công cụ Power Query để tính ngày tự động.
- Cách tạo biểu đồ thời gian dựa trên số ngày tính được.
Bài viết được chia thành các phần rõ ràng, mỗi phần đi kèm ví dụ thực tế và hình ảnh mô phỏng (được mô tả bằng văn bản) để bạn có thể áp dụng ngay vào công việc của mình.
Có thể bạn quan tâm: Cách In 2 Mặt Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z
1. Hiểu Đúng Định Dạng Ngày Trong Excel
1.1. Định dạng ngày mặc định
- Excel lưu trữ ngày dưới dạng số nguyên dương, bắt đầu từ ngày 1/1/1900 (hoặc 1/1/1904 tùy vào cài đặt hệ thống). Ví dụ: ngày 1/1/1900 có giá trị 1, ngày 2/1/1900 có giá trị 2, …
- Thời gian (giờ, phút, giây) được lưu dưới dạng phần thập phân của ngày. Ví dụ:
0.5tương đương 12:00 PM (nửa ngày).
1.2. Kiểm tra và chuyển đổi định dạng
- Để kiểm tra một ô có phải là ngày hay không, bạn có thể dùng hàm
ISNUMBERkết hợp vớiCELL("format",A1). - Nếu ngày được nhập dưới dạng văn bản (text), bạn cần chuyển sang ngày thực bằng hàm
DATEVALUEhoặcTEXTvới định dạng"dd/mm/yyyy".
Ví dụ:
Giả sử A2 chứa giá trị "15-03-2026" (văn bản). Công thức =DATEVALUE(A2) sẽ trả về số nguyên tương ứng với ngày 15/03/2026.
Có thể bạn quan tâm: Cách Di Chuyển Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Hàm DATEDIF: Tính Khoảng Cách Giữa Hai Ngày
DATEDIF (Date Difference) là hàm ngầm trong Excel (không xuất hiện trong danh sách hàm), nhưng rất mạnh mẽ để tính khoảng thời gian giữa hai ngày theo các đơn vị: ngày, tháng, năm, hoặc kết hợp.
2.1. Cú pháp
=DATEDIF(start_date, end_date, unit)
- start_date: ngày bắt đầu.
- end_date: ngày kết thúc.
- unit: đơn vị tính, các ký hiệu:
"Y"– số năm nguyên."M"– số tháng nguyên."D"– số ngày."MD"– số ngày còn lại sau khi đã trừ năm và tháng."YM"– số tháng còn lại sau khi đã trừ năm."YD"– số ngày còn lại sau khi đã trừ năm.
2.2. Ví dụ thực tế
| A (Start) | B (End) | Công thức | Kết quả |
|---|---|---|---|
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"Y") | 2 năm |
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"M") | 26 tháng |
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"D") | 803 ngày |
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"MD") | 14 ngày |
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"YM") | 2 tháng |
| 01/01/2026 | 15/03/2026 | =DATEDIF(A2,B2,"YD") | 74 ngày |
2.3. Lưu ý khi dùng DATEDIF
start_datephải không lớn hơnend_date. Nếu ngược lại, hàm sẽ trả về lỗi#NUM!.- Khi tính
"MD"hoặc"YM"có thể nhận được kết quả âm nếu ngày cuối tháng không đủ ngày so với ngày bắt đầu (ví dụ: 31/01 -> 28/02). Để tránh, bạn có thể dùng hàmEOMONTHđể chuẩn hoá.
3. Hàm NETWORKDAYS và NETWORKDAYS.INTL: Tính Ngày Làm Việc
3.1. NETWORKDAYS – Ngày làm việc tiêu chuẩn (Thứ Hai – Thứ Sáu)
Cú pháp
=NETWORKDAYS(start_date, end_date, holidays)
- holidays (tùy chọn): một dải ô chứa danh sách ngày lễ (các ngày không làm việc).
Ví dụ
Giả sử:
- A2 = 01/03/2026 (ngày bắt đầu)
- B2 = 31/03/2026 (ngày kết thúc)
- D2:D5 chứa các ngày lễ: 08/03/2026, 10/04/2026, …
Công thức:
=NETWORKDAYS(A2,B2,D2:D5)
Kết quả sẽ là số ngày làm việc trong tháng 3, trừ các ngày lễ.
3.2. NETWORKDAYS.INTL – Tùy chỉnh ngày nghỉ cuối tuần
Cú pháp
=NETWORKDAYS.INTL(start_date, end_date, weekend, holidays)
- weekend: chuỗi 7 ký tự (“1” là ngày nghỉ, “0” là ngày làm việc) bắt đầu từ thứ Hai.
- Ví dụ
"0000011": chỉ nghỉ Thứ Bảy và Chủ Nhật (mặc định). "1111110": chỉ nghỉ Chủ Nhật."0000000": không có ngày nghỉ cuối tuần (tính cả ngày cuối tuần).
Ví dụ thực tế
Bạn làm việc ở một quốc gia có ngày nghỉ là Thứ Bảy và Chủ Nhật, nhưng còn có ngày nghỉ Thứ Hai vào ngày 01/05/2026.
=NETWORKDAYS.INTL(A2,B2,"0000011",D2:D5)
Nếu muốn tính ngày làm việc khi chỉ nghỉ Chủ Nhật, dùng "1111110".
3.3. Khi nào dùng NETWORKDAYS vs WORKDAY?
NETWORKDAYStrả về số ngày làm việc trong khoảng.WORKDAYtrả về ngày làm việc tiếp theo sau một số ngày làm việc đã cho.
4. Hàm WORKDAY và WORKDAY.INTL: Tìm Ngày Làm Việc Tiếp Theo
4.1. Cú pháp WORKDAY
=WORKDAY(start_date, days, holidays)
- days: số ngày làm việc cần cộng (có thể âm để trừ).
4.2. Ví dụ
Bạn muốn biết ngày làm việc thứ 10 sau ngày 01/04/2026, trừ các ngày lễ trong cột D.
=WORKDAY(DATE(2026,4,1),10,D2:D10)
Kết quả có thể là 15/04/2026 (tùy vào ngày lễ).
4.3. WORKDAY.INTL – Tùy chỉnh cuối tuần
Cú pháp tương tự WORKDAY nhưng thêm tham số weekend.
=WORKDAY.INTL(start_date, days, weekend, holidays)
Ví dụ: công ty chỉ nghỉ Chủ Nhật, bạn muốn tính ngày làm việc thứ 5:
=WORKDAY.INTL(DATE(2026,5,1),5,"1111110")
5. Hàm YEARFRAC: Tính Phần Thập Phân Năm Giữa Hai Ngày
YEARFRAC trả về số năm dưới dạng thập phân giữa hai ngày, hữu ích trong tính lãi suất, khấu hao, hoặc tính thời gian công tác.
Cú pháp
=YEARFRAC(start_date, end_date, basis)
- basis: cách tính ngày (0 = US (NASD) 30/360, 1 = Actual/Actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360).
Ví dụ
=YEARFRAC(DATE(2026,1,1), DATE(2026,12,31), 1)
Kết quả: 0.99726 (vì năm 2026 là năm nhuận, có 366 ngày).
6. Các Hàm Hỗ Trợ Khác
Có thể bạn quan tâm: Pivot Table Excel Là Gì? – Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao
| Hàm | Mô tả | Ví dụ |
|---|---|---|
EDATE(start_date, months) | Thêm hoặc trừ số tháng | =EDATE(A2,3) → 3 tháng sau ngày A2 |
EOMONTH(start_date, months) | Ngày cuối cùng của tháng | =EOMONTH(A2,0) → cuối tháng của A2 |
DATE(year, month, day) | Tạo ngày từ các thành phần | =DATE(2026,2,29) → 29/02/2026 (năm nhuận) |
TODAY() | Ngày hiện tại | =TODAY() |
NOW() | Ngày + giờ hiện tại | =NOW() |
TEXT(value, format_text) | Định dạng ngày thành text | =TEXT(A2,"dd/mm/yyyy") |
DATEVALUE(text) | Chuyển text thành ngày | =DATEVALUE("15/03/2026") |
7. Ứng Dụng Thực Tế
7.1. Kế toán: Tính Số Ngày Còn Nợ
Giả sử cột A chứa Ngày phát sinh, cột B chứa Ngày thanh toán. Bạn muốn tính số ngày chậm trả (nếu có).
=MAX(0, B2 - A2)
Nếu muốn trừ ngày nghỉ cuối tuần và ngày lễ:
=NETWORKDAYS(A2,B2,HolidayRange) - 1
(Trừ 1 vì NETWORKDAYS tính cả ngày bắt đầu).
7.2. Quản Lý Dự Án: Tính Thời Gian Hoàn Thành
Dòng công việc có Ngày bắt đầu (C2) và Ngày dự kiến kết thúc (D2). Để tính thời gian thực tế (ngày làm việc):
=NETWORKDAYS(C2,D2,HolidayRange)
Nếu muốn biết ngày hoàn thành dự kiến khi có số ngày làm việc (E2):
=WORKDAY.INTL(C2,E2,"0000011",HolidayRange)
7.3. Nhân Sự: Tính Thâm Niên
Thâm niên (số năm) tính theo ngày vào làm (F2) và ngày hiện tại:
=DATEDIF(F2,TODAY(),"Y") & " năm " & DATEDIF(F2,TODAY(),"YM") & " tháng"
Kết quả: 3 năm 2 tháng.
8. Xử Lý Ngày Lễ và Ngày Nghỉ Cuối Tuần Tùy Chỉnh
8.1. Tạo danh sách ngày lễ
- Nhập ngày lễ vào một cột (ví dụ:
Holiday!A2:A20). - Đảm bảo các ô được định dạng là Ngày.
- Khi dùng hàm
NETWORKDAYShoặcWORKDAY, truyền dải này vào tham sốholidays.
8.2. Định dạng ngày lễ bằng công thức
Nếu ngày lễ thay đổi mỗi năm (ví dụ: Tết Nguyên Đán), bạn có thể tạo công thức tự động:
=DATE(YEAR(TODAY()),1,1) + 30 // Giả sử ngày lễ là 30 ngày sau ngày 1/1
Hoặc dùng hàm CHOOSE và WEEKDAY để tính Ngày Lễ Di Động (ví dụ: Lễ Phục Sinh).
8.3. Đánh dấu ngày cuối tuần trong bảng
Bạn có thể dùng Conditional Formatting để tô màu các ngày cuối tuần:
- Chọn dải ngày.
- Vào Home > Conditional Formatting > New Rule > Use a formula.
- Công thức:
=WEEKDAY(A1,2)>5(trong đó2đưa tuần bắt đầu từ Thứ Hai). - Đặt màu nền.
9. Tối Ưu Công Thức và Tránh Lỗi Thông Thường
| Lỗi thường gặp | Nguyên nhân | Cách khắc phục |
|---|---|---|
#VALUE! | Định dạng ngày không đúng (text) | Dùng DATEVALUE hoặc -- để chuyển. |
#NUM! | start_date > end_date trong DATEDIF | Đảo lại thứ tự hoặc dùng ABS. |
Kết quả âm trong DATEDIF(...,"MD") | Tháng không đủ ngày | Dùng EOMONTH để chuẩn hoá. |
| Sai ngày nghỉ cuối tuần | Không chỉ định đúng weekend trong NETWORKDAYS.INTL | Kiểm tra chuỗi 7 ký tự, bắt đầu từ Thứ Hai. |
| Không tính ngày lễ | Danh sách ngày lễ không khớp định dạng | Đảm bảo cột ngày lễ là dạng Date, không có khoảng trắng. |
9.1. Sử dụng -- để ép kiểu nhanh
Nếu ô A2 chứa ngày dạng text "15/03/2026", công thức sau sẽ ép kiểu:
=--A2
Kết quả là số nguyên ngày trong Excel, có thể dùng ngay trong các hàm tính ngày.
9.2. Kết hợp IFERROR để tránh lỗi hiển thị
=IFERROR(DATEDIF(A2,B2,"D"),"Ngày không hợp lệ")
10. Sử Dụng Power Query Để Tự Động Tính Ngày
Power Query (Get & Transform) cho phép bạn xử lý dữ liệu lớn và tự động tính ngày mà không cần viết công thức trong các ô.
10.1. Các bước cơ bản
- Import dữ liệu: Data > Get Data > From Table/Range.
- Trong Power Query Editor, chọn cột ngày bắt đầu và ngày kết thúc.
- Vào Add Column > Custom Column và nhập công thức M:
Duration.Days(EndDate - StartDate)
- Đặt tên cột mới là
SốNgày. - Nếu muốn tính ngày làm việc, dùng hàm List.Dates và List.Count kết hợp với danh sách ngày lễ.
10.2. Ví dụ tính ngày làm việc trong Power Query
Giả sử bạn có bảng Holiday chứa ngày lễ.
let Source = Excel.CurrentWorkbook(){Name="Data"}Content, AddDays = Table.AddColumn(Source, "NgàyLàmViệc", each List.Count( List.Select( List.Dates(StartDate, Duration.Days(EndDate-StartDate)+1, #duration(1,0,0,0)), each not List.Contains(HolidayDate, _) and Date.DayOfWeek(_, Day.Monday) < 5 ) ))
in AddDays
Công thức trên:
- Tạo danh sách các ngày trong khoảng.
- Loại bỏ ngày lễ (
HolidayDate). - Loại bỏ ngày cuối tuần (DayOfWeek < 5 tương đương Thứ Hai – Thứ Sáu).
- Đếm số phần tử còn lại → số ngày làm việc.
Sau khi Close & Load, bảng mới sẽ có cột NgàyLàmViệc tính tự động.
11. Tạo Biểu Đồ Thời Gian Dựa Trên Số Ngày
Khi đã có cột Số ngày hoặc Ngày kết thúc, bạn có thể vẽ biểu đồ Gantt để hiển thị tiến độ dự án.
11.1. Các bước nhanh
- Chuẩn bị dữ liệu: Cột
Task,StartDate,Duration(số ngày làm việc). - Chọn Insert > Bar Chart > Stacked Bar.
- Đặt
StartDatelàm Series 1,Durationlàm Series 2. - Định dạng Series 1 (StartDate) với màu Không màu (No Fill) để chỉ hiển thị thanh
Duration. - Đảo trục Y để hiển thị nhiệm vụ từ trên xuống dưới.
Kết quả: biểu đồ Gantt hiển thị thời gian thực hiện từng công việc, dựa trên ngày tính được.
12. Tổng Kết
- Excel cung cấp một loạt hàm mạnh mẽ để tính ngày:
DATEDIF,NETWORKDAYS,WORKDAY,YEARFRAC, và các hàm hỗ trợ (EDATE,EOMONTH,DATE,TODAY,NOW). - Ngày làm việc và ngày nghỉ cuối tuần có thể tùy chỉnh bằng các phiên bản mở rộng (
NETWORKDAYS.INTL,WORKDAY.INTL). - Ngày lễ cần được quản lý trong một danh sách riêng, và luôn định dạng đúng để các hàm nhận diện.
- Khi làm việc với dữ liệu lớn, Power Query giúp tự động hoá tính toán ngày mà không cần công thức ô.
- Để tránh lỗi, luôn kiểm tra định dạng ngày, sử dụng
IFERROR, và kiểm tra thứ tự ngày trongDATEDIF. - Cuối cùng, biểu đồ Gantt và các báo cáo thời gian giúp truyền đạt kết quả tính ngày một cách trực quan.
Hy vọng bài viết này đã cung cấp cho bạn một cẩm nang toàn diện về cách tính số ngày trong Excel, từ những nhu cầu cơ bản đến những yêu cầu nâng cao và tự động hoá. Hãy áp dụng ngay vào công việc của mình và tận dụng tối đa sức mạnh của Excel!
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ẻ.