Nội Dung Bài Viết

Excel là công cụ mạnh mẽ cho việc xử lý dữ liệu, bao gồm cả tính toán thời gian. Bài viết này sẽ hướng dẫn bạn các công thức hữu ích để cộng, trừ, tính chênh lệch và đếm giờ trong Excel.

Mở Đầu

Tính toán thời gian trong Excel đôi khi gây khó khăn cho người dùng. Thực tế, Excel lưu trữ thời gian dưới dạng số thập phân, cho phép thực hiện các phép tính toán như cộng, trừ. Bài viết này sẽ giúp bạn làm chủ các công thức và kỹ thuật cần thiết để tính toán thời gian hiệu quả trong Excel.

Các Công Thức Tính Chênh Lệch Thời Gian

Công Thức 1: Trừ Thời Gian Trực Tiếp

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

=Thời_gian_kết_thúc - Thời_gian_bắt_đầu

Ví dụ:

=A2-B2: Tính chênh lệch giữa thời gian trong ô A2 và B2.
=TIMEVALUE("8:30 PM") - TIMEVALUE("6:40 AM"): Tính chênh lệch giữa hai thời điểm cụ thể.
=TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) - TIME(HOUR(B2), MINUTE(B2), SECOND(B2)): Tính chênh lệch thời gian, bỏ qua ngày tháng.

Xem thêm: Nâng Cao Hiệu Suất Lọc Dữ Liệu trong Excel với Advanced Filter

1 – cách tính thời gian trong Excel

Kết quả trả về là số thập phân. Để hiển thị đúng định dạng thời gian, bạn có thể sử dụng các mã định dạng như: h (giờ), h:mm (giờ:phút), h:mm:ss (giờ:phút:giây) trong hộp thoại Format Cells (Ctrl + 1 > Custom).

2 – cách tính thời gian trong Excel

Nếu kết quả hiển thị #####, hãy mở rộng độ rộng cột.

Công Thức 2: Sử Dụng Hàm TEXT

Hàm TEXT giúp chuyển đổi giá trị số thành văn bản theo định dạng mong muốn:

=TEXT(B2-A2, "h"): Tính chênh lệch giờ.
=TEXT(B2-A2, "h:mm"): Tính chênh lệch giờ và phút.
=TEXT(B2-A2, "h:mm:ss"): Tính chênh lệch giờ, phút và giây.

3 – cách tính thời gian trong Excel

Lưu ý: Kết quả là văn bản, không dùng cho tính toán tiếp tục. Lỗi #VALUE! xuất hiện nếu chênh lệch âm.

Công Thức 3: Tính Giờ, Phút, Giây Riêng Biệt

Giờ: =(Thời_gian_kết_thúc - Thời_gian_bắt_đầu) * 24
Phút: =(Thời_gian_kết_thúc - Thời_gian_bắt_đầu) * 1440
Giây: =(Thời_gian_kết_thúc - Thời_gian_bắt_đầu) * 86400

4 – cách tính thời gian trong Excel

5 – cách tính thời gian trong Excel

6 – cách tính thời gian trong Excel

Sử dụng định dạng General cho ô kết quả.

Công Thức 4: Tính Chênh Lệch trong Cùng Đơn Vị

Giờ: =HOUR(Thời_gian_kết_thúc - Thời_gian_bắt_đầu)
Phút: =MINUTE(Thời_gian_kết_thúc - Thời_gian_bắt_đầu)
Giây: =SECOND(Thời_gian_kết_thúc - Thời_gian_bắt_đầu)

7 – cách tính thời gian trong Excel

Kết quả không vượt quá 24 giờ, 60 phút, hoặc 60 giây. Lỗi #NUM! xuất hiện nếu chênh lệch âm.

Công Thức 5: Tính Thời Gian Trôi Qua từ Thời Điểm Bắt Đầu

Dưới 24 giờ: =NOW() - Thời_gian_bắt_đầu

8 – cách tính thời gian trong Excel

Trên 24 giờ: Sử dụng định dạng d 'days' h:mm:ss.

9 – cách tính thời gian trong Excel

Chỉ có giờ và phút: =TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW())) - Thời_gian_bắt_đầu

10- cách tính thời gian trong Excel

