Làm thế nào để cộng trừ khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây trong excel

Bài viết này Học Excel cơ bản sẽ cung cấp một vài mẹo tính toán và hiển thị những khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây.

Làm thế nào để hiển thị khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?

Để hiện thị một khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây, hãy sử dụng custom time format nơi mà mã đơn vị thời gian tương ứng nằm trong dấu ngoặc vuông, chẳng hạn như [h], [m], hay [s]. Dưới đây là các bước hướng dẫn chi tiết:

1. Chọn một hay  nhiều ô mà bạn muốn định dạng.
2. Nhấp chuột phải vào các ô được chọn rồi lại nhấp chuột phải vào Format Cells, hay bạn có thể nhấn Ctrl + 1, thao tác này sẽ giúp mở hộp thoại Format Cells.
3. Trong Number tab, bên dưới Category, hãy chọn Custom rồi gõ một trong những định dạng thời gian dưới đây trong hộp Type:

Lớn hơn 24 giờ: [h]:mm:ss hay [h]:mm

Lớn hơn 60 phút: [m]:ss

Lớn hơn 60 giây: [s]

Ảnh chụp màn hình dưới đây cho thấy định dạng thời gian “lớn hơn 24 giờ” trên thực tế:

Dưới đây là một vài định dạng khác. Chúng ta có thể sử dụng nó để hiển thị những khoảng thời gian lớn hơn chiều dài đơn vị thời gian chuẩn.

Khi được sử dụng cho dữ liệu mẫu của chúng ta (Thời gian tổng cộng 50:40 trong ảnh chụp màn hình trên), những định dạng thời gian này sẽ cho kết quả như sau:

Để những khoảng thời gian được hiển thị ở trên dễ hiểu hơn đối với người dùng, bạn có thể bổ sung đơn vị thời gian bằng các từ viết tắt tương ứng, ví dụ:

Lưu ý. Mặc dù những khoảng thời gian trên trông giống như chuỗi ký tự, nhưng chúng vẫn là giá trị số, bởi vì định dạng chữ số trong Excel chỉ thay đổi về hình thức chứ bản chất không thay đổi. Vì thế, bạn có thể tự do cộng hay trừ những khoảng thời gian đã được định dạng một cách bình thường, hãy tham chiếu chúng trong công thức và sử dụng trong các phép tính khác.

Bây giờ, bạn đã biết kỹ thuật cơ bản để hiển thị các khoảng thời gian lớn hơn  24 giờ trong Excel, HEO sẽ chia sẻ với bạn một vài công thức dùng trong các trường hợp cụ thể.

Tìm hiểu thêm: khóa học excel cơ bản

Tính toán sự chêch lệch thời gian theo giờ, phút, giây.

Để tính toán sự chênh lệch giữa hai khoảng thời gian – hai khoảng thời gian này có đơn vị thời gian cụ thể, hãy sử dụng một trong những công thức dưới đây.

Sự chênh lệch thời gian theo giờ

Để tính toán số giờ cách biệt giữa thời gian bắt đầu và thời gian kết thúc và hiển thị nó ở dạng số thập phân, hãy sử dụng công thức này:

(Thời gian kết thúc – thời gian bắt đầu) * 24

Để tính được số giờ phải hoàn thành, hãy sử dụng hàm INT để làm tròn số thập phân thành số nguyên có giá tị gần nhất.

=INT((B2-A2)*24)

Sự chênh lệch thời gian theo phút

Để tính số phút cách biệt của hai khoảng thời gian, hãy lấy thời gian kết thúc trừ thời gian bắt đầu, rồi nhân kết quả với 1440, đây là số phút trong một ngày (24 giờ*60 phút).

(Thời gian kết thúc – thời gian bắt đầu) *1440

Sự chênh lệch thời gian theo giây

Để tính số giây cách biệt của hai khoảng thời gian, hãy nhân kết quả với 86400, đây là số giây trong một ngày (24 giờ*60 phút* 60 giây).

(Thời gian kết thúc – thời gian bắt đầu) * 86400

Giả sử thời gian bắt đầu ở cột A3 và thời gian kết thúc ở cột B3, thì công thức có dạng như sau:

Giờ dưới dạng số thập phân: =(B3-A3)*24

Số giờ cần phải hoàn thành: =INT((B3-A3)*24)

Số phút: =(B3-A3)*1440

Số giây: =(B3-A3)*86400

Ảnh chụp màn hình dưới đây hiển thị kết quả:

Lưu ý:

1. Để có kết quả chính xác, thì cột chứa công thức phải được định dạng thành General (Tổng quát).
2. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu, thì sự chênh lệch thời gian sẽ được hiển thị như giá trị âm, chẳng hạn như trong hàng 5 trong ảnh chụp màn hình trên.

Xem thêm: khóa học Excel nâng cao

Cách cộng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây.

Để cộng khoảng thời gian mong muốn cho một khoảng thời gian cho sẵn, hãy lấy số giờ, phút, hay giây mà bạn muốn cộng cho đơn vị thời gian tương ứng trong một ngày (24 giờ, 1440 phút, 86400 giây), rồi cộng số thương cho thời gian bắt đầu.

Cộng hơn 24 giờ.

Thời gian bắt đầu + (N/24)

Cộng hơn 60 phút.

Thời gian bắt đầu + (N/1440)

Cộng hơn 60 giây.

Thời gian bắt đầu + (N/86400)

Trong đó, N là số giờ, phút, hay giây mà bạn muốn cộng.

Đây là một vài ví dụ công thức trên thực tế:

Để cộng 45 giờ cho thời gian bắt đầu trong ô A2:

=A2+(45/24)

Để cộng 100 phút cho thời gian bắt đầu trong ô A2:

=A2+(100/1440)

Để cộng 200 giây cho thời gian bắt đầu trong ô A2:

=A2+(200/86400)

Hay là, bạn có thể nhập khoảng thời gian cần cộng vào một ô riêng biệt rồi tham chiếu ô đó trong công thức có dạng như trong ảnh chụp màn hình dưới đây:

Để trừ khoảng thời gian trong Excel, hãy sử dụng công thức tương tự nhưng hãy thay dấu cộng bằng dấu trừ nhé!

Trừ hơn 24 giờ

Thời gian bắt đầu – (N/24)

Trừ hơn 60 phút.

Thời gian bắt đầu – (N/1440)

Trừ hơn 60 giây

Thời gian bắt đầu – (N/86400)

Ảnh chụp màn hình dưới đây hiển thị các kết quả:

Lưu ý:

1. Nếu khoảng thời gian được tính toán có dạng số thập phân, hãy sử dụng định dạng thời gian/ngày để định dạng ô.
2. Nếu sau khi sử dụng định dạng và ô hiển thị #####, thì thông thường ô không đủ rộng để hiển thị giá trị thời gian trong ngày. Để xử lý điều này, hãy mở rộng chiều dài ô bằng cách nhấp đúp chuột hay kéo khung bên phải của cột.

Đây là cách bạn có thể hiển thị, cộng hay trừ chiều dài khoảng thời gian trong Excel. Cảm ơn các bạn đã đọc bài viết này và hẹn gặp lại các bạn vào tuần sau.