Cách tính thời gian theo ngày trong Excel
|
Ngày và giờ trong Excel được lưu trữ dưới dạng số ngày, là số ngày kể từ ngày 1 tháng 1 năm 1900, với giá trị sau dấu thập phân biểu thị thời gian trong ngày Show
Để tính số giờ giữa hai ngày, chúng ta chỉ cần trừ hai giá trị và nhân với 24. Điều này trả về một giá trị thập phân mặc dù. Nếu chúng ta muốn thời gian tính bằng giờ và phút (hoặc giờ phút và giây), mọi thứ sẽ phức tạp hơn một chút  Ảnh chụp màn hình hiển thị các lựa chọn thay thế khác nhau để hiển thị số giờ giữa ngày/giờ trong ô B3 và ngày bắt đầu trong B2 Hàng đầu tiên hiển thị kết quả nhân chênh lệch ngày với 24. Điều này hiển thị kết quả chính xác nếu được định dạng dưới dạng số thập phân, nhưng nếu được định dạng theo thời gian thì nó hiển thị giá trị sai Microsoft khuyến nghị sử dụng chức năng Văn bản, với chuỗi định dạng “h. mm”. Lưu ý rằng chuỗi định dạng sẽ ghi đè định dạng ô, vì vậy văn bản trả về sẽ hiển thị giống nhau bất kể định dạng số ô. Điều này sẽ hoạt động chính xác đối với chênh lệch múi giờ lên đến 24 giờ, nhưng màn hình sẽ đặt lại về 0 sau mỗi 24 giờ, do đó chênh lệch múi giờ 36 giờ ở trên hiển thị là 12 giờ. Điều này có thể được thay đổi để hiển thị ngày, giờ và phút với chuỗi định dạng “d. h. mm” Một phương pháp khác là để chênh lệch múi giờ dưới dạng số ngày và định dạng hiển thị dưới dạng giờ. Định dạng thời gian mặc định có vấn đề tương tự như định dạng thời gian “h. mm”, do đó 36 giờ hiển thị là 12, nhưng có thể hiển thị số giờ chính xác
Cập nhật 1 ngày 18 tháng 10. Như Doug Glancy đã lưu ý trong các nhận xét, [h]. mm. định dạng ss có sẵn từ hộp thoại định dạng. Trong phiên bản của tôi (Office 365), nó nằm ở cuối định dạng thời gian, gần cuối danh sách định dạng tùy chỉnh Trang web này sử dụng cookie để chúng tôi có thể cung cấp cho bạn trải nghiệm người dùng tốt nhất có thể. Thông tin cookie được lưu trữ trong trình duyệt của bạn và thực hiện các chức năng như nhận ra bạn khi bạn quay lại trang web của chúng tôi và giúp nhóm của chúng tôi hiểu phần nào của trang web mà bạn thấy thú vị và hữu ích nhất Bạn có thể điều chỉnh tất cả cài đặt cookie của mình bằng cách điều hướng các tab ở phía bên trái Hầu hết công việc trong công thức này được thực hiện bởi hàm TEXT, hàm này áp dụng định dạng số tùy chỉnh cho giờ và phút cho một giá trị được tạo bằng cách lấy ngày kết thúc trừ đi ngày bắt đầu Đây là một ví dụ về nhúng văn bản vào định dạng số tùy chỉnh và văn bản này phải được bao quanh bởi một cặp dấu ngoặc kép bổ sung. Nếu không có thêm dấu ngoặc kép, định dạng văn bản tùy chỉnh sẽ trông như thế này Giá trị của ngày được tính bằng hàm INT, hàm này chỉ trả về phần nguyên của ngày kết thúc trừ đi ngày bắt đầu Ghi chú. Mặc dù bạn có thể sử dụng "d" ở định dạng số tùy chỉnh cho ngày, nhưng giá trị sẽ đặt lại về 0 khi ngày lớn hơn 31 Bao gồm giâyĐể bao gồm giây, bạn có thể mở rộng định dạng số tùy chỉnh như thế này Tổng số ngày, giờ và phút giữa các ngàyĐể có được tổng số ngày, giờ và phút giữa một tập hợp ngày bắt đầu và ngày kết thúc, bạn có thể điều chỉnh công thức bằng TÓM TẮT như thế này trong đó "kết thúc" đại diện cho phạm vi ngày kết thúc và "bắt đầu" đại diện cho phạm vi ngày bắt đầu. Trong ví dụ hiển thị, D11 chứa công thức này Vì ngày và giờ được lưu trữ dưới dạng số ở phần cuối trong Excel nên 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ể thêm 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/thời gian đã cho Trong hướng dẫn này, tôi sẽ chỉ cho bạn một số cách để thực hiện phép tính sử dụng thời gian trong Excel (chẳng hạn như tính chênh lệch thời gian, cộng hoặc 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) Hướng dẫn này bao gồm
Excel xử lý ngày và giờ như thế nào?Như tôi đã đề cập, ngày và giờ được lưu dưới dạng số trong một ô trong Excel. Một số nguyên đại diện cho một ngày hoàn chỉnh và phần thập phân của một số sẽ đại diện cho một phần của ngày (có thể chuyển đổi thành giá trị giờ, phút và giây) Ví dụ: giá trị 1 đại diện cho ngày 01 tháng 1 năm 1900 trong Excel, đây là điểm bắt đầu mà từ đó Excel bắt đầu xem xét các ngày Vì vậy, 2 có nghĩa là ngày 02 tháng 1 năm 1990, 3 có nghĩa là ngày 03 tháng 1 năm 1900, v.v. và 44197 có nghĩa là ngày 01 tháng 1 năm 2021 Ghi chú. 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 1 tháng 1 năm 1900 và 1 trong Excel cho Mac có nghĩa là ngày 1 tháng 1 năm 1904 Nếu có bất kỳ chữ số nào sau dấu thập phân trong những số này, Excel sẽ coi những chữ số đó là một phần của ngày và nó có thể được chuyển đổi thành giờ, phút và giây Ví dụ: 44197. 5 có nghĩa là ngày 01 tháng 1 năm 2021 12. 00. 00 giờ chiều 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 sẽ làm việc với phần thập phân của một số Và Excel cung cấp cho bạn sự linh hoạt để chuyển đổi phần thập phân đó thành 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 thời gian khác nhau trong Excel Công thức tính chênh lệch thời gian giữa hai thời điểmTrong nhiều trường hợp, tất cả những gì bạn muốn làm là tìm ra tổng thời gian đã trôi qua giữa các giá trị hai thời gian (chẳng hạn như trong trường hợp bảng chấm công có Giờ vào và Giờ ra) Phương pháp bạn chọn sẽ phụ thuộc vào cách thời gian được đề cập trong một ô và định dạng bạn muốn kết quả Hãy xem xét một vài ví dụ Phép trừ đơn giản để tính chênh lệch thời gian trong ExcelVì thời gian được lưu trữ dưới dạng số trong Excel, 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 từ thời gian kết thúc End Time – Start Time Kết quả của phép trừ cũng sẽ là một giá trị thập phân biểu thị thời gian đã trôi qua giữa hai giá trị thời gian Dưới đây là một ví dụ mà tôi có thời gian bắt đầu và kết thúc và tôi đã tính chênh lệch thời gian bằng một phép trừ đơn giản Có khả năng kết quả của bạn được hiển thị ở định dạng thời gian (thay vì số thập phân hoặc giá trị giờ/phút). Trong ví dụ trên của chúng tôi, kết quả trong ô C2 hiển thị 09. 30 giờ sáng thay vì 9. 5 Điều đó hoàn toàn ổn vì Excel cố gắng sao chép định dạng từ cột liền kề Để chuyển đổi số này thành số thập phân, hãy thay đổi định dạng của các ô thành Chung (tùy chọn nằm trong tab Trang chủ trong nhóm Số) Khi bạ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ể chỉ hiển thị giá trị theo giờ hoặc chỉ theo phút hoặc kết hợp cả 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 FormatWhat it DoeshChỉ hiển thị số giờ đã trôi qua giữa hai ngày thánghHiển thị giờ ở dạng hai chữ số (chẳng hạn như 04 hoặc 12)hh. mmHiển thị giờ và phút đã trôi qua giữa hai ngày, chẳng hạn như 10. 20h. mm. ss Hiển thị giờ, phút và giây đã trôi qua giữa hai ngày, chẳng hạn như 10. 20. 36Và 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 bên dưới
Các bước trên sẽ thay đổi định dạng và hiển thị cho bạn giá trị dựa trên đị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 hiển thị giá trị. Vì vậy, tôi có thể chọn chỉ hiển thị giá trị giờ trong một ô, trong khi 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âyKhi bạn trừ các giá trị thời gian, Excel sẽ trả về một số thập phân biểu thị 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 thập phân của số sẽ đại diện cho phần lớn ngày đó 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 thời gian theo giờGiả sử bạn có tập dữ liệu như hình dưới đây và bạn muốn tính số giờ giữa các giá trị hai thời gian Dưới đây là công thức sẽ cung cấp cho bạn sự khác biệt về thời gian tính bằng 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 các giá trị hai thời gian Đôi khi, Excel cố gắng hữu ích và cũng sẽ cung cấp cho bạn kết quả ở định dạng thời gian (như hình bên dưới) Bạn có thể dễ dàng chuyển đổi định dạng này sang định dạng Số bằng cách nhấp vào tab Trang chủ và trong nhóm Số, chọn Số làm định dạng Nếu bạn chỉ muốn trả về tổng số giờ đã trôi qua giữa hai lần (không có phần thập phân), hãy sử dụng công thức dưới đây =INT((B2-A2)*24) Ghi chú. Công thức này chỉ hoạt động khi cả hai giá trị thời gian đều trong cùng một ngày. Nếu ngày thay đổi (trong đó một trong các giá trị thời gian là của một ngày khác và giá trị thứ hai của một ngày khác), công thức này sẽ cho kết quả sai. Hãy xem phần tôi đề cập đến công thức để tính chênh lệch múi giờ khi ngày thay đổi sau trong hướng dẫn này Tính chênh lệch thời gian tính bằng phútĐể tính chênh lệch thời gian tính bằng phút, bạn cần nhân giá trị thu được với tổng số phút trong một ngày (là 1440 hoặc 24*60) Giả sử bạn có tập dữ liệu như hình bên dưới 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 Dưới đây là công thức sẽ làm điều đó ________số 8Tính chênh lệch thời gian tính bằng giâyĐể tính chênh lệch thời gian tính bằng 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 (là hoặc 24*60*60 hoặc 86400) Giả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn tính tổng số giây đã trôi qua giữa ngày bắt đầu và ngày kết thúc Dưới đây là công thức sẽ làm điều đó =(B2-A2)*24*60*60 Tính chênh lệch múi giờ với hàm TEXTMộ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 hàm TEXT Hàm TEXT cho phép bạn chỉ định định dạng ngay trong công thức Đối số đầu tiên là phép tính bạn muốn thực hiện và đối 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ó tập dữ liệu như hình bên dưới 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ả với các định dạng khác nhau Chỉ hiển thị số giờ Công thức trên sẽ chỉ cung cấp cho bạn kết quả hiển thị số giờ trôi qua giữa các giá trị hai thời gian. Nếu kết quả của bạn là 9 giờ 30 phút, nó vẫn chỉ hiển thị cho bạn 9 Hiển thị tổng số phút Hiển thị tổng số giây Hiển thị Giờ và Phút Hiển thị Giờ, Phút và Giây =(B2-A2)*240 Nếu bạn đang thắc mắc về sự khác biệt giữa hh và [hh] ở định dạng (hoặc mm và [mm]), thì khi bạn sử dụng dấu ngoặc vuông, nó sẽ cho bạn tổng số giờ giữa hai ngày, ngay cả khi . Vì vậy, nếu bạn trừ hai giá trị ngày có chênh lệch lớn hơn 24 giờ, thì việc sử dụng [hh] sẽ cho bạn tổng số giờ và hh sẽ chỉ cho bạn số giờ đã trôi qua vào ngày của ngày kết thúc Nhận chênh lệch thời gian trong một đơn vị (giờ/phút) và bỏ qua những thứ khácNếu bạn muốn tính chênh lệch thời gian giữa hai giá trị thời gian chỉ bằng số giờ hoặc phút hoặc giây, thì bạn có thể sử dụng hàm HOUR, MINUTE hoặc SECOND chuyên dụng Mỗi hàm này nhận một đối số duy nhất, là giá trị thời gian và trả về đơn vị thời gian đã chỉ định Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn tính tổng số giờ phút và giây đã trôi qua giữa hai thời điểm này Dưới đây là các công thức để làm điều này Tính toán số giờ đã trôi qua giữa hai lần =(B2-A2)*241 Tính phút từ kết quả giá trị thời gian (không bao gồm số giờ đã hoàn thành) =(B2-A2)*242 Tính toán Giây từ kết quả giá trị thời gian (không bao gồm giờ và phút đã hoàn thành) =(B2-A2)*243 Một số điều bạn cần biết khi làm việc với các công thức GIỜ, PHÚT và GIÂY này
Tính toán thời gian đã trôi qua cho đến 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ì Công thức 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. Đây là một trong những hàm không 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 dưới đây =(B2-A2)*244 Dưới đây là một ví dụ trong đó tôi có thời gian bắt đầu ở cột A và thời gian đã trôi qua cho đến bây giờ ở cột B Nếu sự khác biệt về thời gian giữa ngày và giờ bắt đầu và thời gian hiện tại là hơn 24 giờ, thì bạn có thể định dạng kết quả để hiển thị ngày cũng như phần thời gian Bạn có thể làm điều đó bằng cách sử dụng công thức TEXT bên dưới =(B2-A2)*245 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ư đã trình bày trước đó trong hướng dẫn này) để ô hiển thị ngày cũng như phần thời gian Trong trường hợp thời gian bắt đầu của bạn chỉ có phần thời gian thì Excel sẽ coi đó là thời điểm ngày 1 tháng 1 năm 1990 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ẽ có tổng số ngày đã trôi qua kể từ ngày 1 tháng 1 năm 1990) Trong trường hợp như vậy, bạn có thể sử dụng công thức dưới đây =(B2-A2)*246 Công thức trên sử dụng hàm INT để loại bỏ phần ngày khỏi giá trị được hàm now trả về và sau đó giá trị này được sử dụng để tính chênh lệch múi giờ Lưu ý rằng NOW là một hàm dễ bay hơi cập nhật bất cứ khi nào có thay đổi trong trang tính, nhưng nó 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 đề cập 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 Nhưng vấn đề phát sinh 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 bảng chấm công mà bạn chỉ nhập thời gian chứ không nhập toàn bộ ngày và giờ Trong những trường hợp như vậy, nếu bạn đang làm việc theo ca một đêm và ngày thay đổi, có khả năng thời gian kết thúc của bạn sẽ sớm hơn thời gian bắt đầu của bạn Ví dụ, nếu bạn bắt đầu làm việc lúc 6. 00 giờ tối và hoàn thành công việc của bạn và hết giờ làm việc lúc 9 giờ. 00 giờ sáng Nếu bạn chỉ làm việc với các giá trị thời gian, thì việc trừ đi thời gian bắt đầu từ thời gian kết thúc sẽ cho bạn giá trị âm là 9 giờ (9 – 18) Và Excel không thể xử lý các giá trị thời gian âm (và đối với vấn đề đó, con người cũng vậy, trừ khi bạn có thể du hành thời gian) Trong những trường hợp như vậy, bạn cần một cách để biết rằng ngày đã thay đổi và việc tính toán sẽ được thực hiện tương ứng Rất may, có một sửa chữa thực sự dễ dàng cho việc này Giả sử bạn có tập dữ liệu như hì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 rằng đô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 (điều này cho thấy rằng đây là ca làm việc qua đêm và ngày đã thay đổi) Nếu tôi sử dụng công thức dưới đây để tính chênh lệch thời gian, nó sẽ hiển thị cho tôi các dấu băm trong các ô có kết quả là giá trị âm (được đánh dấu bằng màu vàng trong hình ảnh bên dưới) =(B2-A2)*247 Đây là một công thức IF cho dù giá trị chênh lệch thời gian có âm hay không và trong trường hợp nó âm thì nó sẽ trả về kết quả đúng =(B2-A2)*248 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 nó vẫn thiếu trong trường hợp thời gian bắt đầu và thời gian kết thúc cách nhau hơn 24 giờ. Ví dụ: ai đó đăng nhập lúc 9. 00 giờ sáng ngày 1 và đăng xuất lúc 11 giờ. 00 giờ sáng ngày 2 Vì đã hơn 24 giờ nên không có cách nào để biết người đó đã đăng xuất sau 2 giờ hay sau 26 giờ Mặc dù cách tốt nhất để giải quyết vấn đề này là đảm bảo rằng các mục bao gồm ngày cũng như thời gian, nhưng nếu đó chỉ là thời gian bạn đang làm việc, thì công thức trên sẽ giải quyết hầu hết các vấn đề ( Cộng/Trừ Thời gian trong ExcelCho đến nay, chúng ta đã thấy các ví dụ mà chúng ta có thời gian bắt đầu và kết thúc, và chúng ta cần tìm sự khác biệt về thời gian Excel cũng cho phép bạn dễ dàng cộng hoặc trừ một giá trị thời gian cố định khỏi 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 trong đó mỗi tác vụ có thời gian đã chỉ định và bạn muốn biết khi nào mỗi tác vụ sẽ kết thúc Trong trường hợp như vậy, bạn có thể dễ dàng thêm thời gian mà mỗi nhiệm vụ sẽ thực hiện vào thời gian bắt đầu để biết nhiệm vụ dự kiến sẽ hoàn thành vào thời gian nào Vì Excel lưu trữ các giá trị ngày và giờ dưới dạng số, nên bạn phải đảm bảo rằng thời gian bạn đang cố gắng thêm tuân theo đị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 cả ngày trong Excel (tương đương với 24 giờ) 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 vào giá trị đó. bạn phải đảm bảo rằng bạn chuyển đổi giá trị giờ đó thành phần thập phân đại diện cho một giờ. và tương tự với việc thêm phút và giây Sử dụng hàm TIMEHàm thời gian trong Excel lấy giá trị giờ, giá trị phút và giá trị giây rồi chuyển đổi thành số thập phân biểu thị thời gian này 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 bên dưới =(B2-A2)*249 Điều này hữu ích nếu bạn biết bạn muốn thêm bao nhiêu giờ, phút và giây vào thời gian hiện có và chỉ cần sử dụng hàm TIME mà không phải lo lắng về việc chuyển đổi thời gian chính xác thành giá trị thập phân Ngoài ra, lưu ý rằng hàm TIME sẽ chỉ xem xét phần nguyên của giá trị giờ, phút và giây mà bạn nhập vào. Ví dụ: nếu tôi sử dụng 5. 5 giờ trong hàm TIME nó sẽ chỉ thêm năm giờ và bỏ qua phần thập phân Ngoài ra, lưu ý rằng hàm TIME chỉ có thể thêm các giá trị dưới 24 giờ. Nếu giá trị giờ của bạn lớn hơn 24, điều này sẽ cho bạn kết quả không chính xác Và điều tương tự cũng xảy ra với phần phút và giây khi 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 đã cộng thời gian bằng hàm TIME, bạn cũng có thể trừ thời gian. Chỉ cần thay dấu + thành dấu âm trong các công thức trên Sử dụng số học cơ bảnKhi chức năng thời gian dễ sử dụng và thuận tiện, nó sẽ có một số hạn chế (như đã trình bày ở 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ẽ đề cập ở đâ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 phần của ngày, sau đó bạn có thể thêm nó 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 giá trị thời gian hiện có, bạn có thể sử dụng công thức bên dưới =INT((B2-A2)*24)0 Điều này 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ờ, hãy sử dụng cùng một khái niệm về phía trước, giả sử bạn muốn thêm 30 giờ vào một giá trị thời gian, bạn có thể sử dụng công thức bên dưới =INT((B2-A2)*24)1 Công thức trên cũng làm điều tương tự, trong đó phần nguyên của (30/24) sẽ biểu thị tổng số ngày trong thời gian bạn muốn thêm và phần thập phân sẽ biểu thị giờ/phút/giây Tương tự, nếu bạn có một số phút cụ thể mà bạn muốn thêm vào một giá trị thời gian thì bạn có thể sử dụng công thức dưới đây =INT((B2-A2)*24)2 Và nếu bạn có số giây muốn thêm, thì bạn có thể sử dụng công thức bên dưới =INT((B2-A2)*24)3 Mặc dù phương pháp này không dễ bằng cách sử dụng hàm thời gian, nhưng tôi thấy nó tốt hơn rất nhiều vì nó hoạt động trong mọi tình huống và tuân theo cùng một khái niệm. không giống như chức năng thời gian, bạn không phải lo lắng về thời gian bạn muốn thêm ít hơn 24 giờ hay nhiều hơn 24 giờ Bạn có thể làm theo cùng một khái niệm trong khi trừ thời gian. Chỉ cần thay dấu + thành dấu âm trong các công thức trên Cách TỔNG thời gian trong ExcelĐôi khi bạn có thể muốn cộng nhanh tất cả các giá trị thời gian trong Excel. Việc thêm nhiều giá trị thời gian trong Excel khá đơn giản (tất cả chỉ cần một công thức SUM đơn giản) Nhưng có một số điều bạn cần biết khi thêm thời gian trong Excel, cụ thể 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ó một danh sách các nhiệm vụ cùng với thời gian mỗi nhiệm vụ sẽ thực hiện trong cột B và tôi muốn nhanh chóng thêm các thời gian này và biết tổng thời gian sẽ mất cho tất cả các nhiệm vụ này Trong ô B9, tôi đã sử dụng một công thức SUM đơn giản để tính tổng thời gian mà tất cả các tác vụ này sẽ thực hiện và nó mang lại cho tôi giá trị là 18. 30 (có nghĩa là sẽ mất 18 giờ 20 phút để hoàn thành tất cả các nhiệm vụ này) Tất cả đều tốt cho đến nay Cách tính TỔNG hơn 24 giờ trong ExcelBâ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ờ cho biết 03. 20, có nghĩa là sẽ mất 3 giờ 20 phút để hoàn thành tất cả các nhiệm vụ này Điều này là không chính xác (rõ ràng) Vấn đề ở đây không phải là Excel rối tung lên. Vấn đề ở đây là ô được định dạng theo cách nó sẽ chỉ hiển thị cho bạn phần thời gian của giá trị kết quả Và vì giá trị kết quả ở đây là hơn 24 giờ, Excel đã quyết định chuyển đổi phần 24 giờ thành một ngày, loại bỏ nó khỏi giá trị được hiển thị cho người dùng và chỉ hiển thị giờ, phút và giây còn lại Điều này, rất may, có một sửa chữa dễ dàng 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 vượt quá 24 giờ Dưới đây là một số định dạng mà bạn có thể sử dụng Định dạng kết quả mong đợi[h]. mm28. 30[phút]. ss1710. 00d “D” hh. mm1 D 04. 30d “D” hh “Tối thiểu” ss “Giây”1 D 04 Phút 00 Giây “Ngày” hh “Phút” ss “Giây”1 Ngày 04 Phút 00 GiâyBạn có thể thay đổi định dạng bằng cách vào 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 bên dưới để hiển thị thời gian, ngay cả khi đã hơn 24 giờ =INT((B2-A2)*24)4 hoặc công thức dưới đây nếu bạn muốn chuyển đổi số giờ vượt quá 24 giờ thành ngày =INT((B2-A2)*24)5 Kết quả Hiển thị Hash (###) Thay vì Ngày/Giờ (Lý do + Khắc phục)Trong một số trường hợp, bạn có thể thấy rằng thay vì hiển thị cho bạn giá trị thời gian, Excel lại hiển thị các ký hiệu băm trong ô Dưới đây là một số nguyên nhân có thể xảy ra và cách khắc phục chúng Cột không đủ rộngKhi một ô không có đủ không gian để hiển thị ngày đầy đủ, ô đó có thể hiển thị các ký hiệu băm Nó có một cách khắc phục dễ dàng – thay đổi độ rộng của cột và làm cho nó rộng hơn Giá trị ngày âmGiá trị ngày hoặc giờ không được âm trong Excel. trong trường hợp bạn đang tính chênh lệch thời gian và nó trở thành số âm, Excel sẽ hiển thị cho bạn các ký hiệu băm Cách khắc phục để thay đổi công thức cho bạn kết quả đúng. Ví dụ: nếu bạn đang tính chênh lệch múi giờ giữa hai thời điểm và ngày thay đổi, bạn cần điều chỉnh công thức để tính đến chênh lệch đó Trong các trường hợp khác, bạn có thể sử dụng chức năng ABS để chuyển đổi giá trị thời gian âm thành số dương để nó được hiển thị chính xác. Ngoài ra, bạn cũng có thể sử dụng công thức IF để kiểm tra xem kết quả có phải là giá trị âm hay không và trả về giá trị nào đó có ý nghĩa hơn Trong hướng dẫn này, tôi đã đề cập đến các chủ đề về cách tính thời gian trong Excel (nơi bạn có thể tính chênh lệch thời gian, cộng hoặc 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) Có công thức tính thời gian trong Excel không?Một kỹ thuật đơn giản khác để tính khoảng thời gian giữa hai lần trong Excel là sử dụng hàm TEXT. Tính giờ giữa hai thời điểm. =TEXT(B2-A2, "h") Trả về giờ và phút giữa 2 lần. =TEXT(B2-A2,"h. mm") Trả về giờ, phút và giây giữa 2 lần. =TEXT(B2-A2, "h. mm. ss")
Công thức tính thời gian là gì?Công thức thời gian chung cho bất kỳ nhiệm vụ nào được đưa ra là [ Thời gian = Khoảng cách ÷ Tốc độ ]. Đơn vị thời gian SI là giây (s). |