Kết quả chỉ cập nhật khi mở lại hoặc tính toán lại bảng tính (Shift + F9 hoặc F9).

Xem thêm: Toán tử So sánh trong Excel: Hướng dẫn Chi Tiết Cho Người Dùng Việt Nam

Công Thức 6: Hiển Thị Chênh Lệch dưới dạng “XX ngày, XX giờ, XX phút và XX giây”

Sử dụng kết hợp các hàm INT, HOUR, MINUTE, SECOND và ký tự &:

=INT(B2-A2) & " ngày, " & HOUR(B2-A2) & " giờ, " & MINUTE(B2-A2) & " phút và " & SECOND(B2-A2) & " giây"

A user-friendly Excel time difference formula

Để ẩn giá trị 0, sử dụng hàm IF:

=IF(INT(B2-A2)>0, INT(B2-A2) & " ngày, ","") & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & " giờ, ","") & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & " phút và ","") & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & " giây","")

Excel time difference formula that ignores zero values

Hoặc sử dụng định dạng tùy chỉnh: d "ngày," h "giờ," m "phút và" s "giây".

11 – cách tính thời gian trong Excel

Xử Lý Thời Gian Âm

Phương pháp 1: Thay Đổi Hệ Thống Ngày Tháng

Chuyển sang hệ thống 1904 date system (File > Options > Advanced > When calculating this workbook > Use 1904 date system).

12 – cách tính thời gian trong Excel

In the 1904 date system, negative times are displayed like negative numbers

Phương pháp 2: Sử Dụng Công Thức

=IF(A2-B2>0, A2-B2, "-" & TEXT(ABS(A2-B2),"h:mm"))

hoặc

=IF(A2-B2>0, A2-B2, TEXT(ABS(A2-B2),"-h:mm"))

A formula to calculate negative times in Excel

Cộng/Trừ Thời Gian

Cộng/Trừ Giờ

Dưới 24 giờ: =Thời_gian_bắt_đầu + TIME(Số_giờ, 0, 0)

TIME function to add under 24 hours

Bất kỳ số giờ: =Thời_gian_bắt_đầu + (Số_giờ/24)

Universal formula to add hours to a given time in Excel

Để trừ, thay + bằng -.

Cộng/Trừ Phút

Dưới 60 phút: =Thời_gian_bắt_đầu + TIME(0, Số_phút, 0)
Bất kỳ số phút: =Thời_gian_bắt_đầu + (Số_phút/1440)

Để trừ, thay + bằng -.

Cộng/Trừ Giây

Dưới 60 giây: =Thời_gian_bắt_đầu + TIME(0, 0, Số_giây)
Bất kỳ số giây: =Thời_gian_bắt_đầu + (Số_giây/86400)

Để trừ, thay + bằng -.

Cộng Thời Gian bằng Hàm SUM

Sử dụng hàm SUM và định dạng tùy chỉnh:

A SUM formula to add up times in Excel

Phút và giây: [m]:ss
Giây: [ss]

Apply the custom time format to display total time as minutes and seconds, or seconds only.

Cộng Thời Gian trên 24 Giờ

Sử dụng hàm SUM và định dạng tùy chỉnh như [h]:mm:ss, d h:mm:ss, v.v.

Adding up more than 24 hours in Excel

Hiển thị Khoảng Thời Gian Lớn hơn 24 Giờ, 60 Phút, 60 Giây

Sử dụng định dạng tùy chỉnh với mã đơn vị thời gian trong ngoặc vuông: [h], [m], [s].

Cộng/Trừ Thời Gian Lớn hơn 24 Giờ, 60 Phút, 60 Giây

Cộng giờ: Thời_gian_bắt_đầu + (Số_giờ/24)
Cộng phút: Thời_gian_bắt_đầu + (Số_phút/1440)
Cộng giây: Thời_gian_bắt_đầu + (Số_giây/86400)

Để trừ, thay + bằng -.

Kết Luận

Bài viết đã cung cấp cho bạn những kiến thức và công thức cần thiết để tính toán thời gian trong Excel. Hy vọng những thủ thuật này sẽ giúp công việc của bạn trở nên hiệu quả hơn. Hãy khám phá thêm các bài viết khác trên “Thủ Thuật” để nâng cao kỹ năng Excel của bạn.

Đánh Giá Bài Viết