Khi thao tác với dữ liệu là thời gian thì thường người dùng cần phải biết cách sắp xếp ngày tháng năm trong Excel theo thứ tự tăng dần hay giảm dần. Và một thao tác không kém phần quan trọng cho dữ liệu thời gian đó là tính thời gian chệnh lệch giữa 2 mốc thời gian. Sau đây Huongdancongnghe.com xin hướng dẫn các bạn các cách tính chênh lệch thời gian trong Excel đơn giản nhưng hiệu quả nhé. Mời các bạn cùng theo dõi.
Nội dung chính:
Cách tính chênh lệch thời gian trong Excel dùng phép trừ
Để thực hiện việc tính chênh lệch giữa hai mốc ngày, bạn chỉ việc thực hiện phép trừ. Cách cộng trừ thời gian trong Excel bạn cũng thực hiện bình thường như trừ hai con số. Kết quả trả về của phép trừ này là số ngày chênh lệch trong Excel.
Và nếu dữ liệu của ngày có cả thời gian giờ, phút, giây thì kết quả của sẽ là số thập phân. Trong đó phần thập phân là chênh lệch giờ của hai ngày chia cho 24.
Cách tính chênh lệch giờ giữa hai ngày trong Excel
Bạn trừ hai ngày cho nhau rồi lấy kết quả nhân với 24, sẽ cho ra số giờ chênh lệch giữa hai ngày.
Cách tính số phút chênh lệch giữa hai ngày trong Excel
Bạn trừ 2 ngày cho nhau rồi nhân kết quả với 1440 (1 ngày = 24 giờ = 1440 phút). Kết quả cho ra sẽ là phần chênh lệch phút giữa 2 ngày.
Công thức tính giây chênh lệch giữa hai ngày
Tương tự với giây thì bạn cũng trừ hai ngày cho nhau. Và lấy kết quả nhân với 86400 (1 ngày = 24 giờ = 1440 phút = 86400 giây).
Cách tính chênh lệch thời gian trong Excel dùng hàm TEXT
Cách này chúng ta sẽ dùng hàm TEXT trong Excel để tính chênh lệch thời gian. Nó sẽ định dạng lại kết quả của phép trừ 2 mốc thời gian thành dạng giờ (h), phút (h:mm) và giây (h:mm:ss)
Công thức tính chênh lệch giờ: =TEXT(thời gian kết thúc-thời gian bắt đầu,“h”)
Công thức tính chênh lệch giờ và phút: =TEXT(thời gian kết thúc-thời gian bắt đầu,“h:mm”)
Công thức tính chênh lệch giờ, phút và giây: =TEXT(thời gian kết thúc-thời gian bắt đầu,“h:mm:ss”)
Một lưu ý nhỏ cho các bạn khi sử dụng hàm này là kết quả chỉ là văn bản, nên không thể dùng nó để tính toán tiếp được. Và một ý nữa đó là nếu kết quả là số âm, Excel sẽ hiện lỗi #VALUE vào ô kết quả.
Cách tính chênh lệch 2 mốc thời gian cùng ngày
Sau đây mình sẽ giới thiệu các hàm trong Excel để tính chênh lệch thời gian trong cùng 1 ngày. Các bạn có thể áp dụng cách này để tính tổng giờ làm việc trong Excel.
Công thức tính chênh lệch chỉ lấy giờ: =HOUR(thời gian kết thúc-thời gian bắt đầu).
Với công thức này kết quả trả về không vượt quá 24 giờ.
Công thức tính chênh lệch chỉ lấy phút: =MINUTE(thời gian kết thúc-thời gian bắt đầu).
Kết quả trả về sẽ không vượt quá 60 phút.
Công thức tính chênh lệch chỉ lấy giây: =SECOND(thời gian kết thúc-thời gian bắt đầu).
Công thức cho kết quả trả về không vượt quá 60 giây.
Có một lưu ý là nếu thời gian kết thúc lớn hơn thời gian bắt đầu thì sẽ có lỗi #NUM!. Nghĩa là kết quả là một số âm.
Cách tính chênh lệch thời gian trong Excel với hàm NOW and TIME
Công thức lấy thời gian hiện tại là: =NOW().
Giả sử bạn muốn Tính chênh lệch ngày trong Excel. Chênh lệch muốn tính giữa ngày hiện tại với một ngày nào đó trong quá khứ, trong bài viết này mình dùng thời gian bắt đầu.
Hãy sử dụng công thức: =INT(NOW()) – thời gian bắt đầu. Trong đó định dạng kết quả ở dạng số với hàm: =INT(Kết_quả)
Nếu bạn muốn tính chênh lệch thời gian hiện tại và thời gian bắt đầu và kết quả bạn cần là giờ, phút, giây thì hãy sử dụng công thức: =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) – thời gian bắt đầu.
Lưu ý hàm NOW() sẽ lấy thời gian hiện tại nên giá trị sẽ được cập nhật lại khi bạn tắt mở lại file Excel hoặc nhấn nút F9.
Cách tính khoảng thời gian trong Excel và hiện thời gian âm
Đổi hệ thống thời gian Excel
Kết quả âm Excel của việc trừ 2 thời gian sẽ hiển thị như sau #####.
Mình sẽ hướng dẫn các bạn cách tính chênh lệch thời gian và hiển thị thời gian âm trong Excel.
Tại giao diện chính của Excel, chọn File > Options > Advanced hoặc dùng tổ hợp phím Alt + F > T.
Cửa sổ Excel Options sẽ xuất hiện. Bạn kéo xuống tìm When calculating this workbook và tick chọn Use 1904 date system và nhấn OK.
Khi đó Excel sẽ hiển thị kết quả thời gian âm như ý bạn muốn.
Sử dụng công thức để tính thời gian âm
Công thức như sau:
=IF(thời gian kết thúc-thời gian bắt đầu>0, thời gian kết thúc-thời gian bắt đầu, TEXT(ABS(thời gian kết thúc-thời gian bắt đầu),”-h:mm”))
Giải thích công thức:
- Nếu chênh lệch thời gian lớn hơn 0, kết quả dương bình thường.
- Nếu chênh lệch thời gian nhỏ hơn 0. Chúng ta dùng hàm ABS sẽ tính giá trị tuyệt đối định dạng rồi hàm TEXT để đổi cách hiển thị kết quả với định dạng thời gian âm “-h:mm”.
Qua bài viết này mình đã hướng dẫn các bạn các cách tính chênh lệch thời gian trong Excel một cách chi tiết. Với các công thức ở trên các bạn có thể áp dụng trong các bài toán như Cách tính tổng thời gian trong Excel hay Tính tổng số giờ làm việc trong ngày, trong tháng của nhân viên... Hi vọng bài viết hữu ích cho các bạn làm việc văn phòng hay các bạn đang tìm hiểu về Excel. Xin cám ơn và hẹn gặp lại trong những thủ thuật về Excel tiếp theo nhé.
