Did you notice that some of the Google Sheets formulas return the date 30-12-1899 when a date column is empty? It can be quite frustrating, but thankfully, there are solutions to this problem. In this article, we will explore how to return a blank cell instead of 30-12-1899 in Google Sheets formulas.
Why Do Some Google Sheets Formulas Return 30-12-1899?
Before we dive into the solutions, let’s understand why some formulas in Google Sheets return the date 30-12-1899. In formulas, when you use a range of cells that contain dates, and some of those cells are blank or have the value 0, Google Sheets treats those blank or 0 values as dates formatted to numbers.
So, when you format the number 0 as a date, it becomes December 31, 1899. Similarly, if you format the number -1 as a date, it becomes December 29, 1899. This is because Google Sheets stores dates as serial numbers, and December 31, 1899, is serial number 1.
In Excel, the date system is different, and serial number 1 refers to January 1, 1900. Now that we understand why this happens, let’s explore some solutions.
How to Return Blank Instead of 30-12-1899 in Google Sheets
To return a blank cell instead of 30-12-1899 in Google Sheets, we can filter out blanks or zeros from the source data. However, this approach may cause some functions to return errors. Therefore, we will tweak the formula output to return zero or blank if the output is 30-12-1899.
Example 1: Return Blank Instead of 30/12/1899 in MIN Function
Let’s say you want to find the earliest date in a range of dates. The MIN function returns the date 30/12/1899 when there are cells with the value 0. Here are two solutions:
-
Use the TEXT function to format the output to blank. However, note that when there are no 0 values in the range, the returned date will be in text format. The formula would be:
=TEXT(MIN(B2:B11),"DD/MM/YYYY;;"). -
Use the SORTN function to return 0 instead of blank. Unlike MIN, the SORTN function will return 0 if the cells are blank or zero. The formula would be:
=SORTN(B2:B11).
Example 2: Formula to Return Blank Instead of 30/12/1899 in MINIFS Function
Unlike MIN, functions like MINIFS, MAX, and MAXIFS usually return 0 if the cells are blank or zero. However, they return date values, not formatted dates. To fix the issue when the result is 0, we need to either format the result cell as a date or use the TO_DATE function in the formula itself.
For example, if we have item names in column A and purchase dates in column B, the following MINIFS formula returns the earliest purchase date of “Item 1” in the range: =TO_DATE(MINIFS(B2:B11,A2:A11,"Item 1")).
As an alternative, we can use the FILTER + SORTN combination:
=SORTN(TO_DATE(IFNA(FILTER(B2:B11,A2:A11="Item 3"))))At the beginning of this tutorial, I mentioned that the solutions may vary depending on your specific problem. However, there is a common solution that can solve most date-related issues.
Common Solution to Return Blank Instead of 30-12-1899 in Google Sheets
To solve the problem of 0 or blank values in formulas, we can use the LET function. The LET function evaluates a formula expression using declared named arguments. Here is the generic formula:
=LET(test, formula, IF(test=0,,test))
In this formula, the test is the name assigned to the formula you are using, and formula is the formula itself. The expression IF(test=0,,test) returns blank instead of 30-12-1899.
For example, if we replace the MINIFS formula in the earlier example, it would look like this:
=LET(test, TO_DATE(MINIFS(B2:B11,A2:A11,"Item 3")), IF(test=0,,test)).
And for the MIN formula, it would be:
=LET(test, MIN(B2:B11), IF(test=0,,test)).
Conclusion
The LET function is a powerful tool in Google Sheets that can improve the readability and maintainability of formulas. It also solves the problem of 0 or blank values in formulas. If you encounter any situations where a formula returns the date 30-12-1899 and you can’t find a solution, feel free to share the formula in the comments. I’ll do my best to provide a solution.
Thanks for reading this tutorial, and I hope you found it helpful. If you want to learn more about Google Sheets and other useful tips and tricks, visit Crawlan.com.
