Hướng Dẫn Tính Thời Gian Trong Excel: Cộng, Trừ Và Xử Lý Chênh Lệch Chi Tiết

Table of Contents

Thời gian đọc ước tính: 12 phút

Những điểm chính

Bản chất dữ liệu: Excel lưu trữ thời gian dưới dạng số thập phân, trong đó 1 đơn vị tương ứng với 24 giờ.
Tính chênh lệch: Sử dụng phép trừ đơn giản hoặc các hàm như TEXT, HOUR, MINUTE để tính khoảng cách giữa hai mốc thời gian.
Xử lý ca đêm: Áp dụng hàm IF hoặc phép toán số học để xử lý trường hợp giờ kết thúc nhỏ hơn giờ bắt đầu (qua ngày mới).
Định dạng hiển thị: Sử dụng ngoặc vuông [h] trong Custom Format để hiển thị tổng thời gian vượt quá 24 giờ.
Cộng trừ thời gian: Có thể cộng trực tiếp giá trị số (ví dụ: +1/24 cho 1 giờ) hoặc dùng hàm TIME.

Excel là một công cụ phân tích dữ liệu mạnh mẽ, và việc xử lý dữ liệu thời gian là một kỹ năng thiết yếu cho bất kỳ ai làm việc trong lĩnh vực tài chính, kế toán hoặc quản trị nhân sự. Vì Excel lưu trữ ngày và giờ dưới dạng các con số ở hệ thống back-end, bạn có thể thực hiện các phép tính số học đơn giản và sử dụng công thức một cách linh hoạt.

Trong bài viết này, chúng ta sẽ đi sâu vào các phương pháp tính toán thời gian trong Excel, bao gồm tính chênh lệch thời gian, cộng/trừ giờ phút, hiển thị định dạng tùy chỉnh và cách tính tổng thời gian chính xác.

Cơ Chế Lưu Trữ Ngày Và Giờ Của Excel

Trước khi thực hiện bất kỳ phép tính nào, bạn cần hiểu cách Excel “nhìn” dữ liệu thời gian. Về cơ bản, Excel lưu trữ ngày và giờ dưới dạng một chuỗi số liên tục. Một số nguyên đại diện cho một ngày trọn vẹn, và phần thập phân của số đó đại diện cho một phần của ngày (có thể quy đổi thành giờ, phút và giây).

Ví dụ cụ thể:

Giá trị 1 đại diện cho ngày 01/01/1900 (điểm khởi đầu mặc định của Excel trên Windows).
Giá trị 2 là ngày 02/01/1900, và cứ thế tiếp tục.
Giá trị 44197 sẽ tương ứng với ngày 01/01/2021.

Nếu con số có phần thập phân, Excel sẽ coi đó là dữ liệu thời gian:

0.5 đại diện cho nửa ngày, tức là 12:00:00 PM.
44197.5 sẽ là 12:00:00 PM ngày 01/01/2021.

Giá trị ngày giờ và giá trị số tương đương trong Excel

Khi bạn làm việc với thời gian, thực chất bạn đang thao tác với phần thập phân của các con số này. Excel cho phép bạn định dạng phần thập phân này để hiển thị dưới dạng giờ, phút, giây hoặc kết hợp tùy ý.

Hiểu được nguyên lý cốt lõi này sẽ giúp bạn kiểm soát hoàn toàn các công thức tính toán phía sau.

Công Thức Tính Khoảng Cách Giữa Hai Mốc Thời Gian

Nhu cầu phổ biến nhất khi làm việc với thời gian là tính toán khoảng thời gian đã trôi qua giữa giờ bắt đầu và giờ kết thúc (ví dụ: tính giờ làm việc trên bảng chấm công). Phương pháp bạn chọn sẽ phụ thuộc vào định dạng dữ liệu đầu vào và kết quả bạn mong muốn hiển thị.

