doNgày và giờ được lưu dưới dạng số trong phần phụ trợ của Excel, vì vậy bạn có thể dễ dàng sử dụng các phép tính và công thức số học đơn giản trên các giá trị ngày và giờ.
Ví dụ: bạn có thể cộng và trừ hai giá trị thời gian hoặc giá trị ngày khác nhau hoặc bạn có thể tính chênh lệch thời gian giữa hai ngày/giờ nhất định.
Trong hướng dẫn này tôi sẽ chỉ cho bạn cách Thực hiện tính toán sử dụng thời gian trong Excelmột số phương pháp (như tính chênh lệch thời gian, cộng và trừ thời gian, hiển thị thời gian ở các định dạng khác nhau và tính tổng các giá trị thời gian).
Nội dung
- 1 Excel xử lý ngày và giờ như thế nào?
- 2 Công thức tính chênh lệch thời gian giữa hai thời điểm
- 3 Tính thời gian khi ngày thay đổi (tính toán và hiển thị thời gian âm trong Excel)
- 4 Cộng trừ thời gian trong Excel
- 5 Cách tính tổng thời gian trong Excel
- 6 Kết quả hiển thị hàm băm (###) thay vì ngày / giờ (nguyên nhân + sửa chữa)
- 7 Câu hỏi thường gặp về tính cộng và trừ thời gian trong Excel
- 8 Ồ, xin chào, rất vui được làm quen với bạn.
Excel xử lý ngày và giờ như thế nào?
Như tôi đã đề cập, ngày và giờ được lưu trữ dưới dạng số trong các ô trong Excel.Số nguyên đại diện cho cả ngày, phần thập phân của số đại diện cho một phần của ngày (có thể được chuyển đổi thành các giá trị giờ, phút và giây)
Ví dụ, giá trị 1 đại diện cho ngày 1900 tháng 1 năm 1 trong Excel, là điểm bắt đầu để Excel bắt đầu xem xét ngày tháng.
Vì vậy, 2 có nghĩa là ngày 1990 tháng 1 năm 2, 3 có nghĩa là ngày 1900 tháng 1 năm 3, v.v., 44197 có nghĩa là ngày 2021 tháng 1 năm 1.
Lưu ý: Excel cho Windows và Excel cho Mac tuân theo các ngày bắt đầu khác nhau.1 trong Excel cho Windows có nghĩa là ngày 1900 tháng 1 năm 1, 1 trong Excel cho Mac có nghĩa là ngày 1904 tháng 1 năm 1
Nếu những số này có bất kỳ chữ số nào sau dấu thập phân, Excel sẽ coi những số đó như một phần của ngày và có thể chuyển đổi chúng thành giờ, phút và giây.
Ví dụ: 44197.5 có nghĩa là ngày 2021 tháng 1 năm 1 lúc 12:00:00 CH.
Vì vậy, nếu bạn đang làm việc với các giá trị thời gian trong Excel, về cơ bản bạn đang làm việc với phần phân số của số.
Excel cung cấp cho bạn sự linh hoạt để chuyển đổi các phần của phân số sang các định dạng khác nhau, chẳng hạn như chỉ giờ, chỉ phút, chỉ giây hoặc kết hợp giờ, phút và giây
Bây giờ bạn đã hiểu cách thời gian được lưu trữ trong Excel, hãy xem một số ví dụ về cách tính chênh lệch thời gian giữa hai ngày hoặc giờ khác nhau trong Excel
Công thức tính chênh lệch thời gian giữa hai thời điểm
Trong nhiều trường hợp, tất cả những gì bạn phải làm là tìm ra tổng thời gian đã trôi qua giữa hai giá trị thời gian (ví dụ: trong trường hợp thời gian biểu có In-time và Out-time).
Phương pháp bạn chọn phụ thuộc vào cách thời gian được đề cập trong ô và cách bạn muốn kết quả được định dạng.
Hãy xem một vài ví dụ
Phép trừ đơn giản để tính chênh lệch thời gian trong Excel
Vì thời gian được lưu trữ dưới dạng số trong Excel, nên để tìm sự khác biệt giữa 2 giá trị thời gian, bạn có thể dễ dàng trừ thời gian bắt đầu cho thời gian kết thúc.
thời gian kết thúc - thời gian bắt đầu
Kết quả của phép trừ cũng sẽ là một giá trị thập phân đại diện cho thời gian đã trôi qua giữa hai giá trị thời gian.
Dưới đây là một ví dụ trong đó tôi có thời gian bắt đầu và thời gian kết thúc và tôi đã tính toán chênh lệch thời gian bằng phép trừ đơn giản.
Có thể kết quả của bạn được hiển thị ở định dạng thời gian (thay vì giá trị thập phân hoặc giờ / phút).Trong ví dụ trên, kết quả trong ô C2 hiển thị 09:30 AM thay vì 9.5.
Điều này là tốt vì Excel sẽ cố gắng sao chép định dạng từ các cột liền kề.
Để chuyển nó thành số thập phân, hãy thay đổi định dạng của ô thành "Chung" (tùy chọn này nằm trong tab "Trang chủ" của nhóm "Số")
Sau khi nhận được kết quả, bạn có thể định dạng nó theo nhiều cách khác nhau.Ví dụ: bạn có thể hiển thị các giá trị chỉ theo giờ, chỉ phút hoặc kết hợp giờ, phút và giây.
Dưới đây là các định dạng khác nhau mà bạn có thể sử dụng:
| định dạng | những gì nó có thể làm |
| hh | Chỉ hiển thị số giờ trôi qua giữa hai ngày |
| mm | Hiển thị giờ dưới dạng hai chữ số (ví dụ: 04 hoặc 12) |
| hh: mm | Hiển thị giờ và phút trôi qua giữa hai ngày, ví dụ: 10:20 |
| hh: mm: ss | Hiển thị giờ, phút và giây trôi qua giữa hai ngày, ví dụ: 10:20:36 |
Nếu bạn đang thắc mắc về vị trí và cách áp dụng các định dạng ngày tùy chỉnh này, hãy làm theo các bước sau:
- Chọn các ô để áp dụng định dạng ngày
- Giữ Control và nhấn 1 (Command + 1 nếu sử dụng máy Mac)
- Trong hộp thoại Định dạng Ô mở ra, hãy bấm vào tab Số (nếu chưa được chọn)
- Trong ngăn bên trái, nhấp vào Tùy chỉnh
- Nhập bất kỳ mã định dạng mong muốn nào vào trường Loại (Tôi đang sử dụng hh: mm: ss trong ví dụ này)
- Bấm OK
Các bước trên sẽ thay đổi định dạng và hiển thị cho bạn giá trị theo định dạng.
Lưu ý rằng định dạng số tùy chỉnh không thay đổi giá trị trong ô.Nó chỉ thay đổi cách giá trị được hiển thị.Vì vậy, tôi có thể chọn chỉ hiển thị giá trị giờ trong ô và nó vẫn có giá trị ban đầu.
MẸO CHUYÊN NGHIỆP: Nếu tổng số giờ vượt quá 24 giờ, hãy sử dụng định dạng số tùy chỉnh sau để thay thế: [hh]: mm: ss
Tính chênh lệch thời gian theo giờ, phút hoặc giây
Khi bạn trừ các giá trị thời gian, Excel sẽ trả về một số thập phân thể hiện sự chênh lệch thời gian kết quả.
Vì mỗi số nguyên đại diện cho một ngày, nên phần phân số của số sẽ đại diện cho hầu hết thời gian trong ngày và có thể dễ dàng chuyển đổi thành giờ hoặc phút hoặc giây.
Tính chênh lệch múi giờ theo giờ
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn tính số giờ giữa hai giá trị thời gian
Công thức dưới đây sẽ cung cấp cho bạn chênh lệch múi giờ theo giờ:
= (B2-A2) * 24
Công thức trên sẽ cung cấp cho bạn tổng số giờ đã trôi qua giữa hai giá trị thời gian.
Đôi khi Excel sẽ cố gắng trợ giúp và cũng sẽ cung cấp cho bạn kết quả ở định dạng thời gian (được hiển thị bên dưới).
Bạn có thể dễ dàng chuyển đổi nó sang định dạng số bằng cách nhấp vào tab Trang đầu và chọn Số làm định dạng trong nhóm Số.
Nếu bạn chỉ muốn trả về tổng số giờ đã trôi qua giữa hai thời điểm (không có bất kỳ phần phân số nào), hãy sử dụng công thức này:
= INT ((B2-A2) * 24)
Lưu ý: Công thức này chỉ hoạt động nếu hai giá trị thời gian ở cùng một ngày.Công thức này sẽ cho kết quả sai nếu ngày thay đổi (một trong các giá trị thời gian là một ngày khác và giá trị thứ hai là một ngày khác).Khi ngày thay đổi sau trong hướng dẫn này, hãy xem phần mà tôi giới thiệu công thức tính chênh lệch thời gian.
Tính chênh lệch múi giờ theo phút
Để tính chênh lệch thời gian theo phút, bạn cần nhân giá trị kết quả với tổng số phút trong ngày (tức là 1440 hoặc 24 * 60).
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn tính tổng số phút đã trôi qua giữa ngày bắt đầu và ngày kết thúc.
Đây là công thức có thể làm điều này:
= (B2-A2) * 24 * 60
Tính chênh lệch thời gian theo giây
Để tính chênh lệch thời gian theo giây, bạn cần nhân giá trị kết quả với tổng số giây trong một ngày (tức là 24 * 60 * 60 hoặc 86400).
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn đếm tổng số giây đã trôi qua giữa ngày bắt đầu và ngày kết thúc.
Đây là công thức có thể làm điều này:
=(B2-A2)*24*60*60
Tính chênh lệch múi giờ bằng hàm TEXT
Một cách dễ dàng khác để nhanh chóng nhận được chênh lệch múi giờ mà không phải lo lắng về việc thay đổi định dạng là sử dụng chức năng TEXT.
Hàm TEXT cho phép bạn chỉ định định dạng trong công thức.
= TEXT (end_date-start_date, format)
Tham số đầu tiên là phép tính bạn muốn thực hiện và tham số thứ hai là định dạng mà bạn muốn hiển thị kết quả của phép tính.
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn tính chênh lệch thời gian giữa hai thời điểm.
Dưới đây là một số công thức sẽ cung cấp cho bạn kết quả ở các định dạng khác nhau
Chỉ hiển thị giờ:
= TEXT (B2-A2, "hh")
Công thức trên sẽ chỉ cung cấp cho bạn kết quả của số giờ trôi qua giữa hai giá trị thời gian.Nếu kết quả của bạn là 9 giờ 30 phút, nó vẫn sẽ chỉ hiển thị 9.
Hiển thị tổng số phút
= TEXT (B2-A2, "[mm]")
Hiển thị tổng số giây
= TEXT (B2-A2, "[ss]")
Hiển thị giờ và phút
= TEXT (B2-A2, "[hh]: mm")
Hiển thị giờ, phút và giây
= TEXT (B2-A2, "hh: mm: ss")
Nếu bạn đang tự hỏi sự khác biệt giữa hh và [hh] trong định dạng (hoặc mm và [mm]), khi bạn sử dụng dấu ngoặc vuông, nó sẽ cung cấp cho bạn tổng số giờ giữa hai ngày, ngay cả khi giờ có giá trị Lớn hơn 24.Vì vậy, nếu bạn đang trừ hai giá trị ngày khác nhau hơn 24 giờ, việc sử dụng [hh] sẽ cung cấp cho bạn tổng số giờ, trong khi hh sẽ chỉ cung cấp cho bạn số giờ đã trôi qua vào ngày kết thúc.
Nhận chênh lệch múi giờ trong một đơn vị (giờ / phút), bỏ qua các đơn vị khác
Nếu bạn chỉ muốn tính toán chênh lệch thời gian giữa hai giá trị thời gian theo giờ hoặc phút hoặc giây, bạn có thể sử dụng các hàm HOUR, MINUTE hoặc SECOND chuyên dụng.
Mỗi hàm này nhận một tham số, giá trị thời gian và trả về đơn vị thời gian được chỉ định.
Giả sử bạn có một tập dữ liệu như hình dưới đây và bạn muốn tính tổng số giờ và giây đã trôi qua giữa hai tập dữ liệu đó.
Đây là công thức để làm điều này:
Tính số giờ trôi qua giữa hai thời điểm
= GIỜ (B2-A2)
Tính số phút dựa trên kết quả giá trị thời gian (không bao gồm số giờ đã hoàn thành)
= PHÚT (B2-A2)
Tính số giây từ kết quả giá trị thời gian (không bao gồm giờ và phút đã hoàn thành)
= SECOND (B2-A2)
Một số điều cần biết khi sử dụng các công thức HOURS, MINUTE và SECOND sau:
- Chênh lệch giữa thời gian kết thúc và thời gian bắt đầu không được âm (trường hợp này thường xảy ra khi ngày thay đổi).Trong trường hợp này, các công thức này sẽ trả về lỗi #NUM!Sai
- Các công thức này chỉ sử dụng phần thời gian (và bỏ qua phần ngày) của giá trị thời gian kết quả.Vì vậy, nếu sự khác biệt giữa thời gian kết thúc và thời gian bắt đầu là 2 ngày, 10 giờ, 32 phút và 44 giây, công thức HOUR sẽ cho ra 10, công thức MINUTE sẽ cho 32 và công thức SECOND sẽ cho 44
Tính thời gian đã trôi qua bây giờ (từ thời điểm bắt đầu)
Nếu bạn muốn tính tổng thời gian đã trôi qua giữa thời gian bắt đầu và thời gian hiện tại, bạn có thể sử dụng công thức NOW thay vì thời gian kết thúc.
Hàm NOW trả về ngày và giờ hiện tại trong ô mà nó được sử dụng.Nó là một trong những hàm không chấp nhận bất kỳ đối số đầu vào nào.
Vì vậy, nếu bạn muốn tính tổng thời gian đã trôi qua giữa thời gian bắt đầu và thời gian hiện tại, bạn có thể sử dụng công thức sau:
= NOW () - thời gian bắt đầu
Dưới đây là một ví dụ mà tôi có thời gian bắt đầu trong cột A và thời gian cho đến nay trong cột B.
Nếu chênh lệch thời gian giữa ngày và giờ bắt đầu và thời gian hiện tại hơn 24 giờ, bạn có thể định dạng kết quả để hiển thị phần ngày và giờ.
Bạn có thể thực hiện việc này bằng công thức TEXT sau:
= TEXT (NOW () - A2, "dd hh: ss: mm")
Bạn cũng có thể đạt được điều tương tự bằng cách thay đổi định dạng tùy chỉnh của ô (như được mô tả trước đó trong hướng dẫn này) để nó hiển thị phần ngày và giờ.
Nếu thời gian bắt đầu của bạn chỉ có phần thời gian, Excel sẽ coi nó là ngày 1990 tháng 1 năm 1.
Trong trường hợp này, nếu bạn sử dụng hàm NOW để tính thời gian đã trôi qua cho đến bây giờ, nó sẽ cho bạn kết quả sai (vì giá trị kết quả cũng sẽ chứa tổng số ngày đã trôi qua kể từ ngày 1990 tháng 1 năm 1).
Trong trường hợp này, bạn có thể sử dụng công thức sau:
= NOW () - INT (NOW ()) - A2
Công thức trên sử dụng hàm INT để loại bỏ phần ngày khỏi giá trị được trả về bởi hàm now, sau đó được sử dụng để tính chênh lệch thời gian.
Lưu ý rằng NOW là một hàm dễ thay đổi, cập nhật khi trang tính thay đổi, nhưng không cập nhật theo thời gian thực
Tính thời gian khi ngày thay đổi (tính toán và hiển thị thời gian âm trong Excel)
Các phương pháp được mô tả cho đến nay hoạt động tốt nếu thời gian kết thúc của bạn muộn hơn thời gian bắt đầu.
Tuy nhiên, sự cố xảy ra khi thời gian kết thúc của bạn thấp hơn thời gian bắt đầu.Điều này thường xảy ra khi bạn điền vào một bảng chấm công và bạn chỉ nhập thời gian chứ không phải toàn bộ ngày và giờ.
Trong trường hợp này, nếu bạn làm ca đêm và ngày thay đổi, thời gian kết thúc của bạn có thể sớm hơn thời gian bắt đầu của bạn.
Ví dụ: nếu bạn bắt đầu công việc lúc 6 giờ chiều và kết thúc công việc và tạm dừng lúc 00 giờ sáng.
Nếu bạn chỉ sử dụng các giá trị thời gian, thì trừ thời gian bắt đầu cho thời gian kết thúc sẽ cho giá trị âm là 9 giờ (9 - 18).
Excel không thể xử lý các giá trị thời gian âm (và, đối với vấn đề đó, con người, trừ khi bạn có thể du hành thời gian)
Trong trường hợp này, bạn cần một cách để xác định rằng ngày đã thay đổi, và nên tính toán cho phù hợp.
Rất may, có một cách giải quyết rất đơn giản.
Giả sử bạn có một tập dữ liệu như bên dưới, trong đó tôi có thời gian bắt đầu và thời gian kết thúc.
Như bạn sẽ nhận thấy, đôi khi thời gian bắt đầu là vào buổi tối và thời gian kết thúc là vào buổi sáng (cho thấy rằng đó là một ca làm việc qua đêm và ngày đã thay đổi).
Nếu tôi sử dụng công thức bên dưới để tính chênh lệch thời gian, nó sẽ hiển thị dấu thăng (được đánh dấu bằng màu vàng trong hình ảnh bên dưới) trong các ô mà kết quả là giá trị âm.
= B2-A2
Đây là một công thức IF, nếu giá trị chênh lệch thời gian là âm, nếu nó là âm, nó sẽ trả về kết quả chính xác
=IF((B2-A2)<0,1-(A2-B2),(B2-A2))
Mặc dù điều này hoạt động tốt trong hầu hết các trường hợp, nhưng vẫn chưa đủ nếu thời gian bắt đầu và kết thúc cách nhau hơn 24 giờ.Ví dụ: ai đó đăng nhập lúc 1:9 sáng ngày 00 và đăng xuất lúc 2:11 sáng ngày thứ 00.
Vì đây là hơn 24 giờ, không có cách nào để biết liệu người đó rời khỏi sau 2 giờ hay 26 giờ.
Mặc dù cách tốt nhất để khắc phục điều này là đảm bảo mục nhập bao gồm ngày và giờ, nếu đó chỉ là thời gian bạn đang làm việc với thì công thức trên sẽ giải quyết được hầu hết vấn đề (xem xét rằng không có khả năng ai sẽ làm việc nhiều hơn 24 tiếng)
Cộng trừ thời gian trong Excel
Cho đến nay, chúng ta đã thấy các ví dụ về thời gian bắt đầu và kết thúc, chúng ta cần tìm chênh lệch múi giờ.
Excel cũng cho phép bạn dễ dàng thêm hoặc trừ các giá trị thời gian cố định khỏi các giá trị ngày và giờ hiện có.
Ví dụ: giả sử bạn có một danh sách các tác vụ được xếp hàng đợi, trong đó mỗi tác vụ mất một thời gian cụ thể và bạn muốn biết khi nào mỗi tác vụ kết thúc.
Trong trường hợp này, bạn có thể dễ dàng thêm thời gian của mỗi nhiệm vụ vào thời gian bắt đầu để xem khi nào nhiệm vụ dự kiến sẽ được hoàn thành.
Vì Excel lưu trữ các giá trị ngày và giờ dưới dạng số, bạn phải đảm bảo rằng thời gian bạn đang cố gắng thêm ở định dạng mà Excel đã tuân theo.
Ví dụ: nếu bạn thêm 1 vào một ngày trong Excel, nó sẽ cung cấp cho bạn ngày tiếp theo.Điều này là do 1 đại diện cho một ngày đầy đủ (tương đương với 24 giờ) trong Excel.
Vì vậy, nếu bạn muốn thêm 1 giờ vào giá trị thời gian hiện có, bạn không thể tiếp tục và chỉ cần thêm 1 giờ vào giá trị đó.Bạn phải đảm bảo chuyển đổi giá trị giờ thành một phần thập phân đại diện cho một giờ.Tương tự với việc thêm phút và giây.
Sử dụng chức năng thời gian
Các hàm thời gian trong Excel lấy các giá trị giờ, phút và giây và chuyển chúng thành một số thập phân biểu thị thời gian.
Ví dụ: nếu tôi muốn thêm 4 giờ vào thời gian hiện tại, tôi có thể sử dụng công thức sau:
= thời gian bắt đầu + TIME (4,0,0)
Điều này rất hữu ích nếu bạn biết giờ, phút và giây để thêm vào thời gian hiện có và có thể chỉ cần sử dụng chức năng TIME mà không cần lo lắng về việc chuyển đổi thời gian thành giá trị thập phân một cách chính xác.
Ngoài ra, hãy lưu ý rằng hàm TIME chỉ xem xét phần nguyên của các giá trị giờ, phút và giây mà bạn nhập.Ví dụ: nếu tôi sử dụng 5.5 giờ trong hàm TIME, nó chỉ thêm 5 giờ và bỏ qua phần phân số.
Cũng lưu ý rằng hàm TIME chỉ có thể thêm các giá trị nhỏ hơn 24 giờ.Điều này sẽ cung cấp cho bạn một kết quả không chính xác nếu giá trị giờ của bạn vượt quá 24.
Tương tự đối với phần phút và giây, hàm sẽ chỉ xem xét các giá trị nhỏ hơn 60 phút và 60 giây
Giống như tôi đã sử dụng hàm TIME để thêm thời gian, bạn cũng có thể trừ thời gian.Chỉ cần thay đổi dấu + trong công thức trên thành dấu trừ
Sử dụng số học cơ bản
Trong khi chức năng thời gian rất dễ sử dụng, nó có một số hạn chế (như đã đề cập ở trên).
Nếu bạn muốn kiểm soát nhiều hơn, bạn có thể sử dụng phương pháp số học mà tôi sẽ trình bày ở đây.
Khái niệm này rất đơn giản - chuyển đổi giá trị thời gian thành giá trị thập phân đại diện cho một phần của ngày, sau đó bạn có thể thêm vào bất kỳ giá trị thời gian nào trong Excel.
Ví dụ: nếu bạn muốn thêm 24 giờ vào một giá trị thời gian hiện có, bạn có thể sử dụng công thức sau:
= thời gian bắt đầu + 24/24
Nó chỉ có nghĩa là tôi đang thêm một ngày vào giá trị thời gian hiện có.
Bây giờ lấy cùng một khái niệm, giả sử bạn muốn thêm 30 giờ vào giá trị thời gian, bạn có thể sử dụng công thức sau:
= thời gian bắt đầu + 30/24
Công thức trên cũng làm điều tương tự, trong đó phần nguyên của (30/24) đại diện cho tổng số ngày của thời gian bạn muốn thêm và phần phân số đại diện cho giờ / phút / giây
Tương tự như vậy, nếu bạn muốn thêm một số phút cụ thể vào giá trị thời gian, bạn có thể sử dụng công thức sau:
=Thời gian bắt đầu + (Thêm phút) / 24 * 60
Nếu bạn có giây để thêm, bạn có thể sử dụng công thức này:
= thời gian bắt đầu + (phút bổ sung) / 24 * 60 * 60
Mặc dù phương pháp này không đơn giản như sử dụng hàm thời gian, nhưng tôi thấy nó tốt hơn nhiều vì nó hoạt động trong mọi trường hợp và tuân theo các khái niệm giống nhau.Không giống như chức năng thời gian, bạn không phải lo lắng về việc thêm thời gian ít hơn hoặc lớn hơn 24 giờ
Bạn cũng có thể làm theo cùng một khái niệm trong khi trừ thời gian.Chỉ cần thay đổi + thành dấu âm trong công thức trên
Cách tính tổng thời gian trong Excel
Đôi khi bạn có thể muốn nhanh chóng thêm tất cả các giá trị thời gian trong Excel.Thêm nhiều giá trị thời gian trong Excel thật dễ dàng (chỉ cần một công thức SUM đơn giản)
Tuy nhiên, khi bạn thêm thời gian trong Excel, có một số điều bạn cần biết, đặc biệt là định dạng ô sẽ hiển thị cho bạn kết quả.
Hãy xem một ví dụ.
Dưới đây, tôi có danh sách các nhiệm vụ trong cột B và thời gian thực hiện của mỗi nhiệm vụ, và tôi muốn thêm nhanh các thời gian đó và biết tổng thời gian của tất cả các tác vụ này.
Trong ô B9, tôi đã sử dụng công thức SUM đơn giản để tính tổng thời gian tất cả các tác vụ này sẽ mất và nó cho tôi giá trị là 18:30 (có nghĩa là phải mất 18 giờ 20 phút để hoàn thành tất cả các tác vụ này)
Càng xa càng tốt!
Cách tính tổng trên 24 giờ trong Excel
Bây giờ, hãy xem điều gì sẽ xảy ra khi tôi thay đổi thời gian hoàn thành nhiệm vụ 2 từ 1 giờ thành 10 giờ.
Kết quả bây giờ hiển thị 03:20, nghĩa là sẽ mất 3 giờ 20 phút để hoàn thành tất cả các tác vụ này.
Điều này không chính xác (rõ ràng)
Vấn đề ở đây không phải là Excel bị lỗi.Vấn đề ở đây là các ô được định dạng để chỉ hiển thị phần thời gian của giá trị kết quả.
Và vì giá trị kết quả ở đây vượt quá 24 giờ, Excel quyết định chuyển đổi phần 24 giờ thành một ngày, xóa nó khỏi giá trị hiển thị cho người dùng và chỉ hiển thị giờ, phút và giây còn lại.
May mắn thay, điều này là dễ dàng để sửa chữa.
Tất cả những gì bạn cần làm là thay đổi định dạng ô để buộc nó hiển thị giờ ngay cả khi đã hơn 24 giờ.
Dưới đây là một số định dạng bạn có thể sử dụng:
| định dạng | kết quả mong đợi |
| [h]: mm | 28:30 |
| [m]: ss | 1710:00 |
| d “D” hh: mm | Ngày 04 30:XNUMX |
| d "D" hh "Tối thiểu" ss "Giây" | 1 ngày 04 phút 00 giây |
| d “Ngày” hh “Phút” ss “Giây” | 1 ngày 04 phút 00 giây |
Bạn có thể thay đổi định dạng bằng cách đi tới hộp thoại Định dạng Ô và áp dụng định dạng tùy chỉnh hoặc sử dụng hàm TEXT và sử dụng bất kỳ định dạng nào ở trên trong chính công thức
Bạn có thể sử dụng công thức TEXT sau để hiển thị thời gian ngay cả khi nó vượt quá 24 giờ:
= TEXT (SUM (B2: B7), "[h]: mm: ss")
Hoặc nếu bạn muốn chuyển đổi số giờ trên 24 giờ thành ngày, hãy sử dụng công thức sau:
= TEXT (SUM (B2: B7), "dd hh: mm: ss")
Kết quả hiển thị hàm băm (###) thay vì ngày / giờ (nguyên nhân + sửa chữa)
Trong một số trường hợp, bạn có thể thấy rằng thay vì hiển thị giá trị thời gian, Excel sẽ hiển thị dấu thăng trong ô.
Dưới đây là một số nguyên nhân có thể xảy ra và cách khắc phục:
Các cột không đủ rộng
Khi một ô không có đủ không gian để hiển thị ngày đầy đủ, ô đó có thể hiển thị dấu thăng.
Nó có một sửa chữa dễ dàng - thay đổi chiều rộng cột và làm cho nó rộng hơn.
giá trị ngày âm
Giá trị ngày hoặc giờ trong Excel không được âm.Nếu bạn đang tính toán chênh lệch thời gian và kết quả là âm, Excel sẽ hiển thị dấu thăng.
Sửa phương pháp thay đổi công thức để đưa ra kết quả chính xác.Ví dụ: nếu bạn đang tính chênh lệch thời gian giữa hai thời điểm và ngày thay đổi, bạn sẽ cần điều chỉnh công thức để tính đến điều đó.
Trong các trường hợp khác, bạn có thể sử dụng chức năng ABS để chuyển giá trị thời gian âm sang số dương để hiển thị phù hợp.Ngoài ra, bạn có thể sử dụng công thức IF để kiểm tra xem kết quả có âm hay không và trả về giá trị có ý nghĩa hơn.
Trong hướng dẫn này, tôi đề cập đến các chủ đề về tính toán thời gian trong Excel (nơi bạn có thể tính toán chênh lệch thời gian, cộng và trừ thời gian, hiển thị thời gian ở các định dạng khác nhau và tính tổng các giá trị thời gian)
Tôi hy vọng bạn thấy hướng dẫn này hữu ích.
Câu hỏi thường gặp về tính cộng và trừ thời gian trong Excel
Trong Excel, bạn có thể sử dụng hàm thời gian để thêm thời gian.Lấy phút làm ví dụ, nếu ô B2 có thời gian “2:30” rồi nhập “=B2+time(2)” ở ô C0,30,0 thì kết quả sẽ là “2:3” hiển thị ở ô B00. CXNUMX.
Để giảm thời gian trong Excel, bạn có thể sử dụng hàm thời gian.Ví dụ: nếu bạn có thời gian là '3:30' ở B2, sau đó nhập '=B2-time(2)' vào C0,30,0 thì kết quả sẽ là '2:3' ở C00.
Để tính khoảng thời gian trong Excel, chỉ cần trừ thời gian bắt đầu khỏi thời gian kết thúc.Ví dụ: nếu có thời gian bắt đầu là '2:1' trong B30 và thời gian kết thúc là '2:3' trong C00, hãy nhập '=C2-B2' trong D2.
Hệ thống ngày giờ mặc định của Excel dựa trên đồng hồ 24 giờ, nếu kết quả tính toán thời gian vượt quá 24 giờ thì Excel sẽ chuyển nó sang định dạng ngày tháng.Để giải quyết vấn đề này, bạn có thể đặt định dạng của các ô có trong phép tính thành "[h]:mm".
Đảm bảo rằng sau khi cộng hoặc trừ thời gian, định dạng số của trường kết quả vẫn ở định dạng thời gian.Thông thường, bạn có thể chọn ô, sau đó chọn "Định dạng số" từ thanh công cụ, sau đó chọn "Thời gian" để đặt thành định dạng thời gian.
Sau đây là một mẫu đơn:
| lần một | lần hai | thời gian chênh lệch |
|---|---|---|
| 2:30 | 3:00 | 0:30 |
| 10:00 | 8:00 | 2:00 |
