Nhập thời gian tiêu cực (Microsoft Excel)

Guido cần nhập thời gian tiêu cực vào tế bào. Nếu anh ta nhập thời gian tích cực (2:00), Excel sẽ nhận ra điều đó tốt. Nếu anh ta cố gắng nhập -2: 00, Excel sẽ cho anh ta biết rằng có lỗi trong công thức của anh ta. Guido thắc mắc về cách dễ nhất để nhập giá trị thời gian âm vào một ô và để Excel nhận ra nó là giá trị thời gian.

Trước khi đưa ra một vài câu trả lời khả thi cho câu hỏi, bạn nên hiểu cách Excel xem ngày và giờ. Bên trong, ngày và giờ được lưu trữ dưới dạng số sê-ri, không phải là số thực tế. Giả sử bạn nhập một ngày vào ô B4, chẳng hạn như “Ngày 17 tháng 3 năm 2020” (không có dấu ngoặc kép. Excel hiểu rằng bạn đang đưa ra một ngày và nó chuyển đổi nó thành một số sê-ri. Tuy nhiên, bạn không thể biết đó là số sê-ri , vì Excel sẽ hiển thị hữu ích cho bạn ngày được biểu thị bằng số sê-ri. (Xem Hình 1.)

Hình 1. Nhập ngày vào Excel.

Lưu ý rằng ngày hiển thị trong ô là 17-Mar-20 và trong thanh Công thức là 3/17/2020. (Chính xác cách Excel hiển thị ngày phụ thuộc vào cách bạn nhập ngày đó và cài đặt khu vực của bạn nằm trong Windows.) Ngày hiển thị trong ô (17-Mar-20) là kết quả của cách ô được định dạng. Khi bạn nhập ngày vào ô và vì ô trước đó có định dạng “Chung”, Excel đã sửa đổi định dạng của ô để hiển thị ngày phù hợp nhất.

Vì ngày được hiển thị trên thanh Công thức (3/17/2020), bạn có thể nghĩ rằng đây là t theo cách mà Excel lưu trữ ngày tháng trong nội bộ. Không phải vậy; bạn có thể thấy điều này bằng cách nhập công thức vào một ô liền kề (= B4) và sau đó định dạng ô đó là “Chung”. (Xem Hình 2.)

Hình 2. Xem cách Excel lưu trữ ngày tháng.

Thanh Công thức hiển thị công thức (= B4), nhưng ô hiển thị giá trị 43907. Nếu bạn định dạng ngày trong ô B4 là chung, nó cũng sẽ hiển thị cùng một giá trị. Đây là số sê-ri; đó là cách Excel lưu trữ ngày tháng trong nội bộ.

Nhưng số sê-ri có nghĩa là gì? Nó là “số đếm” của số ngày kể từ ngày gốc. Thông thường, ngày gốc này là ngày 1 tháng 1 năm 1900, với ngày đó là ngày 1. Ngày 2 tháng 1 năm 1900, sẽ là ngày 2, v.v. Như vậy, 43907 là ngày thứ 43,907 bắt đầu từ ngày 1 tháng 1 năm 1900.

Tuy nhiên, có thể ngày cơ sở cho sổ làm việc sẽ khác. Excel, cho mục đích lịch sử, hiểu hai ngày cơ sở có thể có. Hệ thống còn lại được gọi là hệ thống ngày tháng năm 1904. Nó bắt đầu đếm với ngày 1 tháng 1 năm 1904. Không giống như hệ thống ngày năm 1900, hệ thống ngày năm 1904 bắt đầu đếm bằng 0. Vì vậy, ngày 1 tháng 1 năm 1904, có số sê-ri là 0, ngày 2 tháng 1 năm 1904, có số sê-ri là 1, vv

Bạn có thể xem hệ thống ngày tháng nào được sử dụng trong sổ làm việc bằng cách làm theo các bước sau:

(Xem Hình 3.)

Nếu hộp kiểm được chọn ở bước 4, thì sổ làm việc của bạn sử dụng hệ thống ngày 1904. Nếu nó không được chọn, thì bạn đang sử dụng hệ thống ngày 1900.

Điều này có liên quan gì đến câu hỏi ban đầu của Guido về việc bước vào thời điểm tiêu cực? Rất nhiều, thực tế. Bạn thấy đấy, giá trị thời gian, đối với Excel, không hơn gì một phần của ngày. Lưu ý các ví dụ được cung cấp cho đến nay liên quan đến ngày tháng được lưu trữ dưới dạng toàn bộ số sê-ri, trong trường hợp này là 43907.

Nếu bạn nhập ngày có thời gian vào ô, chẳng hạn như “3/17/20 6:00 chiều”

(Một lần nữa không có dấu ngoặc kép, sau đó nó được lưu trữ bên trong dưới dạng một phần của số sê-ri ở bên phải dấu thập phân. (Xem Hình 4)

Hình 4 . Xem cách Excel lưu trữ ngày và giờ.

Phần phân số được lưu trữ với số sê-ri (.75) có nghĩa là thời gian bạn đã nhập (6:00 chiều) là 75% trong ngày . Thời gian — phần thập phân — có thể nằm trong khoảng từ .0 (12:00:00 sáng) đến .999988426 (11:59:59 pm).

Vì sơ đồ lưu trữ ngày và thời gian nội bộ dưới dạng số sê-ri, trong thế giới Excel không có thứ gọi là “thời gian âm”.

Không có cách nào để phân tích cú pháp và lưu trữ một giá trị như vậy, vì vậy Excel sẽ đưa ra thông báo lỗi khi bạn thử. có thể thực hiện “toán học ngày” và trừ thời gian từ một giá trị thời gian đã được lưu trữ trong một ô, nhưng tất cả những gì bạn đang làm là điều chỉnh số sê-ri ngày và giờ. Ví dụ: nếu bạn có số sê-ri 43907,75 được lưu trữ trong một ô và bạn S rút ngắn “thời gian” sẽ cho kết quả âm, tất cả những gì bạn làm là chuyển sang ngày hôm trước. (Xem Hình 5.)

Hình 5. Trừ ngày và giờ.

Lưu ý rằng trong ô B6, một thời gian, không có ngày đi kèm, đã được nhập (10:00 tối). Số này được Excel phân tích cú pháp dưới dạng số sê-ri với “ngày tháng không” như được hiển thị trong ô C6. Nếu bạn cố gắng trừ một thời gian từ số sê-ri đó mà cho kết quả âm, thì Excel không thể hiển thị kết quả là thời gian. (Xem Hình 6.)

Hình 6. Bạn không thể trừ quá xa theo thời gian.

Nếu bạn di con trỏ chuột qua các dấu thăng được hiển thị trong ô B9, bạn sẽ thấy thông báo lỗi: “Ngày và giờ âm hoặc hiển thị quá lớn là .” (Từ ngữ chính xác của thông báo lỗi có thể khác nhau giữa các phiên bản Excel.)

Bạn cũng sẽ nhận được kết quả tương tự nếu cố gắng đến những thời điểm tiêu cực theo những cách khác. Ví dụ: nếu bạn nhập giá trị thời gian vào một ô và sau đó, trong một ô khác, hãy sử dụng công thức để nhân thời gian với -1.

Khi bạn cố gắng định dạng kết quả theo thời gian, bạn lại nhận được dấu thăng. Excel chỉ đơn giản là không thể hiển thị thời gian âm vì chúng không thể tồn tại trong chừng mực chương trình có liên quan.

Vậy Guido để làm gì? Nếu anh ta thực sự cần làm việc với những khoảng thời gian tiêu cực, anh ta có thể làm rất ít vì những lý do đã được giải thích.

(Tuy nhiên, hãy nói thêm về điều này trong giây lát.) Nếu anh ta chỉ đơn giản là muốn trừ thời gian, đó là một câu chuyện khác. Thực tế là Guido đang cố gắng nhập một cái gì đó như -02: 00 cho thấy rằng anh ta đang làm việc với thời lượng sự kiện, không phải với “thời gian đồng hồ” thực tế. (Hãy nhớ rằng Excel hoạt động với thời gian đồng hồ để phân tích cú pháp của nó và -02: 00 không phải là thời gian đồng hồ — bạn không thể nói với ai đó rằng bạn sẽ gặp họ lúc -02: 00.)

Để xử lý thời lượng sự kiện, bạn có thể chỉ cần để Excel làm phép toán cho bạn. Ví dụ: thời gian bắt đầu có thể được đặt trong ô B4 và một số giờ mà Guido muốn trừ đi (2) có thể được đặt trong ô C4. Trong ô D4 sau đó anh ta có thể sử dụng một công thức để tính toán kết quả:

Chia số giờ cho 24 sẽ cho ra giá trị thập phân chính xác được Excel sử dụng nội bộ trong các số sê-ri của nó. Tất nhiên, nếu kết quả của công thức là số sê-ri âm, thì bạn sẽ nhận được dấu thăng khi cố định dạng kết quả theo thời gian. Giải pháp duy nhất cho điều này là đảm bảo rằng những gì được lưu trữ trong ô B4 bao gồm ngày tháng. Bạn có thể định dạng ô B4 để chỉ hiển thị thời gian, nhưng việc bao gồm ngày tháng sẽ khiến kết quả bị lỗi. Bạn có thể tìm hiểu thêm về cách làm việc với thời gian âm đã trôi qua bằng cách xem phần này ExcelTip:

Sau khi tất cả được nói và làm, có một cách mà bạn có thể làm việc với thời gian tiêu cực thực sự: Chuyển sang hệ thống ngày 1904, như đã thảo luận trước đó.

Ví dụ, hãy xem kết quả của các phép trừ khi sử dụng hệ thống ngày tháng năm 1904. (Xem Hình 7.)

Hình 7. Hệ thống ngày tháng năm 1904 sẽ xử lý thời gian âm.

Cách tiếp cận này sẽ hiệu quả đối với từng sổ làm việc trên hệ thống của riêng bạn, nhưng có thể không hiệu quả khi những người khác sử dụng sổ làm việc của bạn. Dựa vào sổ làm việc sử dụng các hệ thống ngày tháng khác nhau (một số năm 1900 và một số khác năm 1904)

có thể là nguyên nhân của các sự cố tiềm ẩn khi bạn (hoặc những người khác) sao chép thông tin từ sổ làm việc sang sổ làm việc. Vấn đề này đã được thảo luận trong một ExcelTip:

Khi sử dụng hệ thống ngày tháng 1904, bạn vẫn sẽ gặp trục trặc khi nhập thời gian âm, nhưng kết quả của công thức có thể hiển thị thời gian âm mà không có vấn đề gì.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13356) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.