Phép Trừ Đơn Giản Để Tính Chênh Lệch Thời Gian

Vì thời gian là các con số, cách nhanh nhất để tìm sự khác biệt là lấy thời gian kết thúc trừ đi thời gian bắt đầu.

Công thức tổng quát:
= Thời gian kết thúc – Thời gian bắt đầu

Kết quả của phép trừ này là một số thập phân đại diện cho khoảng thời gian đã trôi qua.

Tính chênh lệch thời gian bằng phép trừ đơn giản

Trong ví dụ trên, ô kết quả C2 hiển thị “09:30 AM” thay vì số thập phân 0.3958. Điều này xảy ra do Excel tự động sao chép định dạng từ các ô liền kề. Để xem giá trị thực (số thập phân), bạn có thể chuyển định dạng ô về General (trong thẻ Home, nhóm Number).

Tuy nhiên, thông thường chúng ta muốn hiển thị kết quả dưới dạng số giờ hoặc phút cụ thể. Bạn có thể sử dụng Custom Number Formatting để tùy chỉnh cách hiển thị:

Định dạng	Ý nghĩa hiển thị
h	Chỉ hiển thị số giờ đã trôi qua (ví dụ: 9)
hh	Hiển thị giờ với hai chữ số (ví dụ: 09)
hh:mm	Hiển thị giờ và phút (ví dụ: 09:30)
hh:mm:ss	Hiển thị giờ, phút và giây (ví dụ: 09:30:00)

Để áp dụng các định dạng này, hãy thực hiện theo các bước sau:

Chọn các ô chứa kết quả bạn muốn định dạng.
Nhấn tổ hợp phím Ctrl + 1 (hoặc Command + 1 trên Mac) để mở hộp thoại Format Cells.
Chọn tab Number (nếu chưa được chọn).

Chọn nhóm Number trong hộp thoại Format Cells

Trong danh sách bên trái, chọn Custom.

Chọn tùy chọn Custom trong hộp thoại Format Cells

Nhập mã định dạng mong muốn vào ô Type (ví dụ: hh:mm:ss).

Nhập mã định dạng để hiển thị giờ phút giây

Nhấn OK.

Lúc này, dữ liệu của bạn sẽ hiển thị chính xác theo định dạng bạn đã quy định.

Kết quả tính toán chênh lệch thời gian sau khi định dạng

Lưu ý quan trọng: Định dạng số tùy chỉnh (Custom Formatting) chỉ thay đổi cách hiển thị, không thay đổi giá trị thực của ô. Nếu tổng số giờ vượt quá 24, bạn nên sử dụng định dạng [h]:mm:ss để Excel không tự động quy đổi sang ngày và reset lại giờ về 0.

Chuyển Đổi Kết Quả Sang Giờ, Phút Hoặc Giây Cụ Thể

Đôi khi, bạn cần kết quả là một con số cụ thể (ví dụ: “8.5 giờ” để nhân với lương theo giờ) thay vì định dạng thời gian “8:30”. Vì mỗi số nguyên là 1 ngày (24 giờ), chúng ta có thể sử dụng phép nhân để quy đổi.

Tính Số Giờ Chênh Lệch

Để chuyển đổi giá trị thời gian sang tổng số giờ (dạng số thập phân), bạn nhân kết quả phép trừ với 24.

Công thức:
=(B2-A2)*24

Dữ liệu mẫu tính chênh lệch giờ làm việcKết quả chênh lệch thời gian tính bằng giờ

Nếu Excel tự động định dạng kết quả thành giờ (ví dụ: 12:00 AM), hãy chuyển định dạng ô sang Number hoặc General để thấy con số thực (ví dụ: 9.5).

Kết quả hiển thị sai định dạng thời gianChuyển đổi định dạng sang Number trong tab Home

Nếu bạn chỉ muốn lấy phần nguyên của số giờ (bỏ qua phút), hãy dùng hàm INT:
=INT((B2-A2)*24)

