Cách tính chênh lệch thời gian trong Excel (Phần 1)

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Cách tính chênh lệch thời gian trong Excel (Phần 1)

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Trong bài viết này, chúng tôi sẽ hướng dẫn những công thức hữu ích để cộng/trừ thời gian, chênh lệch thời gian, đếm giờ…thời gian trong Excel.

1. Trừ thời gian trong Excel

Như bạn đã biết, thời gian trong Excel thường được định dạng số thập phân để trông giống thời gian. Vì chúng là số, nên bạn có thể cộng trừ thời gian như các giá trị số khác.

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

=End time – Start time

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

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:

Phần thập phân trong cột D đúng nhưng không có ý nghĩa. Để giúp chúng cung cấp thông tin có ích hơn, bạn có thể áp dụng định dạng thời gian với một trong các mã sau đây:

Để áp dụng định dạng thời gian, 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.

Bây giờ, để xem 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 trong bảng tính thực sự. Với Start time trong cột A và End time trong cột B, bạn có thể sao chép công thức vào cột C và E:

=$B2-$A2

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

Chú ýNếu thời gian chênh lệch được hiển thị thành #####, nghĩa là ô 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.

2. Tính chênh lệch thời gian trong Excel bằng hàm text

Một kỹ thuật khác để tính chênh lệch thời gian trong Excel là sử dụng hàm TEXT.

  • Tính toán chênh lệch giờ giữa hai thời gian: =TEXT(B2-A2, “h”)
  • Tính toán chênh lệch giờ và phút giữa hai thời gian: =TEXT(B2-A2, “h:mm”)
  • Tính toán chênh lệch giờ, phút và giây giữa hai thời gian: =TEXT(B2-A2, “h:mm:ss”)
Tính chênh lệch thời gian trong Excel bằng hàm text

Tính chênh lệch thời gian trong Excel bằng hàm text

Chú ý.

  • Kết quả của hàm TEXT luôn là văn bản. Các giá trị text được căn lề trài trong các cột C, D, E trong hình trên. Trong vài tình huống nhất định, điều này có thể là một hạn chế vì bạn sẽ không thể sử dụng kết quả này để tiếp tục tính toán.
  • Nếu kết quả là một số âm, công thức TEXT sẽ hiển thị lỗi #VALUE!.

3. Tính giờ, phút, giây giữa hai thời gian trong Excel

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

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

=(End time – Start time) * 24

Giả sử thời gian bắt đầu trong ô A2 và thời gian kết thúc trong ô B2, bạn hãy sử dụng 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.

=(B2-A2) * 24

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

=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

Công thức có thể cho kết quả âm hoặc dương, 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:

=(B2-A2)*1440

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

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

=(End time – Start time) * 86400

Trong ví dụ, công thức sẽ như sau:

=(B2-A2)* 86400

Chú ý. Đị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 để kết quả hiển thị đúng.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