Trong bài viết này, Học Excel Online 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ờ; cách cộng trừ thời gian trong excel.
Bạn đang xem: Hàm tính giờ phút trong excel
Cách tính chênh lệch thời gian trong Excel
Cách tính và hiển thị thời gian âm trong Excel
Cộng/trừ thời gian trong Excel
Cách cộng thời gian trong Excel
Cách hiển thị khoảng thời gian lớn hơn 24 giờ, 60 phút, 60 giây?
Cách cộng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây
Cách tính chênh lệch thời gian trong Excel
Công thức 1. Trừ thời gian
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ụ:
| Công thức | Giải thích |
| =A2-B2 | Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2 |
| =TIMEVALUE(“8:30 PM”) – TIMEVALUE(“6:40 AM”) | Tính toán chênh lệch thời gian giữa hai thời gian cụ thể |
| =TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) – TIME(HOUR(B2), MINUTE(B2), SECOND(B2)) | Tính toán chênh lệch thời gian giữa hai giá trị trong ô A2 và B2, không tính chênh lệch ngày tháng nếu ô tính chứa cả giá trị ngày tháng và thời gian |
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:
| Mã thời gian | Giải thích |
| h | Số giờ trôi qua, ví dụ 4 |
| h:mm | Số giờ và số phút trôi qua, ví dụ 4:10 |
| h:mm:ss | Số giờ, số phút và số giây trôi qua, ví dụ 4:10:20. |
Để á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.
Công thức 2. Tính chênh lệch thời gian 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”)
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.Công thức 3. Tính giờ, phút, giây giữa hai thời gian
Để 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.
Công thức 4. Tính chênh lệch thời gian trong cùng một đơn vị thời gian và bỏ qua các đơn vị khác
Để 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, hãy sử dụng một trong các hàm sau:
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)
Chênh lệch giây, bỏ qua giờ và phút:
=SECOND(B2-A2)
Khi sử dụng hàm HOUR, MINUTE, SECOND, hãy nhớ rằng kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.
Chú ý. Nếu thời gian kết thúc lớn hơn thời gian bắt đầu, cụ thể nếu kết quả là số âm, công thức sẽ hiển thị lỗi #NUM!
Công thức 5. Tính thời gian trôi qua kể từ thời gian bắt đầu đến bây giờ
Để tính thời gian đã trôi qua, bạn có thể sử dụng hàm Now và Time. Trường hợp khoảng thời gian trôi qua dưới 24 giờ, 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.
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, ví dụ d ‘days’ h:mm:ss như trong hình sau:
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 để tính toán khoảng thời gian trôi đi một cách chính xác. 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, nhấn Shift + F9 để tính lại trang tính hoặc nhấn F9 để tính lại toàn bộ bảng tính.
Công thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”
Bạn sử dụng các hàm HOUR, MINUTE và SECOND để lấy kết quả thời gian tương tứng và hàm INT để tính chênh lệch ngày.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”,””)
Ngoài ra, 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”
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.
Phương pháp 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:
Phương pháp 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.
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 TIMESử 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.
Tham khảo ngay: Giáo trình Excel 2020
Cộng/trừ giờ vào thời gian cụ thể
Hàm thời gian để cộng thêm dưới 24 giờ
=Start time + TIME(N hours, 0, 0)
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)
Công thức để cộng bất kỳ số giờ nào (dưới/trên 24 giờ)
=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.
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)
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)Cách cộng thời gian trong ExcelPhé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:
Hiển thị thời gian dưới dạng giây:
Kết quả như sau:
Cách cộng qua 24 giờ trong Excel
Để tính toán kết quả hơn 24 giờ, bạn có thể sử dụng công thức SUM, và chọn một trong các định dạng sau:
| Định dạng | Hiển thị | Giải thích |
| 30:10 | Giờ và phút | |
| 30:10:20 | Giờ, phút và giây | |
| 30 hours, 10 minutes, 20 seconds | ||
| d h:mm:ss | 1 06:10:20 | Ngày, giờ, phút và giây |
| d “day” h:mm:ss | 1 day 06:10:20 | |
| d “day,” h “hours,” m “minutes and” s “seconds” | 1 day, 6 hours, 10 minutes and 20 seconds |
Để xem kết quả định dạng trong trang tính thực, xem hình dưới đây (công thức SUM được nhập vào ô A9 tới ô A13:
=SUM($B$2:$B$4)
Chú ý. Định dạng thời gian chỉ áp dụng cho số dương. Nếu kết quả tính toán là số âm, nghĩa là bạn lấy giá trị nhỏ hơn trừ cho giá trị lớn hơn, kết quả sẽ hiển thị #####.
Cách 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ư
Lớn hơn 24 giờ:
Lớn hơn 60 phút:
Lớn hơn 60 giây:
Ả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.
| Miêu tả | Mã định dạng |
| Tổng số giờ | |
| Số giờ và phút | |
| Số giờ, phút, giây | |
| Tổng số phút | |
| Số phút và giây | |
| Tổng số giây |
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:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | |
| 3 | Số giờ và phút | 50:40 | |
| 4 | Số giờ, phút, giây | 50:40:30 | |
| 5 | Số phút | 3040 | |
| 6 | Sốphút và giây | 3040:30 | |
| 7 | Số giây | 182430 |
Để 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ụ:
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ và phút | 50 giờ và 40 phút | |
| 3 | Số giờ, phút, giây | 50h.40m.30s | |
| 4 | Số phút | 3040 phút | |
| 5 | Số phút và giây | 3040 phút và 30 giây | |
| 6 | Số giây | 182430 giây |
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.
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âyThờ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 ý:
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 ô.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.Đó là toàn bộ cách để các bạn có thể cách cộng trừ thời gian trong excel. Nếu còn bất cứ thắc mắc gì thì đừng ngại ngần cmt xuống dưới để được giải đáp nhanh nhất nhé!
Một trong các thao tác xử lý dữ liệu thời gian chúng ta sử dụng phổ biến nhất làđổi giờ ra số trong Excel. Không chỉ quy đổi giờ sang các đơn vị nhỏ hơn như phút và giây, với ô tính chứa cả thông tin ngày tháng và thời gian, bạn còn có thể tách riêng dữ liệu thời gian và đổi giờ ra phút trong Excel. Hãy cùng mamnongautruc.edu.vn tìm hiểu cách làm trong bài viết ngày hôm nay nhé.
Cách đổi giờ ra số trong Excel
Trong bài viết này, mình sẽ hướng dẫn bạn cách đổi giờ ra số trong Excel, bao gồm số giờ, số phút và số giây. Có rất nhiều cách bạn có thể áp dụng để thực hiện thao tác quy đổi định dạng thời gian trong Excel, cụ thể như:
Sử dụng các phép tính trong Excel, ví dụ:
Để đổi mốc thời gian ra số giờ, chúng ta thực hiện phép nhân mốc thời gian với 24.Để đổi giờ ra phút trong Excel, chúng ta viết công thức nhân giá trị giờ với 24*60Để đổi giờ ra giây trong Excel, chúng ta thêm một giá trị 60 vào phép nhân số phút để được công thức giá trị giờ nhân 24*60*60.Sử dụng hàm Excel các hàm thời gian trong Excel:
Hàm HOUR: Hàm quy đổi thời gian ra đơn vị giờHàm MINUTE: Hàm quy đổi thời gian ra đơn vị phút
Hàm SECOND: Hàm quy đổi thời gian ra đơn vị giây
Sử dụng hàm CONVERT để quy đổi đơn vị.
Như vậy, bạn đã nắm qua các cách chuyển đổi định dạng thời gian trong Excel sang số thập phân rồi. Chúng ta hãy tìm hiểu kỹ hơn các phương pháp này nhé!
Cách chuyển đổi định dạng thời gian ra đơn vị giờ
Nếu bạn muốn quy đổi dữ liệu thời gian trên trang tính Excel của mình sang đơn vị giờ, có 3 cách đổi giờ ra số trong Excel sẽ giúp bạn làm được điều này,
Sử dụng phép nhân trong ExcelCách đầu tiên và cũng là cách đơn giản nhất để tính ra số giờ từ dữ liệu thời gian là thông qua một phép tính nhân cơ bản. Để giúp bạn dễ hiểu các bước, chúng ta hãy cùng thực hành một ví dụ nhé.
Giả sử bạn có dữ liệu thời gian 4:00 PM trong ô A2. Yêu cầu đặt ra là chuyển đổi giá trị này về số thập phân để tính số giờ đã trôi qua trong ngày.
Để đổi giờ ra số trong Excel, bạn hãy sử dụng phép nhân dưới đây:
=A2*24
Trong đó: A2 là ô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Nếu bạn đang thắc mắc tại sao chúng ta có thể đổi giờ ra số trong Excel bằng phép nhân này, câu trả lời nằm ở cơ chế lưu trữ dữ liệu thời gian của hệ thống. Excellưu trữ ngày tháng dưới dạng các con số sê-ri thập phân.
Cụ thể hơn về định dạng thời gian trong Excel với các số sê-ri thập phân:
Do một ngày (24 giờ) được Excel tính là 1, chúng ta hiểu rằng mỗi giờ được lưu trữ với giá trị 1/24.
Như vậy, nếu bạn nhìn thấy thời gian trong ô tính Excel hiển thị là 4:00 PM, thì dữ liệu này thực chất được Excel lưu trữ dưới giá trị 0.(6), nghĩa là 16 giờ đã trôi qua trong tổng 24 giờ của một ngày. Dựa vào logic này, khi bạn thực hiện phép nhân giá trị thời gian tại ô A2 với 24, kết quả đổi giờ ra số trong Excel thu được là 16.
Trong trường hợp dữ liệu thời gian lẻ như 5:27 PM, phép nhân A2*24 sẽ cho ra kết quả 17.45 với đơn vị phút được thể hiện trong phần thập phân. Cụ thể hơn, giá trị tương ứng với 27 phút trong Excel là 0.45 giờ.
Lưu ý: Nếu giá trị giờ của bạn chứa phần thập phân (giả sử 17.45), hãy đảm bảo ô tính đã được định dạng số hiển thị phần thập phân. Nếu không, bạn sẽ thấy kết quả đổi giờ ra số trong Excel được làm tròn (17 thay vì 17.45).
Để thay đổi định dạng thời gian trong Excel, bạn hãy nhấn chuột phải tại ô tính > Format Cells > Number, sau đó xác định số chữ số muốn hiển thị trong phần thập phân tại mục Decimal places.
Nếu như bạn chỉ cần quan tâm đến phần giờ (phần số nguyên) mà không cần chú ý đến phần phút (phần thập phân) trong kết quả đã chuyển đổi, bạn có thể sử dụng hàm INT - hàm tách riêng phần nguyên của số thập phân:
=INT(A2*24)
Trong đó:A2 là ô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Nếu bạn không muốn viết công thức phép nhân thủ công, một phương pháp đơn giản khác để đổi giờ ra số trong Excel là sử dụng cá hàm thời gian như hàm HOUR, hàm MINUTE và hàm SECOND.
Giả sử mình có một bảng dữ liệu thời gian như trong hình dưới đây. Câu hỏi cần trả lời là: Bao nhiêu giờ đã trôi qua tương ứng với các mốc thời gian trong cột A?
Dưới đây là công thức hàm thời gian trong Excel giúp mình trả lời câu hỏi trên:
=HOUR(A2)+MINUTE(A2)/60+SECOND(A2)/(60*60)
Trong đó: A2 làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Như bạn thấy, công thức hàm thời gian trong Excel là sự kết hợp của 3 hàm: Hàm HOUR, hàm MINUTE và hàm SECOND. Các hàm này sẽ lần lượt trả về giá trị số thập phân tương ứng với dữ liệu trong ô A2, sau đó kết hợp các kết quả với nhau để ra một con số hoàn chỉnh.
Tuy nhiên, vì chúng ta cần quy đổi ra đơn vị giờ nên kết quả hàm MINUTE sẽ chia cho 60 (vì 1 giờ = 60 phút) và kết quả hàm SECOND sẽ chia cho 60*60 (vì 1 giờ = 60*60 giây).
Sử dụng hàm CONVERT trong ExcelTrên thực tế, Excel có tích hợp một hàm riêng để thực hiện các thao tác chuyển đổi đơn vị giờ, phút, giây. Đó là hàm CONVERT. Có lẽ đây phương pháp đơn giản nhất trong số 3 phương pháp đổi giờ ra số trong Excel mình đã giới thiệu.
Với bảng dữ liệu như ở phần trên, chúng ta sẽ điền công thức hàm CONVERT trong Excel tại ô B2 như sau:
=CONVERT(A2,"day","hr")
Trong đó:
A2 - làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.day - định dạng ngày là định dạng dữ liệu gốc của ô A2.hr - định dạng giờ là định dạng dữ liệu đích muốn chuyển đổi.
Cách chuyển đổi định dạng thời gian ra đơn vị giờ
Tiếp theo, chúng ta sẽ tìm hiểu 3 phương pháp tương tự như trong phần trên để đổi giờ ra phút trong Excel: Sử dụng phép nhân, sử dụng hàm thời gian và sử dụng hàm CONVERT.
Sử dụng phép nhân trong ExcelMột ngày có 1.440 phút (24*60). Do đó, khi đổi giờ ra phút trong Excel, bạn cần nhân giá trị cần đổi với 1.440.
Giả sử bạn có giá trị 5:00:00 PM tại ô A2, công thức dưới đây sẽ giúp bạn chuyển đổi giá trị này về số phút.
=A2*1440
Trong đó: A2làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Công thức này sẽ cho bạn biết tổng số phút đã trôi qua trong ngày.
Lưu ý:Nếu giá trị giờ của bạn chứa phần thập phân (giả sử 615.67), hãy đảm bảo ô tính đã được định dạng số hiển thị phần thập phân. Nếu không, bạn sẽ thấy kết quảđổi giờ ra phút trong Excelđược làm tròn (616 thay vì 615.67). Bạn nên hiển thị 2 hoặc 3 chữ số phần thập phân để đảm bảo kết quả đúng.
Nếu như bạn chỉ cần quan tâm đến phần phút (phần số nguyên) mà không cần chú ý đến phần giây (phần thập phân) trong kết quả đã chuyển đổi, bạn có thể sử dụng hàm INT:
=INT(A2*1440)
Trong đó:A2 là ô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Tương tự như cách chuyển đổi giá trị giờ ra số thập phân, chúng ta có thể áp dụng các hàm thời gian trong Excel để quy đổi giờ ra phút. Công thức kết hợp 3 hàm HOUR, MINUTE và SECOND trong trường hợp đổi giờ ra phút trong Excel như sau:
=HOUR(A2)*60+MINUTE(A2)+SECOND(A2)/60
Trong đó:A2 là ô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Để có được kết quả ở đơn vị phút, chúng ta nhân số giờ của hàm HOUR với 60 và ngược lại, chia số giây của hàm SECOND với 60, sau đó cộng tổng các giá trị của các hàm và có kết quả cuối cùng.
Sử dụng hàm CONVERT trong ExcelCông thức hàm CONVERT có thể giúp bạn đổi giờ ra số trong Excel dưới nhiều đơn vị, bao gồm giờ, phút, giây. Dưới đây là công thức bạn sẽ cần để đổi giờ ra phút trong Excel:
=CONVERT(A2,"day","mn")
Trong đó:
A2 - làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.day - định dạng ngày là định dạng gốc của ô A2.mn - định dạng phút là định dạng đích muốn chuyển đổi.
Cách chuyển đổi định dạng thời gian ra đơn vị phút
Không chỉ với đơn vị giờ, phút, mà 3 phương pháp chúng ta đã thực hành còn có thể được áp dụng khi bạn cần đổi giờ ra giây trong Excel. Chúng ta hãy cùng thực hành từng cách làm với ví dụ dưới đây nhé.
Sử dụng phép nhân trong ExcelMột ngày có 86.400 giây (24*60*60).Do đó, khi đổi giờ ra giây trong Excel, bạn cần nhân giá trị cần đổi với 86.400.
Giả sử bạn có giá trị 5:00:00 PM tại ô A2, công thức dưới đây sẽ giúp bạn chuyển đổi giá trị này về số phút.
=A2*86400
Trong đó: A2làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Công thức hàm thời gian trong Excel dưới đây sẽ giúp bạn tính bao nhiêu giây đã trôi qua trong ngày dựa vào từng mốc thời gian tương ứng:
=HOUR(A2)*60*60+MINUTE(A2)*60+SECOND(A2)
Trong đó: A2 làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.
Logic của công thức này là tính số giây bằng cách quy đổi các đại lượng giờ, phút, giây và cộng tổng của chúng lại để ra kết quả cuối cùng của phép đổi giờ thành giây trong Excel. Cụ thể hơn, chúng ta nhân số giờ với 60*60 và nhân số phút với 60.
Sử dụng hàm CONVERT trong ExcelHàm CONVERT Excel vẫn là phương pháp nhanh nhất giúp bạn đổi giờ thành giây trong Excel. Hãy điền vào ô B2 công thức dưới đây:
=CONVERT(A2,"day","sec")
Trong đó:
A2 - làô tính chứa dữ liệu thời gian cần được quy đổi ra số giờ.day - định dạng ngày là định dạng gốc của ô A2.sec - định dạng giây là định dạng đích muốn chuyển đổi.
Cách tách riêng dữ liệu thời gian và đổi giờ ra số trong Excel
Trong phần trên, chúng ta đã tìm hiểu 3 phương pháp đổi giờ ra số trong Excel cực kì đơn giản và nhanh chóng. Vậy nếu ô tính của bạn chứa cả dữ liệu ngày tháng và dữ liệu thời gian thì phải làm thế nào?
Trong trường hợp này, bạn sẽ không thể sử dụng trực tiếp các phương pháp đã thực hành. Do định dạng thời gian trong Excel được lưu trữ dưới dạng số sê-ri thập phân, trong đó, phần nguyên đại diện cho ngày tháng và phần thập phân đại diện cho thời gian, chúng ta sẽ tách riêng các phần trước khi thực hiện quy đổi các đơn vị giờ, phút, giây.
Hãy cùng mình thực hành các phương pháp tách dữ liệu đểđổi giờ ra số trong Excel nhé.
Tách các phần dữ liệu bằng hàm INT
Giả sử chúng ta có bảng dữ liệu ngày và giờ như trong hình dưới đây. Yêu cầu đặt ra là tách riêng dữ liệu giờ và chuyển đổi về giá trị số.
Đầu tiên, chúng ta sẽ xác định mục đích công thức Excel giải bài toán trên: Loại bỏ phần số nguyên và để lại phần số thập phân trong số sê-ri đại diện cho dữ liệu của ô tính Excel. Để thực hiện được thao tác này, chúng ta sẽ sử dụng hàm INT để lấy ra phần nguyên, sau đó trừ trực tiếp kết quả hàm INT vào giá trị gốc tại ô tính.
Như vậy, công thức đổi giờ ra số trong Excel tại ô B2 được viết như sau:
=A2-INT(A2)
Trong đó: A2 là ô tínhchứa dữ liệu thời gian cần được quy đổi ra số giờ.
Sau khi đã tách được giá trị giờ khỏi dữ liệu gốc, bạn có thể tiếp tục sử dụng 1 trong 3 phương pháp đổi giờ ra số trong Excel chúng ta đã thực hành ở phần trên: Sử dụng phép nhân, sử dụng các hàm trong Excel và sử dụng hàm CONVERT.
Tách các phần dữ liệu bằng hàm MOD
Một hàm khác có thể giúp bạn tách riêng giá trị giờ khỏi dữ liệu ngày và giờ là hàm MOD. Đây là hàm trả về số dư sau khi số bị chia chia cho số chia.Kết quảhàmtrả về sẽ cùng dấu với số chia (không quan tâm tới dấu số bị chia).
Với bảng dữ liệu phía trên, công thức hàm MOD được sử dụng là:
=MOD(A2,1)
Trong đó:
A2 - là số bị chia1 - là số chia
Công thức hàm MOD sẽ trả về kết quả là số dư trong phép chia dữ liệu ngày tháng dưới dạng số sê-ri cho 1, nghĩa là phần thập phân trong con số gốc. Như vậy, bạn đã tách riêng được giá trị giờ và có thể tiếp tục sử dụng cách đổi giờ ra số trong Excel.
Tổng kết
Trong bài viết ngày hôm nay, chúng ta đã tìm hiểu các phương pháp đổi giờ ra số trong Excel để xử lý dữ liệu thời gian với đơn vị giờ, phút, giây. Bạn có thể sử dụng bất kỳ phương pháp nào thuận tiện nhất cho trang tính Excel của mình.
Xem thêm: Tổng Hợp 16 Cách Giảm Cân Hiệu Quả An Toàn Cho Nữ Mà Không Cần Ăn Kiêng
Hy vọng bài viết đã đem lại cho bạn các thủ thuật hữu ích khi làm việc với định dạng thời gian trong Excel, Để tìm hiểu thêm các kiến thức tin học văn phòng khác bạn hãy tham khảo các bài viết về chủ đề Excel trên blog mamnongautruc.edu.vn nhé.