Tính số giờ nguyên đã trôi qua giữa hai mốc thời gian

Tính Số Phút Chênh Lệch

Để tính tổng số phút, bạn nhân giá trị chênh lệch với tổng số phút trong một ngày (24 giờ * 60 phút = 1440).

Công thức:
=(B2-A2)*1440 hoặc =(B2-A2)*24*60

Tính chênh lệch thời gian theo đơn vị phút

Tính Số Giây Chênh Lệch

Tương tự, để tính tổng số giây, bạn nhân với tổng số giây trong ngày (86400).

Công thức:
=(B2-A2)*86400 hoặc =(B2-A2)*24*60*60

Tính chênh lệch thời gian theo đơn vị giây

Sử Dụng Hàm TEXT Để Tính Toán Và Định Dạng

Hàm TEXT là một công cụ linh hoạt cho phép bạn tính toán và định dạng kết quả ngay trong một công thức duy nhất, giúp tiết kiệm thời gian thao tác định dạng thủ công.

Cấu trúc:
=TEXT(Giá trị, "Định dạng")

Dưới đây là các ví dụ áp dụng cho dữ liệu thời gian:

Chỉ hiển thị số giờ:
=TEXT(B2-A2,"hh")
Lưu ý: Công thức này trả về kết quả dạng văn bản (text).

Tính giờ bằng hàm TEXT

Hiển thị tổng số phút:
=TEXT(B2-A2,"[mm]")
Dấu ngoặc vuông [] báo cho Excel biết cần tính tổng tích lũy phút, không reset về 0 sau mỗi giờ.

Tính phút bằng hàm TEXT

Hiển thị tổng số giây:
=TEXT(B2-A2,"[ss]")

Tính giây bằng hàm TEXT

Hiển thị Giờ và Phút:
=TEXT(B2-A2,"[hh]:mm")

Hiển thị chênh lệch giờ và phút

Hiển thị đầy đủ Giờ, Phút, Giây:
=TEXT(B2-A2,"hh:mm:ss")

Hiển thị đầy đủ giờ phút giây

Tách Riêng Giờ, Phút, Giây Bằng Hàm Chuyên Biệt

Nếu bạn muốn trích xuất riêng lẻ từng thành phần thời gian từ một khoảng thời gian chênh lệch, bạn có thể dùng các hàm HOUR, MINUTE, và SECOND.

Dữ liệu giờ vào và giờ ra

Lấy số giờ (đã hoàn thành):
=HOUR(B2-A2)

Sử dụng hàm HOUR

Lấy số phút (phần dư sau khi trừ giờ):
=MINUTE(B2-A2)

Sử dụng hàm MINUTE

Lấy số giây (phần dư sau khi trừ giờ và phút):
=SECOND(B2-A2)

Sử dụng hàm SECOND

Lưu ý: Các hàm này sẽ trả về lỗi #NUM! nếu kết quả phép trừ là số âm. Ngoài ra, chúng chỉ lấy phần thời gian trong một ngày (ví dụ: nếu chênh lệch là 25 giờ, hàm HOUR sẽ trả về 1, vì nó đã bỏ qua phần 24 giờ của ngày).

Tính Thời Gian Trôi Qua Tính Đến Hiện Tại

Để tính khoảng thời gian từ một mốc trong quá khứ đến thời điểm hiện tại, bạn sử dụng hàm NOW() thay cho giờ kết thúc. Hàm này tự động cập nhật ngày giờ hiện tại của hệ thống.

Công thức:
=NOW() - Thời gian bắt đầu

Tính thời gian trôi qua đến hiện tại

Nếu dữ liệu “Thời gian bắt đầu” của bạn chỉ chứa giờ (không có ngày tháng), Excel sẽ mặc định hiểu đó là ngày 01/01/1900. Khi trừ với NOW() (chứa ngày tháng năm hiện tại), kết quả sẽ rất lớn và sai lệch. Trong trường hợp này, bạn cần loại bỏ phần ngày khỏi hàm NOW():

