Cách tính và hiển thị thời gian trong excel

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 và hiển thị thời gian trong excel

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, Cách tính và hiển thị thời gian âm trong excel, chênh lệch thời gian, đếm giờ…thời gian trong Excel

1. Cách tính và hiển thị thời gian âm trong excel

Khi tính toán chênhh lệch thờ gian, bạn có thể bắt gặp lỗi ##### vì độ chênh lệch này âm. Tuy nhiên, cũng có nhiều cách để hiển thị kết quả âm trong Excel.

1.1. Thay đổi hệ thông ngày tháng của excel thành 1904 date system

Vào File > Options > Advanced, kéo thanh cuốn xuống When calculating this workbook và đánh dấu chọn Use 1904 date system.

Nhấp OK để lưu cài đặt mới, giá trị âm sẽ được hiển thị như sau:

1.2. Tính toán thời gian âm bằng công thức

Nếu không muốn sử dụng phương pháp 1, bạn có thể sử dụng công thức sau:

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

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

Cả hai công thức sẽ kiểm tra chênh lệch thời gian (A2-B2) có lớn hơn 0 hay không và nó có hiện kết quả hay không. Nếu chênh lệch bé hơn 0, công thức đầu tiên sẽ tính trị tuyệt đối rồi thêm dấu trừ. Công thức thứ hai cho kết quả tương tự bằng định dạng thời gian âm “-h:: mm”.

Chú ý. Hãy nhớ rằng phương pháp đầu tiên cho ra kết quả âm là giá trị số âm, kết quả hàm TEXT luôn là chuỗi văn bản và không thể sử dụng trong phép toán hoặc công thức khác.

2. Cộng/trừ thời gian trong excel

Về cơ bản, có 2 cách cộng/trừ thời gian trong Excel:

  • Sử dụng hàm TIME
  • Sử dụng phép toán dựa trên số giờ (24), số phút (1440), số giây (86400) trong một ngày.

Hàm TIME(hour, minute, second) giúp việc tính toán dễ dàng hơn, tuy nhiên nó không cho phép việc cộng trừ hơn 23 giờ, 59 phút hoặc 59 giây. Nếu bạn muốn tính toán khoảng thời gian lớn hơn, hãy sử dụng các phép toán dưới đây.

2.1. Cộng/trừ giờ vào thời gian trong Excel cụ thể

Hàm thời gian để cộng thêm dưới 24 giờ

=Start time + TIME(N hours, 0, 0)

Cộng/trừ thời gian trong excel

Cộng/trừ thời gian trong excel

Ví dụ, trong ô A2 là thời gian bắt đầu, bạn muốn thêm 2 giờ vào đó, công thức sẽ là:

=A2 + TIME(2, 0, 0)

Chú ý. Nếu bạn thử cộng hơn 23 giờ bằng hàm TIME, số giờ được chỉ định sẽ chia cho 24 và phần nguyên sẽ thêm vào thời gian bắt đầu. Ví dụ, nếu bạn cộng thêm 25 giờ vào 6/2/20155 10:00 AM (ô A4) bằng công thức =A4 + TIME(25, 0, 0), kết quả sẽ là: 06/02/2015 11:00, cụ thể là A4 + 1 giờ

2.2. Công thức để cộng bất kỳ số giờ nào (dưới/trên 24 giờ)

Công thức sau không có giới hạn về số giờ bạn muốn cộng thêm:

=Start time + (N hours/24)

Ví dụ, để cộng thêm 28 giờ vào ô A2, nhập công thức sau:

=A2 + (28/24)

Để trừ đi một số giờ, sử dụng công thức tương tự như thay dấu + bằng dấu –

Ví dụ, trừ ô A2 cho 3 giờ, nhập một trong các công thức sau:

=A2-(3/24)

=A2-TIME(3,0,0)

Nếu bạn trừ số giờ lớn hơn 23, sử dụng công thức đầu tiên.

2.3. Cách cộng/trừ phút vào thời gian cụ thể

Cộng/trừ dưới 60 phút

Sử dụng hàm TIME để cộng trừ số phút bạn muốn vào tham số thứ 2:

=Start time + TIME(0, N minutes, 0)

Đây là một vài công thức thực tế để tính số phút trong Excel:

Công 20 phút vào A2: =A2 + TIME(0,20,0)

Trừ A2 cho 30 phút: =A2 – TIME(0,30,0)

Cộng trừ trên 60 phút

Trong phép tính của bạn, chia số phút cho 1440 – số phút trong một ngày và thêm thời gian bắt đầu vào :

=Start time + (N minutes / 1440)

Để trừ số phút, thay dấu + thành dấu -. Ví dụ :

Cộng thêm 200 phút: =A2 + (200/1440)

Trừ đi 300 phút: =A2 -(300/1440)

2.4. Cách cộng/trừ giây vào thời gian cụ thể

Để cộng thêm số giây ít hơn 60 vào thời gian cụ thể, sử dụng hàm TIME:

=Start time + TIME(0, 0, N seconds)

Để cộng thêm số giây lớn hơn 59, sử dụng công thức:

=Start time + (N seconds / 86400)

Để trừ số giây, tận dụng công thức tương tự nhưng thay dấu + thành dấu -.

Trên trang tính Excel, công thức trông như sau:

Thêm 30 giây vào ô A2: =A2 + TIME(0,0,31)

Thêm 1200 giây vào ô A2: =A2 + (1200/86400)

Trừ ô A2 cho 40 giây: =A2 – TIME(0,0,40)

Trừ ô A2 cho 900 giây: =A2 – (900/86400)

2.5. Cách cộng thời gian trong excel

Phép cộng Excel thường sử dụng hàm SUM, và áp dụng định dạng phù hợp cho kết quả.

Giả sử bạn có một vài thời gian trong cột B và bạn muốn cộng chúng với nhau. Bạn viết công thức SUM như sau =SUM(B2:B4), kết quả hiển thị theo định dạng mặc định như hh:mm:ss

 

Trong một vài trường hợp, định dạng thời gian mặc định có thể ổn, nhưng đôi khi bạn muốn hiển thị tổng thời gian dưới dạng phút và giây, hoặc chỉ hiển thị giây. Khi đó, bạn không cần phép tính nào cả, bạn chỉ cần định dạng lại thời gian cho ô tính chứa công thức SUM.

Nhấp phải vào ô tính và chọn Format Cells từ danh sách tùy chọn, nhấn Ctrl + 1 để mở hộp thoại Format Cells. Chọn Custom từ Category list và gõ một trong các định dạng sau vào hộp Type:

Hiển thị thời gian dưới dạng phút và giây: [m]:ss

Hiển thị thời gian dưới dạng giây: [ss]

Kết quả như sau:

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Đ