Có thể bạn quan tâm: Hàm Cộng Trừ Nhân Chia Trong Excel: Cách Sử Dụng, Ví Dụ Thực Tế Và Mẹo Tối Ưu
Mở Đầu
Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ để lưu trữ dữ liệu mà còn là một nền tảng mạnh mẽ để thực hiện các phép tính phức tạp, trong đó việc tính thời gian chiếm một vị trí quan trọng. Dù bạn là nhân viên kế toán, quản lý dự án, nhân sự hay giáo viên, việc hiểu và áp dụng đúng các công thức tính thời gian trong Excel sẽ giúp bạn tiết kiệm thời gian, giảm thiểu sai sót và đưa ra quyết định nhanh chóng, chính xác hơn.
Bài viết dưới đây sẽ cung cấp cho bạn một hướng dẫn toàn diện, chi tiết và thực tiễn về cách tính thời gian trong Excel, bao gồm:
- Cơ bản về định dạng thời gian trong Excel
- Các hàm thời gian cơ bản: NOW, TODAY, TIME, DATE
- Cách tính khoảng thời gian (độ lệch) giữa hai ngày/giờ
- Tính tổng thời gian làm việc, giờ overtime
- Xử lý thời gian vượt ngày (over 24h) và định dạng tùy chỉnh
- Các hàm nâng cao: NETWORKDAYS, WORKDAY, DATEDIF, TEXT
- Ứng dụng thực tế: tính lương, tính tiến độ dự án, tính tuổi, tính ngày hết hạn
- Mẹo và thủ thuật: sử dụng bảng phụ, công cụ Conditional Formatting, PivotTable
- Lưu ý thường gặp và cách khắc phục lỗi
Hãy cùng đi vào chi tiết từng phần, kèm theo các ví dụ thực tế và hình ảnh minh họa (được mô tả chi tiết) để 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: Hàm Left Và Right Trong Excel: Tất Cả Những Gì Bạn Cần Biết Để Xử Lý Chuỗi Văn Bản Hiệu Quả
1. Hiểu Định Dạng Thời Gian Trong Excel
1.1. Cấu Trúc Nội Bộ Của Thời Gian
Excel lưu trữ ngày và giờ dưới dạng số thập phân:
- Ngày được tính từ ngày 0 (01/01/1900). Ví dụ: 1 = 01/01/1900, 2 = 02/01/1900, …
- Giờ được biểu diễn dưới dạng phần thập phân của một ngày. Vì một ngày có 24 giờ, nên:
- 0.5 = 12:00 PM (nửa ngày)
- 0.25 = 06:00 AM (1/4 ngày)
- 0.0416667 ≈ 1 giờ (1/24 ngày)
Do đó, 1 ngày = 1, 1 giờ = 1/24, 1 phút = 1/(2460), 1 giây = 1/(246060).
1.2. Định Dạng Hiển Thị
Mặc dù dữ liệu được lưu dưới dạng số, chúng ta có thể hiển thị chúng bằng các định dạng ngày/giờ:
| Định dạng | Mô tả | Ví dụ hiển thị |
|---|---|---|
dd/mm/yyyy | Ngày tháng năm | 21/06/2026 |
mm/dd/yyyy | Tháng ngày năm (US) | 06/21/2026 |
hh:mm:ss | Giờ phút giây (24h) | 14:35:20 |
hh:mm AM/PM | Giờ phút (12h) | 02:35 PM |
$-F800dddd, mmmm dd, yyyy | Ngày đầy đủ | Tuesday, June 21, 2026 |
Bạn có thể tùy chỉnh định dạng bằng cách Nhấn chuột phải → Format Cells → Number → Custom và nhập mã định dạng.
Có thể bạn quan tâm: Header Và Footer Trong Excel: Hướng Dẫn Toàn Diện Để Tối Ưu Hóa Báo Cáo Và Tài Liệu
2. Các Hàm Thời Gian Cơ Bản
2.1. Hàm NOW()
- Công dụng: Trả về ngày và giờ hiện tại của hệ thống.
- Cú pháp:
=NOW() - Lưu ý: Hàm này là volatile, nghĩa là nó tự động cập nhật mỗi khi bảng tính được tính lại (F9, mở file, thay đổi dữ liệu).
Ví dụ:
Nếu hiện tại là 21/06/2026 14:30, công thức =NOW() sẽ hiển thị 21/06/2026 14:30.
2.2. Hàm TODAY()
- Công dụng: Trả về ngày hiện tại (không có giờ).
- Cú pháp:
=TODAY() - Lưu ý: Cũng là hàm volatile, nhưng chỉ cập nhật ngày.
2.3. Hàm TIME(hour, minute, second)
- Công dụng: Tạo một giá trị thời gian từ các thành phần giờ, phút, giây.
- Cú pháp:
=TIME(h, m, s) - Ví dụ:
=TIME(9,30,0)→09:30:00.
2.4. Hàm DATE(year, month, day)
- Công dụng: Tạo một giá trị ngày.
- Cú pháp:
=DATE(y, m, d) - Ví dụ:
=DATE(2026,6,21)→21/06/2026.
2.5. Hàm TIMEVALUE(text) và DATEVALUE(text)
- Công dụng: Chuyển đổi chuỗi văn bản dạng ngày/giờ thành giá trị ngày/giờ thực.
- Cú pháp:
=TIMEVALUE("14:45"),=DATEVALUE("21/06/2026").
3. Tính Khoảng Thời Gian Giữa Hai Ngày/Giờ
3.1. Cách Đơn Giản: Trừ Trực Tiếp
Nếu ô A2 chứa ngày/giờ bắt đầu và ô B2 chứa ngày/giờ kết thúc, công thức:
=B2 - A2
Kết quả sẽ là một số thập phân đại diện cho số ngày (có phần thập phân là giờ/phút). Để hiển thị dưới dạng giờ:phút, thay đổi định dạng thành hh:mm:ss hoặc tùy chỉnh h:mm:ss (để hiển thị tổng giờ vượt quá 24).
3.2. Chuyển Đổi Sang Giờ, Phút, Giây
- Giờ:
=(B2-A2)24 - Phút:
=(B2-A2)2460 - Giây:
=(B2-A2)246060
Ví dụ:
A2 = 21/06/2026 08:00, B2 = 21/06/2026 17:30
– Tổng ngày = 0.3958333
– Tổng giờ = 9.5 (9 giờ 30 phút)
3.3. Hàm DATEDIF(start_date, end_date, unit)
- Công dụng: Tính khoảng cách giữa hai ngày theo các đơn vị như
"Y"(năm),"M"(tháng),"D"(ngày),"MD"(số ngày không tính tháng/năm),"YM"(số tháng không tính năm),"YD"(số ngày không tính năm). - Cú pháp:
=DATEDIF(A2, B2, "d")→ trả về số ngày.
Lưu ý:
DATEDIFkhông được liệt kê trong danh sách hàm chính thức, nhưng vẫn hoạt động trong các phiên bản Excel.
4. Tính Tổng Thời Gian Làm Việc và Giờ Overtime
4.1. Bảng Thời Gian Nhân Viên
| Nhân viên | Ngày | Giờ vào | Giờ ra |
|---|---|---|---|
| A | 20/06/2026 | 08:00 | 17:30 |
| A | 21/06/2026 | 08:15 | 18:00 |
| … | … | … | … |
Giả sử Giờ vào ở cột C, Giờ ra ở cột D, Ngày ở cột B.
Công thức tính giờ làm việc mỗi ngày (ô E2):
= (D2 - C2) 24
Định dạng E2 là General hoặc Number với 2 chữ số thập phân.
4.2. Tính Tổng Giờ Làm Việc
Sử dụng SUM:
=SUM(E2:E100)
Nếu muốn hiển thị dưới dạng hh:mm, dùng:
=TEXT(SUM(E2:E100)/24, "h:mm")
4.3. Tính Overtime (Giờ tăng ca)
Giả sử chuẩn làm việc là 8 giờ mỗi ngày.
Công thức overtime cho mỗi ngày (ô F2):
=MAX(0, (D2 - C2)24 - 8)
Sau đó tổng overtime:
=SUM(F2:F100)
Bạn có thể dùng hàm IF để đưa ra thông báo:
=IF((D2-C2)24>8, (D2-C2)24-8, 0)
5. Xử Lý Thời Gian Vượt Ngày (Over 24h) và Định Dạng Tùy Chỉnh
5.1. Vấn Đề 24h
Khi tổng thời gian vượt quá 24 giờ, định dạng chuẩn hh:mm:ss sẽ bắt đầu lại từ 00:00. Để hiển thị tổng giờ thực, sử dụng định dạng h:mm:ss (đặt dấu ngoặc vuông).
Có thể bạn quan tâm: Hàm If (nếu) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Ví dụ: Tổng 27 giờ 45 phút → hiển thị 27:45:00.
5.2. Định Dạng Tùy Chỉnh
Bạn có thể tạo các định dạng như:
- h “giờ” mm “phút” →
27 giờ 45 phút - h:mm →
27:45 - h”h” mm”m” →
27h 45m
Cách thực hiện: Format Cells → Custom → Nhập mã.
6. Các Hàm Nâng Cao
6.1. Hàm NETWORKDAYS(start_date, end_date, holidays)
- Công dụng: Tính số ngày làm việc (không tính cuối tuần) giữa hai ngày.
- Cú pháp:
=NETWORKDAYS(A2, B2, C2:C5)(trong đó C2:C5 là danh sách ngày nghỉ lễ).
6.2. Hàm WORKDAY(start_date, days, holidays)
- Công dụng: Trả về ngày làm việc cuối cùng sau một số ngày làm việc.
- Cú pháp:
=WORKDAY(A2, 10, $E$2:$E$10)→ ngày làm việc thứ 10 tính từ A2, bỏ qua ngày lễ trong E2:E10.
6.3. Hàm EDATE(start_date, months)
- Công dụng: Thêm hoặc trừ số tháng.
- Ví dụ:
=EDATE(TODAY(), -3)→ ngày hiện tại trừ 3 tháng.
6.4. Hàm TEXT(value, format_text)
- Công dụng: Chuyển đổi ngày/giờ thành chuỗi văn bản với định dạng tùy chỉnh.
- Ví dụ:
=TEXT(NOW(), "dd mmmm yyyy - hh:mm")→21 June 2026 - 14:30.
6.5. Hàm HOUR, MINUTE, SECOND
- Công dụng: Trích xuất thành phần giờ, phút, giây.
- Cú pháp:
=HOUR(A2),=MINUTE(A2),=SECOND(A2).
6.6. Hàm TIMEVALUE kết hợp INT và MOD
Để tách ngày và giờ:
- Ngày:
=INT(A2)→ trả về phần nguyên (ngày). - Giờ:
=MOD(A2,1)→ trả về phần thập phân (giờ).
7. Ứng Dụng Thực Tế
7.1. Tính Lương Dựa Trên Giờ Làm Việc
Giả sử lương giờ là 150,000 VND.
- Giờ làm việc (cột E) đã tính ở mục 4.
- Lương (cột G):
=E2150000.
Nếu có overtime trả 1.5 lần lương giờ:
= (E2 - F2)150000 + F21500001.5
Trong đó F2 là số giờ overtime.
7.2. Tính Tiến Độ Dự Án
| Nhiệm vụ | Ngày bắt đầu | Ngày kết thúc dự kiến | Ngày hoàn thành thực tế |
|---|---|---|---|
| A | 01/06/2026 | 10/06/2026 | 09/06/2026 |
| B | 05/06/2026 | 20/06/2026 | 22/06/2026 |
Số ngày dự kiến: =DATEDIF(B2, C2, "d")
Số ngày thực tế: =DATEDIF(B2, D2, "d")
Độ trễ %: = (Thực tế - Dự kiến) / Dự kiến
Bạn có thể dùng Conditional Formatting để tô màu đỏ nếu độ trễ > 10%.
7.3. Tính Tuổi
Nếu ngày sinh trong ô A2:
=DATEDIF(A2, TODAY(), "Y") & " tuổi"
Có thể bổ sung tháng và ngày:
=DATEDIF(A2, TODAY(), "Y") & " năm " & DATEDIF(A2, TODAY(), "YM") & " tháng " & DATEDIF(A2, TODAY(), "MD") & " ngày"
7.4. Tính Ngày Hết Hạn
Nếu ngày bắt đầu trong B2 và thời hạn 30 ngày:
=WORKDAY(B2, 30, $E$2:$E$10) // bỏ qua ngày lễ
Hoặc nếu không quan tâm ngày lễ:
=B2 + 30
8. Mẹo Và Thủ Thuật
8.1. Sử Dụng Bảng Phụ (Helper Table) Để Đánh Dấu Ngày Lễ
Tạo một sheet Holiday và liệt kê tất cả ngày lễ trong cột A. Khi dùng NETWORKDAYS hoặc WORKDAY, tham chiếu tới dải này để tự động bỏ qua.
8.2. Conditional Formatting Để Highlight Thời Gian Ngoài Giới Hạn
- Chọn cột giờ làm việc (E).
- Vào Home → Conditional Formatting → New Rule → Use a formula.
- Công thức:
=E2>8→ Đặt màu nền đỏ.
8.3. PivotTable Để Tổng Hợp Thời Gian
- Chọn dữ liệu (có cột Nhân viên, Ngày, Giờ làm việc).
- Insert → PivotTable.
- Kéo Nhân viên vào Rows, Ngày vào Columns, Giờ làm việc vào Values (đặt Summarize by → Sum).
- Định dạng giá trị bằng
NumberhoặcCustomh:mm.
8.4. Sử Dụng ARRAYFORMULA (Excel 365) Để Tính Toàn Bộ Cột
Nếu bạn dùng Excel 365, có thể tính tổng giờ làm việc cho một cột mà không cần copy công thức:
=SUM((D2:D100 - C2:C100) 24)
Nhấn Enter và Excel sẽ tự động mở rộng.
8.5. Đánh Dấu Thời Gian Quá Giờ Tự Động
=IF((D2-C2)24>8, "Overtime", "Normal")
Sau đó dùng Data Validation để tạo danh sách thả xuống cho cột trạng thái.
9. Lưu Ý Thường Gặp Và Cách Khắc Phục
| Vấn đề | Nguyên nhân | Giải pháp |
|---|---|---|
| Kết quả thời gian hiển thị là 0.00 | Ô được định dạng là General hoặc Number mà không có định dạng thời gian. | Định dạng lại ô thành Time hoặc Custom. |
| Khi trừ ngày, nhận được #VALUE! | Một trong hai ô không phải là ngày/giờ hợp lệ (có thể là chuỗi). | Sử dụng DATEVALUE hoặc TIMEVALUE để chuyển đổi. |
| Tổng giờ > 24h hiển thị 00:xx | Định dạng không có dấu ngoặc vuông. | Đặt định dạng h:mm:ss. |
NETWORKDAYS tính sai ngày cuối tuần | Thiết lập regional settings của Excel tính thứ Bảy là ngày làm việc. | Sử dụng NETWORKDAYS.INTL(start, end, "0000011") để chỉ định cuối tuần tùy chỉnh. |
| Công thức không tự động cập nhật khi nhập dữ liệu mới | Bảng tính không được đặt thành Table. | Chuyển dải dữ liệu thành Table (Ctrl+T) để công thức tự mở rộng. |
Lỗi #NUM! với WORKDAY | Số ngày âm quá lớn hoặc ngày bắt đầu là ngày lễ không hợp lệ. | Kiểm tra giá trị days và danh sách ngày lễ. |
10. Tổng Kết
Việc tính thời gian trong Excel không chỉ là một kỹ năng hữu ích mà còn là nền tảng để xây dựng các báo cáo, phân tích và quyết định kinh doanh chính xác. Bằng cách nắm vững:
- Cấu trúc nội bộ của ngày/giờ (số thập phân).
- Các hàm cơ bản như
NOW,TODAY,TIME,DATE. - Cách trừ và định dạng để tính khoảng thời gian.
- Hàm nâng cao như
NETWORKDAYS,WORKDAY,DATEDIF. - Kỹ thuật hiển thị tổng giờ vượt 24h bằng
h:mm:ss. - Ứng dụng thực tế trong tính lương, dự án, tuổi, ngày hết hạn.
Bạn sẽ có thể xử lý hầu hết các tình huống thời gian trong môi trường công việc. Hãy thực hành với các ví dụ trên, tùy chỉnh theo nhu cầu thực tế và dần dần xây dựng các bộ công cụ thời gian riêng cho mình.
Mẹo cuối cùng: Khi làm việc với thời gian, luôn luôn đặt định dạng hiển thị ngay sau khi nhập hoặc tính toán. Điều này giúp tránh những lỗi hiển thị không mong muốn và giữ cho dữ liệu luôn trong trạng thái “sạch sẽ, chuẩn xác”.
Chúc bạn thành công trong việc áp dụng các kỹ thuật tính thời gian trong 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ẻ.