=NOW() - INT(NOW()) - A2

Công thức này lấy thời gian hiện tại trừ đi phần nguyên (ngày) để chỉ giữ lại phần giờ phút giây, sau đó mới trừ đi thời gian bắt đầu.

Xử Lý Trường Hợp Qua Đêm (Giờ Kết Thúc Nhỏ Hơn Giờ Bắt Đầu)

Một thách thức thường gặp khi làm bảng chấm công cho ca đêm là giờ kết thúc (ví dụ: 02:00 AM) nhỏ hơn giờ bắt đầu (ví dụ: 08:00 PM). Nếu thực hiện phép trừ thông thường, bạn sẽ nhận được kết quả âm, và Excel sẽ hiển thị một chuỗi dấu thăng #####.

Dữ liệu giờ vào ra khác ngàyLỗi hiển thị dấu thăng khi kết quả thời gian âm

Để khắc phục điều này, chúng ta cần “nói” cho Excel biết rằng nếu giờ kết thúc nhỏ hơn, nghĩa là đã sang ngày hôm sau (cộng thêm 1 ngày hay 24 giờ).

Công thức sử dụng hàm IF:
=IF((B2-A2)<0, 1+(B2-A2), (B2-A2))

Công thức này kiểm tra: Nếu hiệu số âm, hãy cộng thêm 1 (tương đương 24 giờ) vào hiệu số đó. Nếu dương, giữ nguyên phép trừ.

Công thức xử lý thời gian khi đổi ngày

Giải pháp này hoạt động tốt cho các ca làm việc qua đêm thông thường (dưới 24 giờ).

Cộng Và Trừ Thời Gian Trong Excel

Ngoài việc tính chênh lệch, bạn thường xuyên cần cộng thêm một lượng thời gian nhất định vào một mốc cụ thể (ví dụ: Dự kiến hoàn thành sau 4 tiếng).

Sử Dụng Hàm TIME

Hàm TIME(hour, minute, second) chuyển đổi các con số riêng lẻ thành giá trị thời gian thập phân chuẩn của Excel.

Ví dụ: Cộng thêm 4 giờ vào thời gian bắt đầu.
=Thời gian bắt đầu + TIME(4, 0, 0)

Cộng thời gian sử dụng hàm TIME

Lưu ý: Hàm TIME sẽ tự động “xoay vòng” nếu giá trị giờ nhập vào lớn hơn 23. Ví dụ TIME(25,0,0) sẽ được hiểu là 1 giờ sáng (1:00 AM) chứ không phải 25 giờ.

Sử Dụng Phép Toán Số Học (Phương Pháp Chuyên Gia)

Để linh hoạt hơn và tránh giới hạn của hàm TIME (đặc biệt khi cộng số giờ lớn hơn 24), bạn nên sử dụng phép chia trực tiếp dựa trên nguyên lý 1 ngày = 24 giờ.

Cộng thêm Giờ: + Số_Giờ / 24
Ví dụ cộng 30 tiếng: =A2 + 30/24
Cộng thêm Phút: + Số_Phút / 1440 (hoặc 24*60)
Ví dụ cộng 45 phút: =A2 + 45/1440
Cộng thêm Giây: + Số_Giây / 86400 (hoặc 24*60*60)

Phương pháp này đảm bảo tính chính xác tuyệt đối ngay cả khi bạn cộng hàng trăm giờ vào một mốc thời gian. Để trừ thời gian, bạn chỉ cần thay dấu cộng (+) thành dấu trừ (-).

Cách Tính Tổng (SUM) Thời Gian

Việc tính tổng cột thời gian tưởng chừng đơn giản bằng hàm SUM, nhưng thường gặp lỗi hiển thị nếu tổng thời gian vượt quá 24 giờ.

