Bạn đã để ý rằng một số công thức trong Google Sheets trả về 30-12-1899 khi một cột ngày không có giá trị? Làm thế nào để trả về giá trị trống thay vì 30-12-1899 trong Google Sheets. Có một giải pháp nhanh chóng cho vấn đề này không?
Có một số cách để giải quyết câu đố này, nhưng điều đó phụ thuộc vào công thức đang sử dụng. Ví dụ, tôi sẽ lấy ví dụ với các hàm MIN và MINIFS.
Nếu bạn muốn tìm các giải pháp cho các công thức khác mà liên quan đến các hàm khác, có nghĩa là trả về giá trị trống thay vì giá trị mặc định là 30-12-1899, bạn có thể đề xuất trong phần bình luận ở cuối trang này.
Cập nhật: Một giải pháp phổ biến cho vấn đề này đã được giới thiệu gần đây sử dụng hàm LET. Tôi đã bao gồm nó trong phần cuối của bài hướng dẫn này.
Tại sao một số công thức trong Google Sheets trả về 30-12-1899?
Trong một công thức, nếu bạn sử dụng một loạt các ô chứa các ngày và trong đó một số ô trống hoặc bằng 0, Google Sheets sẽ xem các giá trị trống hoặc bằng 0 đó như các ngày được định dạng thành số.
Tôi có thể giải thích điều này một cách chi tiết hơn. Nếu bạn định dạng số 0 thành ngày từ menu Format > Number > Date, bạn sẽ nhận được ngày 31 tháng 12 năm 1899.
Thay đổi số 0 thành 1, ngày sẽ trở thành ngày 31 tháng 12 năm 1899.
Nếu bạn định dạng số -1 thành ngày bằng cách sử dụng các bước giống nhau, bạn sẽ nhận được ngày 29 tháng 12 năm 1899.
Điều này xảy ra vì Google Sheets lưu trữ các ngày dưới dạng số lần lượt để có thể sử dụng trong các phép tính. Trong Google Sheets, ngày 31 tháng 12 năm 1899 có số lần lượt là 1. Hầu hết các hàm tự động chuyển đổi các giá trị ngày thành số.
Lưu ý nhỏ, Excel và Google Sheets sử dụng các hệ thống ngày khác nhau. Số lần lượt là 1 trong Excel đề cập đến ngày 1 tháng 1 năm 1900.
Có hai tình huống có thể xảy ra khi một số công thức trong Google Sheets trả về ngày 30 tháng 12 năm 1899:
- Khi một công thức trả về một ô trống thay vì một ngày.
- Khi một công thức trả về giá trị 0 thay vì một ngày.
Trong cả hai trường hợp, Google Sheets sẽ xem giá trị trống hoặc 0 là ngày 30/12/1899.
Dưới đây là một số ví dụ về công thức để giúp bạn tìm hiểu cách trả về giá trị trống thay vì 30-12-1899 trong Google Sheets.
Làm thế nào để Trả về Giá trị Trống Thay vì 30-12-1899 trong Google Sheets
Chúng ta có thể loại bỏ các ô trống hoặc số 0 từ dữ liệu nguồn để tìm giải pháp. Tuy nhiên, nếu làm như vậy, “ngày không hợp lệ” sẽ không hiển thị, nhưng một số hàm sẽ không hoạt động và có thể trả về lỗi “Đối số phải là một dãy”. Do đó, chúng ta sẽ điều chỉnh đầu ra của công thức để trả về số 0 hoặc giá trị trống thay vì “ngày không hợp lệ” nếu đầu ra công thức là 30-12-1899.
Ví dụ 1: Trả về Giá trị Trống Thay vì 30/12/1899 trong Hàm MIN
Trong một khoảng ngày, tôi muốn tìm ngày sớm nhất. Công thức MIN dưới đây trả về ngày 30/12/1899 vì có các ô với giá trị 0:
=MIN(B2:B11)
Dưới đây là hai giải pháp:
- Sử dụng hàm TEXT để định dạng đầu ra thành trống. Tuy nhiên, điều này có một hạn chế: khi không có giá trị 0 trong khoảng, ngày trả về sẽ có định dạng văn bản.
=TEXT(MIN(B2:B11),”DD/MM/YYYY;;”)
- Sử dụng hàm SORTN để trả về 0 thay vì giá trị trống. Khác với MIN, hàm SORTN sẽ trả về 0 nếu các ô trống hoặc bằng 0.
=SORTN(B2:B11)
Ví dụ 2: Công thức để Trả về Giá trị Trống Thay vì 30/12/1899 trong Hàm MINIFS
Khác với MIN, nếu các ô trống hoặc bằng 0, các hàm như MINIFS, MAX và MAXIFS thường trả về 0.
Tuy nhiên, kết quả MINIFS và MAXIFS thường trả về các giá trị ngày, không phải là các ngày được định dạng. Do đó, chúng ta cần áp dụng ô kết quả bằng cách sử dụng Format > Number > Date hoặc sử dụng hàm TO_DATE với công thức.
Điều này gây ra vấn đề về ngày 30/12/1899 khi kết quả là 0. Kết quả của MINIFS và MAXIFS có thể là 0 khi không có tiêu chí phù hợp.
Trong ví dụ sau đây, chúng ta có tên mặt hàng trong A2:A11 và ngày mua hàng trong B2:B11. Công thức MINIFS dưới đây trả về ngày mua hàng sớm nhất của “Mặt hàng 1” trong khoảng:
=TO_DATE(MINIFS(B2:B11,A2:A11,”Mặt hàng 1″))
Nếu chúng ta thay tiêu chí trong công thức thành “Mặt hàng 3”, công thức sẽ trả về ngày 30/12/1899.
Như một phương alternative cho MINIFS, chúng ta có thể sử dụng sự kết hợp của hàm FILTER + SORTN như sau:
=SORTN(TO_DATE(IFNA(FILTER(B2:B11,A2:A11=”Mặt hàng 3″))))
Trong đó:
- Hàm FILTER lọc khoảng B2:B11 mà trong đó A2:A11 phù hợp với “Mặt hàng 3”.
- Hàm IFNA trả về trống nếu không có phù hợp với tiêu chí.
- Hàm TO_DATE chuyển đổi kết quả thành một ngày được định dạng.
- Hàm SORTN trả về 1 mục sau khi sắp xếp khoảng theo thứ tự tăng dần.
Ở đầu bài hướng dẫn này, tôi đã đề cập rằng các giải pháp trả về giá trị trống thay vì 30-12-1899 phụ thuộc vào vấn đề cụ thể của bạn. Đây là một giải pháp thông thường có thể giải quyết hầu hết vấn đề liên quan đến ngày như trên.
Giải pháp Thông thường để Trả về Giá trị Trống Thay vì 30-12-1899 trong Google Sheets
Chúng ta có thể sử dụng hàm LET với công thức đang được thảo luận để giải quyết vấn đề của các giá trị 0 hoặc trống trong các công thức trong Google Sheets.
Mục đích của hàm LET là đánh giá một biểu thức công thức bằng cách sử dụng các đối số được đặt tên.
Dưới đây là công thức thông thường:
=LET( test, formula, IF(test=0,,test) )
Trong đó:
- formula là công thức bạn đang sử dụng.
- test là tên mà chúng ta gán cho công thức bạn đang sử dụng.
- IF(test=0,,test) là biểu thức công thức để trả về 0 nếu ngày là 30-12-1899. Bạn có thể thay thế bằng IF(test=0,,test) để trả về giá trị trống thay vì 30-12-1899.
Vì vậy, chúng ta có thể thay thế công thức MINIFS trong ví dụ trước đó bằng công thức thông thường sau:
=LET( test, TO_DATE(MINIFS(B2:B11,A2:A11,”Mặt hàng 3″)), IF(test=0,,test) )
Còn công thức MIN thì như sau:
=LET( test, MIN(B2:B11), IF(test=0,,test) )
Kết luận
Hàm LET là một công cụ mạnh mẽ có thể được sử dụng để cải thiện khả năng đọc và bảo trì các công thức trong Google Sheets. Đây cũng là một cách tốt để tránh vấn đề về các giá trị 0 hoặc trống trong các công thức.
Nếu bạn gặp bất kỳ tình huống nào trong đó một công thức trả về ngày 30-12-1899 và bạn không thể tìm một giải pháp đơn giản, hãy chia sẻ công thức đó với tôi trong phần bình luận dưới đây. Điều đó sẽ hữu ích cho các độc giả khác và tôi sẽ cố gắng hết sức để đưa ra một giải pháp cho bạn.
Cảm ơn vì đã theo dõi! Hy vọng bạn đã thích bài hướng dẫn này.
