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.
Để 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ụ:
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:
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 #####.
Để 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.
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.
Để 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,
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.
Để 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!
Thời gian xét tuyển Cao đẳng Dược chính quy Y Khoa Phạm Ngọc Thạch bắt…
Để đáp ứng nhu cầu học tập của thí sinh, ở khu vực TPHCM có…
Trong những năm gần đây, học phí ngày càng tăng mạnh khiến thí sinh vô…
Cao đẳng Y Dược TPHCM tuyển sinh xét học bạ và miễn giảm học phí…
Học Cao đẳng Y mấy năm? là nỗi băn khoăn của nhiều thí sinh yêu…
Gộp dữ liệu 2 cột trong Excel là thủ thuật cũng thường xuyên được sử…