Ví dụ: Bạn có danh sách thời gian thực hiện các tác vụ.

Dữ liệu thời gian thực hiện các tác vụ

Nếu tổng nhỏ hơn 24 giờ, hàm SUM hiển thị đúng (ví dụ: 18:30).

Tổng thời gian nhỏ hơn 24 giờ

Tuy nhiên, nếu bạn thay đổi một tác vụ thành 10 giờ, tổng thực tế là 27 giờ 20 phút. Nhưng Excel mặc định sẽ chỉ hiển thị phần giờ lẻ: 03:20 (27 trừ đi 24).

Hàm SUM hiển thị sai khi tổng quá 24 giờ

Cách Hiển Thị Tổng Lớn Hơn 24 Giờ

Vấn đề nằm ở định dạng hiển thị, không phải do Excel tính sai. Để sửa, bạn cần dùng Custom Format với dấu ngoặc vuông bao quanh chữ h: [h]:mm.

Các định dạng hữu ích cho tổng thời gian:

Định dạng	Kết quả hiển thị
`[h]:mm`	28:30 (Hiển thị tổng giờ tích lũy)
`[m]:ss`	1710:00 (Hiển thị tổng phút tích lũy)
`d "ngày" hh:mm`	1 ngày 04:30

Bạn có thể áp dụng định dạng này qua hộp thoại Format Cells hoặc dùng trực tiếp trong hàm TEXT:

=TEXT(SUM(B2:B7),"[h]:mm:ss")

Hàm TEXT hiển thị giờ lớn hơn 24

Hoặc chuyển đổi sang ngày và giờ:
=TEXT(SUM(B2:B7),"dd hh:mm:ss")

Hàm TEXT hiển thị ngày và giờ trong kết quả

Nguyên Nhân Và Cách Khắc Phục Lỗi Dấu Thăng (###)

Khi làm việc với ngày giờ, bạn sẽ thường xuyên gặp lỗi hiển thị #####. Dưới đây là hai nguyên nhân chính và cách xử lý:

Cột quá hẹp: Dữ liệu ngày giờ dài hơn độ rộng của cột.
- Khắc phục: Kéo rộng cột hoặc nhấp đúp vào biên phải của tiêu đề cột để tự động điều chỉnh độ rộng (AutoFit).
Giá trị thời gian âm: Như đã đề cập ở phần ca đêm, Excel không hỗ trợ hiển thị thời gian âm.
- Khắc phục: Kiểm tra lại công thức trừ (có bị ngược không?) hoặc sử dụng hàm IF để xử lý trường hợp qua đêm. Bạn cũng có thể dùng hàm ABS để lấy giá trị tuyệt đối nếu chỉ quan tâm đến độ lớn của khoảng thời gian mà không quan tâm chiều hướng.

FAQ – Câu Hỏi Thường Gặp

1. Làm thế nào để nhập nhanh ngày và giờ hiện tại trong Excel?
Bạn có thể sử dụng phím tắt: Ctrl + ; để nhập ngày hiện tại và Ctrl + Shift + ; để nhập giờ hiện tại vào ô. Các giá trị này là tĩnh và sẽ không thay đổi khi bạn mở lại file.

2. Tại sao tôi cộng 1 vào ô chứa giờ thì nó lại nhảy sang ngày hôm sau?
Trong Excel, số 1 đại diện cho 24 giờ (1 ngày). Để cộng 1 giờ, bạn phải cộng 1/24. Để cộng 1 phút, bạn phải cộng 1/1440.

3. Hàm DATEDIF có dùng được cho giờ phút không?
Hàm DATEDIF chủ yếu được thiết kế để tính chênh lệch ngày, tháng, năm. Đối với giờ phút giây, phép trừ trực tiếp (End – Start) hoặc các hàm như HOUR/MINUTE thường hiệu quả và chính xác hơn.

Nguồn Tham Khảo: