Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian
Cách tính toán thời gian trong excel; Đếm số giờ, khác biệt thời gian; Các công thức hữu ích để tính toán thời gian. Học Excel Online sẽ hướng dẫn trong bài viết này.
Cách tính chênh lệch thời gian trong Excel
Công thức 1. Trừ thời gian
Thời gian trong Excel thường được định dạng dưới dạng số thập phân để có vẻ giống như thời gian, như bạn đã biết. Bạn có thể thực hiện các phép tính cộng trừ với thời gian như với các giá trị số khác vì chúng cũng là số.
Công thức đơn giản nhất để tính sự khác biệt về thời gian là:
=Thời gian kết thúc – Thời gian bắt đầu.
Tùy theo cấu trúc dữ liệu, khoảng thời gian thực tế có thể có nhiều hình thức 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 Excel, thời gian được hiển thị ở phần thập phân, bạn sẽ nhận được kết quả như sau:.
Để hỗ trợ chúng tôi cung cấp thông tin hữu ích hơn, bạn có thể sử dụng định dạng thời gian với một trong các mã sau đây: Phần số thập phân trong cột D chính xác nhưng không mang ý nghĩa.
| 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. |
Để mở hộp thoại Định dạng ô, nhấn tổ hợp phím Ctrl + 1, sau đó chọn Tùy chỉnh từ danh sách Thể loại và nhập mã thời gian vào ô Loại để áp dụng định dạng thời gian.
Để hiểu cách công thức tính sự khác biệt về thời gian và mã thời gian hoạt động trong bảng tính thực tế, hãy xem ngay bây giờ. Bạn có thể sao chép công thức vào cột C và E, với thời gian bắt đầu trong cột A và thời gian kết thúc trong cột B.
=$B2-$A2.
Khoảng thời gian hiển thị khác nhau tùy thuộc vào định dạng thời gian trong từng cột:.
Nếu thời gian khác biệt được hiển thị thành #####, hãy lưu ý rằng ô chứa công thức không đủ rộng để hiển thị toàn bộ thời gian hoặc kết quả tính toán 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 phương pháp khác để tính độ chênh lệch thời gian trong Excel là sử dụng hàm TEXT.
Chú ý.
Công thức 3. Tính giờ, phút, giây giữa hai thời gian
Các phép tính dưới đây cần được thực hiện để hiển thị sự khác biệt giữa hai thời gian bằng một đơn vị (giờ, phút, giây).
Tính thời gian giữa hai mốc thời gian.
Bằng (thời gian kết thúc – thời gian bắt đầu) nhân 24.
Sau đó nhân với 24, 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. Giả sử thời gian kết thúc trong ô B2 và thời gian bắt đầu trong ô A2.
=(B2-A2) * 24.
Để có số giờ hoàn chỉnh, sử dụng hàm INT để làm tròn đến số nguyên gần nhất.
=SỐ.NGUYÊN((B2-A2) * 24).
Tổng số phút giữa hai khoảng thời gian là:.
Để tính số phút giữa hai thời điểm, nhân khoảng thời gian với 1440.
=(Thời gian kết thúc – Thời gian bắt đầu) * 1440.
Khi thời gian kết thúc lớn hơn thời gian bắt đầu như hàng 5, kết quả có thể âm hoặc dương.
=(B2-A2)*1440.
Tổng số giây giữa hai khoảng thời gian là:.
Để tính số giây giữa hai thời điểm, nhân sự khác biệt về thời gian với 86400.
=(Thời gian kết thúc – Thời gian bắt đầu) * 86400.
Trong trường hợp này, công thức sẽ là như sau:.
=(B2-A2)* 86400.
Để hiển thị kết quả đúng, hãy lưu ý rằng định dạng “Chung” nên được cài đặt cho ô tính chứa công thức tính sự khác biệt về thời gian.
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
Sử dụng một trong các hàm sau để tính khác biệt giữa hai thời gian trong cùng một đơn vị và bỏ qua các đơn vị thời gian khác:
Chênh lệch thời gian, bỏ qua phút và giây.
=HOUR(B2-A2).
Chênh lệch một 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).
Hãy nhớ rằng, khi sử dụng hàm GIỜ, PHÚT, GIÂY, kết quả không được vượt quá 24 giờ, 60 phút hoặc 60 giây.
Lỗi #NUM! Sẽ xuất hiện nếu thời gian kết thúc lớn hơn thời gian bắt đầu, đặc biệt là khi kết quả là số âm. Chú ý.
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ờ
Bạn chỉ cần dùng hàm HIỆN_TẠI để 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 để tính thời gian đã trôi qua. Để tính thời gian đã trôi qua dưới 24 giờ, bạn có thể dùng hàm HIỆN_TẠI và THỜI_GIAN.
Trong ví dụ này, công thức =NOW()-A2 được sử dụng để tính thời gian kể từ lúc bắt đầu được lưu trong ô A2. Kết quả được hiển thị trong cột B và được định dạng dưới dạng thời gian h:mm.
Ví dụ trong hình sau, sử dụng một trong các định dạng thời gian sau, nếu khoảng thời gian trôi qua vượt quá 24 giờ: d ‘ngày’ h:mm:ss.
Cách tính thời gian trôi đi chính xác là sử dụng hàm Time khi chỉ có giờ phút mà không có ngày. Ví dụ, công thức sau sẽ trả về khoảng thời gian từ ô A2 đến giờ.
=THỜI_GIAN(GIỜ(BÂY_GIỜ()), PHÚT(BÂY_GIỜ()), GIÂY(BÂY_GIỜ())) – A2.
Lưu ý. Để cập nhật công thức, nhấn Shift + F9 để tính lại bảng tính hoặc nhấn F9 để tính lại toàn bộ bảng tính. 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ông thức 6. Hiển thị chênh lệch thời gian “XX days, XX hours, XX minutes and XX seconds”
Các hàm GIỜ, PHÚT và GIÂY được sử dụng để lấy kết quả thời gian tương ứng và hàm SỐ NGUYÊN được sử dụng để tính chênh lệch ngày. Sau đó, các hàm được lồng ghép vào cùng một công thức cùng các chuỗi văn bản như sau:.
Số ngày =SỐ.NGÀY(B2-A2) & ” ngày, ” & số giờ = GIỜ(B2-A2) & ” giờ, ” & số phút = PHÚT(B2-A2) & ” phút và ” & số giây = GIÂY(B2-A2) & ” giây”.
Để che giấu giá trị 0, hãy nhúng bốn hàm IF vào nó:.
” Giây” & NẾU(GIÂY(B2-A2)>0, GIÂY(B2-A2) & ” phút và ”,””) & NẾU(PHÚT(B2-A2)>0, PHÚT(B2-A2) & ” giờ, ”,””) & NẾU(GIỜ(B2-A2)>0, GIỜ(B2-A2) & ” ngày, ”,””) & NẾU(SỐ.NGUYÊN(B2-A2)>0, SỐ.NGUYÊN(B2-A2) & ” ngày, ”,””)
Định dạng dưới đây cho ô tính, bạn có thể thiết lập bằng cách tính toán khác biệt thời gian (ví dụ = B2-A2) bằng cách lấy thời gian kết thúc trừ đi thời gian bắt đầu. Thêm vào đó, bạn cũng có thể tính toán khác biệt thời gian.
D ”ngày,” h ”giờ,” m ”phút và” s ”giây”.
Cách tính và hiển thị thời gian âm trong Excel
Có nhiều phương pháp để hiển thị kết quả âm trong Excel khi tính toán sự khác biệt về thời gian, bạn có thể gặp phải lỗi ##### do sự khác biệt này âm.
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ấu hình mới, số â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 áp dụng phương pháp 1, bạn có thể dùng công thức dưới đây:
=NẾU(A2-B2>0, A2-B2, ”-” & VĂN_BẢN(ABS(A2-B2),”h:mm”)).
=NẾU(A2-B2>0, A2-B2, Văn_bản(TUYỆT_ĐỐI(A2-B2),”-h:mm”)).
Công cụ rewrite tiếng Việt sẽ thay thế các từ trong đoạn văn Input theo ngữ cảnh và đưa ra đoạn văn Output đã được chỉnh sửa.
Lưu ý, kết quả âm là giá trị số âm phương pháp đầu tiên cho ra. Kết quả hàm VĂN BẢN luôn là chuỗi văn bản, không thể sử dụng trong phép toán hoặc công thức khác. Hãy nhớ rằng.
Cộng/trừ thời gian trong Excel
Về cơ bản, có hai phương pháp tính thời gian trong Excel:
Hỗ trợ tính toán thuận tiện hơn, hàm THỜI_GIAN(giờ, phút, giây). Tuy nhiên, nó không cho phép thực hiện phép cộng hoặc trừ vượt quá 23 giờ, 59 phút hoặc 59 giây. Nếu muốn tính toán khoảng thời gian lớn hơn, vui lòng sử dụng các phép toán sau.
Cộng/trừ giờ vào thời gian cụ thể
Hàm thời gian để thêm dưới 24 giờ.
=Thời gian bắt đầu + TIME(N giờ, 0, 0).
Ví dụ, trong ô A2 là thời điểm bắt đầu, bạn muốn cộng thêm 2 giờ vào đó, công thức sẽ là:.
=A2 + TIME(2, 0, 0).
Chú ý. Nếu bạn thử thêm hơn 23 giờ bằng hàm TIME, số giờ được chỉ định sẽ được chia cho 24 và phần nguyên sẽ được thêm vào thời gian bắt đầu. Ví dụ, nếu bạn thêm 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ờ.
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ó hạn chế về số giờ bạn muốn thêm vào.
=Thời gian bắt đầu + (N giờ/24).
Ví dụ, để thêm 28 giờ vào ô A2, nhập công thức sau:.
=A2 + (7/6).
Để lấy đi một số giờ, sử dụng công thức tương tự như thay dấu + thành dấu -.
Ví dụ, trừ ô A2 trong vòng 3 tiếng, chọn 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ờ vượt quá 23, hãy sử dụng công thức đầu tiên.
Cách cộng/trừ phút vào thời gian cụ thể
Tính toán dưới 60 phút.
Sử dụng hàm THỜI GIAN để thực hiện phép cộng trừ số phút mà bạn muốn vào tham số thứ hai.
=Start time + TIME(0, N phút, 0).
Đây là một số 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 đi 30 phút: =A2 – TIME(0,30,0).
Phép tính trong vòng 60 phút.
Khi bắt đầu, hãy thêm số phút trong một ngày và chia cho 1440 trong phép tính của bạn.
=Thời gian bắt đầu + (N phút / 1440).
Để giảm số phút, thay dấu + bằng dấu -. Ví dụ: .
Thêm 200 phút: =A2 + (200/1440).
Giảm đi 300 phút: =A2 -(300/1440).
Cách cộng/trừ giây vào thời gian cụ thể
Để thêm thời gian ít hơn 60 giây vào một thời gian cụ thể, sử dụng hàm TIME:.
=Thời gian bắt đầu + TIME(0, 0, N giây).
Để thêm số giây lớn hơn 59, áp dụng công thức sau:.
=Thời điểm bắt đầu + (N giây / 86400).
Để khấu trừ số giây, sử dụng công thức tương tự nhưng thay dấu cộng bằng dấu trừ.
Trên bảng tính Excel, công thức có vẻ như sau:.
Cách cộng thời gian trong Excel
Phép tính tổng trong Excel thường sử dụng hàm SUM và áp dụng định dạng thích hợp cho kết quả.
Theo sau là hh:mm:ss, nhưng bạn có thể thay đổi cách hiển thị theo ý muốn.
Trong một số trường hợp, định dạng thời gian mặc định có thể phù hợp, 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 chỉ cần định dạng lại thời gian cho ô tính chứa công thức TỔNG, bạn không cần phép tính nào cả.
Hộp thoại Format Cells có thể mở bằng cách nhấp chuột phải vào ô tính và chọn Format Cells từ danh sách lựa chọn. Để thay đổi cấu trúc câu, hãy nhấn tổ hợp phím Ctrl + 1. Nhập một trong các định dạng sau vào ô Type: và chọn Custom từ danh sách Category.
Hiển thị thời gian dưới dạng phút và giây: [m]:ss.
Hiển thị thời gian theo đơn vị giây: [ss].
Kết quả như dưới đây:
Cách tổng hợp qua 24 giờ trong Excel.
Có thể dùng phương pháp TỔNG để tính toán kết quả lớn hơn 24 giờ, và lựa chọn một trong những hình thức sau đây.
| Định dạng | Hiển thị | Giải thích |
| [h]:mm | 30:10 | Giờ và phút |
| [h]:mm:ss | 30:10:20 | Giờ, phút và giây |
| [h] “hours”, mm “minutes”, ss “seconds” | 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 ảnh dưới đây để xem kết quả định dạng trong trang tính thực (công thức TỔNG đượ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?
Hướng dẫn chi tiết như sau: 1. Sử dụng định dạng thời gian tùy chỉnh để hiển thị một khoảng thời gian vượt quá 24 giờ, 60 phút và 60 giây.2. Các ký hiệu thời gian tương ứng được đặt trong dấu ngoặc vuông, ví dụ như [h], [m], hoặc [s].
Vượt quá 24 giờ: [h]:mm:ss hoặc [h]:mm.
Trên 60 phút: [m]:ss.
Trên 60 giây: [s].
Hình ảnh bên dưới cho thấy định dạng thời gian ”vượt quá 24 giờ” trong thực tế.
Ta có thể sử dụng mẫu sau để hiển thị các khoảng thời gian lớn hơn đơn vị thời gian chuẩn. Dưới đây là một số mẫu khác.
| Miêu tả | Mã định dạng |
| Tổng số giờ | [h] |
| Số giờ và phút | [h]:mm |
| Số giờ, phút, giây | [h]:mm:ss |
| Tổng số phút | [m] |
| Số phút và giây | [m]:ss |
| Tổng số giây | [s] |
Kết quả sẽ được hiển thị như sau, những định dạng thời gian này khi áp dụng cho dữ liệu mẫu của chúng ta (Thời gian tổng cộng 50:40 trong hình ảnh màn hình trên):.
| A | B | C | |
| 1 | Miêu tả | Thời gian hiển thị | Định dạng |
| 2 | Số giờ | 50 | [h] |
| 3 | Số giờ và phút | 50:40 | [h]:mm |
| 4 | Số giờ, phút, giây | 50:40:30 | [h]:mm:ss |
| 5 | Số phút | 3040 | [m] |
| 6 | Sốphút và giây | 3040:30 | [m]:ss |
| 7 | Số giây | 182430 | [s] |
Bằng cách thêm các đơn vị thời gian tương ứng, bạn có thể làm cho những khoảng thời gian hiển thị trên trở nên dễ hiểu hơn đối với người dùng. Ví dụ, bạn có thể sử dụng các từ viết tắt để đại diện cho các đơn vị thời gian.
| 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 | [h] “số giờ và” mm “số phút” |
| 3 | Số giờ, phút, giây | 50h.40m.30s | [h] “h.” mm “m.” ss “s.” |
| 4 | Số phút | 3040 phút | [m] “số phút” |
| 5 | Số phút và giây | 3040 phút và 30 giây | [m] “số phút và” ss “số giây” |
| 6 | Số giây | 182430 giây | [s] “số giây” |
Lưu ý. Do đó, bạn có thể tự do thêm hoặc trừ những đoạn thời gian đã được định dạng một cách thông 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. Mặc dù các đoạn thời gian trên trông giống như chuỗi ký tự, nhưng chúng vẫn là giá trị số, vì định dạng số trong Excel chỉ thay đổi về hình thức mà không thay đổi bản chất.
Cách cộng trừ thời gian lớn hơn 24 giờ, 60 phút, 60 giây
Để tăng thời gian mong muốn cho một đoạn thời gian có sẵn, hãy lấy số giờ, phút, hoặc giây mà bạn muốn thêm vào cho đơn vị thời gian tương ứng trong một ngày (24 giờ, 1440 phút, 86400 giây), sau đó thêm kết quả vào thời gian ban đầu.
Cộng hơn 24 giờ.
Thời gian khởi đầu + (N/24).
Cộng hơn 60 phút.
Thời gian khởi đầu + (N/1440).
Cộng hơn 60 giây.
Thời điểm bắt đầu cộng thêm (N/86400).
Trong đó, N là tổng số giờ, phút hoặc giây mà bạn muốn thêm vào.
Dưới đây là một số ví dụ về công thức thực tế:
Để thêm 45 giờ vào thời gian bắt đầu trong ô A2:.
=A2+(45/24).
Để tăng thêm 100 phút cho thời gian bắt đầu trong ô A2:.
=A2+(100/1440).
Để thêm 200 giây vào thời gian bắt đầu trong ô A2:.
=A2+(200/86400).
Có thể nhập thời gian cần thêm vào một ô riêng biệt, sau đó tham khảo ô đó trong công thức có định dạng như trong hình ảnh màn hình dưới đây. Hoặc bạn có thể nhập thời gian cần thêm vào một ô riêng biệt và tham khảo ô đó trong công thức có định dạng như trong hình ảnh màn hình dưới đây.
Hãy sử dụng công thức tương tự trong Excel để trừ khoảng thời gian, nhưng hãy thay dấu cộng bằng dấu trừ.
Trừ hơn 24 giờ
Thời điểm khởi đầu – (N/24).
Trừ hơn 60 phút.
Thời gian khởi đầu – (N/1440).
Trừ hơn 60 giây
Thời điểm khởi đầu – (N/86400).
Hình ảnh dưới đây thể hiện các kết quả.
Lưu ý:.
Có thắc mắc gì sợ gì không bình luận dưới đây! Để được giải đáp nhanh chóng nhất, các bạn có thể tính tổng trừ thời gian trong excel bằng cách đó là hoàn toàn.
Hãy tham gia ngay tại địa chỉ: Đào tạo Excel trực tuyến. Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong bảng tính Excel, ứng dụng Excel trong công việc… Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Đào tạo Excel trực tuyến.
