Hướng dẫn cách cộng trừ thời gian trong Excel trong từng trường hợp

Cách cộng trừ thời gian trong Excel là một dạng khó cần được tìm hiểu và thao tác một cách chuẩn nhất. Dưới đây chúng tôi sẽ hướng dẫn bạn cách làm để đạt kết quả đúng nhất.

Cách cộng trừ thời gian trong Excel

Công thức 1. Trừ thời gian

Để trông giống thời gian, thời gian trong Excel thường được định dạng số thập phân. Bạn có thể cộng trừ thời gian như các giá trị số khác vì tất cả đều ở dạng số.

Công thức đơn giản nhất để tính chênh lệch thời gian là:

=End time – Start time

Chênh lệch thời gian thực tế có thể có nhiều dạng khác nhau tùy vào cấu trúc dữ liệu ví dụ:

• =A2-B2: Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2
• =TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”): Tính toán chênh lệch thời gian giữa hai thời gian cụ thể;
• =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)): Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian.

Trong hệ thống Excel, thời gian biểu thị ở phần thập phân, bạn sẽ có kết quả như sau:

Xem ngay: cách chỉnh khoảng cách trong Word để biết cách làm

Trong cột D, phần thập phân đúng nhưng không có ý nghĩa. Bạn có thể áp dụng định dạng thời gian với một trong các mã sau đây để thông tin được trở nên chính xác nhất:

• h: Số giờ trôi qua, ví dụ 4;
• h:mm: Số giờ và số phút trôi qua, ví dụ 4:10;
• h:mm:ss: Số giờ, số phút và số giây trôi qua, ví dụ 4:10:20.

Nhấn Ctrl + 1 để mở hộp thoại Format Cells, chọn Custom từ Category list và gõ mã thời gian vào hộp Type để áp dụng định dạng thời gian.

Bạn có thể nhìn vào bảng tính thực sự để xem được công thức tính chênh lệch thời gian và mã thời gian hoạt động như thế nào. Bạn có thể sao chép công thức vào cột C và E khi trong cột  A chứa Start time và trong cột B chứa End time.

=$B2-$A2

Tùy vào định dạng thời gian trong mỗi cột, thời gian chênh lệch được hiển thị khác nhau.

Chú ý. Ô chứa công thức không đủ rộng để hiển thị hết thời gian hoặc kết quả phép tính là một giá trị âm nếu thời gian chênh lệch được hiển thị thành #####.

Công thức 2. Tính chênh lệch thời gian bằng hàm TEXT

Để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT. Đây là kỹ thuật được đánh giá rất hay.

=TEXT(B2-A2, “h”) là cách tính toán chênh lệch giờ giữa hai thời gian.

=TEXT(B2-A2, “h:mm”) là cách tính toán chênh lệch giờ và phút giữa hai thời gian.

=TEXT(B2-A2, “h:mm:ss”) là cách tính toán chênh lệch giờ, phút và giây giữa hai thời gian.

Chú ý.

Văn bản là kết quả ta nhận được khi sử dụng hàm TEXT. Trong các cột C, D, E sẽ là giá trị text được căn lề. Bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán nếu điều này bị hạn chế trong vài trường hợp đặc biệt.

Công thức TEXT sẽ hiển thị lỗi #VALUE! Nếu kết quả là một số âm.

Công thức 3. Tính giờ, phút, giây giữa hai thời gian

Bạn cần thực hiện các phép tính sau để hiển thị chênh lệch giữa hai thời gian bằng một đơn vị (giờ, phút, giây).

Phép tính giờ giữa hai thời gian

=(End time – Start time) * 24

Giả sử trong ô A2 thể hiện thời gian bắt đầu, trong ô B2 thể hiện thời gian kết thúc. Phép tính đơn giản là B2-A2 để tính toán chênh lệch thời gian, sau đó nhân với 24 là phép tính đơn giản bạn có thể sử dụng.

=(B2-A2) * 24

Sử dụng hàm INT để làm tròn đến số nguyên hần nhất để có số Complete hours:

=INT((B2-A2) * 24)

Tổng số phút giữa hai thời gian:

Để tính số phút giữa hai thời gian, nhân chênh lệch thời gian với 1440

=(End time – Start time) * 1440

Kết quả có thể ra âm hoặc dương khi sử dụng công thức này. Kết quả âm xảy ra khi thời gian kết thúc lớn hơn thời gian bắt đầu như hàng 5:

Click ngay: cách chèn ảnh chìm trong Excel để biết cách làm

=(B2-A2)*1440

Tổng số giây giữa hai thời gian:

Thực hiện nhân chênh lệch thời gian với 86400 để tính số giây giữa hai thời gian.

=(End time – Start time) * 86400

Chú ý. Để kết quả hiển thị đúng, định dạng General nên được thiết lập cho ô tính chứa công thức tính chênh lệch thời gian.

Công thức 4. Tính chênh lệch thời gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị khác

Để tính chênh lệch giữa hai thời gian trong cùng một đơn vị, bỏ qua các đơn vị thời gian khác, các hàm sau bạn có thể áp dụng:

Chênh lệch giây, bỏ qua giờ và phút:

=SECOND(B2-A2)

Chênh lệch giờ, bỏ qua phút và giây:

=HOUR(B2-A2)

Chênh lệch phút, bỏ qua giờ và giây:

=MINUTE(B2-A2)

Bạn cần nhớ rằng kết qua cho ra không được vượt quá 24 giờ, 60 phút hoặc 60 giây khi sử dụng hàm HOUR, MINUTE, SECOND.

Chú ý: Kết quả là số âm, công thức sẽ hiển thị lỗi #NUM! nếu thời gian kết thúc lớn hơn thời gian bắt đầu,

Công thức 5. Tính thời gian trôi qua kể từ thời gian bắt đầu đến bây giờ

Bạn có thể sử dụng hàm Now và Time để tính thời gian đã trôi qua. Bạn chỉ cần sử dụng hàm NOW để lấy kết quả ngày tháng và thời gian hiện tại, sau đó trừ đi ngày và thời gian bắt đầu nếu trong trường hợp khoảng thời gian trôi qua dưới 24 giờ.

Giả sử ô A2 chứa thời điểm bắt đầu, công thức =NOW()-A2 cho kết quả sau với cột B được định dạng thời gian phù hợp, trong ví dụ này nó có dạng h:mm.

Trong trường hợp khoảng thời gian trôi qua vượt quá 24 giờ, sử dụng một trong các định dạng thời gian sau.

Để tính toán khoảng thời gian trôi đi một cách chính xác nếu thời gian bắt đầu chỉ có giờ phút mà không có ngày, bạn cần sử dụng hàm Time. Ví dụ, công thức sau trả về khoảng thời gian từ ô A2 đến giờ.

=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) – A2

Chú ý. Khoảng thời gian trôi qua sẽ không được cập nhật theo thời gian thực tế, nó chỉ cập nhật khi bảng tính được mở lại hoặc tính toán lại. Để cập nhật công thức, để tính lại trang tính nhấn Shift + F9  hoặc để tính lại toàn bộ bảng tính nhấn F9.

Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”

Để lấy kết quả thời gian tương tứng và hàm INT để tính chênh lệch ngày, bạn sử dụng các hàm HOUR, MINUTE và SECOND. Sau đó, bạn lồng ghép các hàm vào cùng một công thức cùng các chuỗi text như sau :

=INT(B2-A2) & ” days, ” & HOUR(B2-A2) & ” hours, ” & MINUTE(B2-A2) & ” minutes and ” & SECOND(B2-A2) & ” seconds”

Để ẩn giá trị 0, lồng bốn hàm IF vào đó:

=IF(INT(B2-A2)>0, INT(B2-A2) & ” days, “,””) & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & ” hours, “,””) & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & ” minutes and “,””) & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & ” seconds”,””)

Thêm vào đó, bạn có thể tính toán chênh lệch thời gian bằng cách lấy thời gian bắt đầu trừ thời gian kết thúc (ví dụ = B2-A2) rồi thiết lập định dạng sau cho ô tính:

d “days,” h “hours,” m “minutes and” s “seconds”

Trên đây là cách cộng trừ thời gian trong Excel trong từng trường. Chúc các bạn thành công!