Why Google Sheets Shows 1899-12-30 (and How to Return Blank Instead)

If you’ve ever seen 1899-12-30 (or 30-12-1899, depending on your locale) in Google Sheets, it doesn’t always mean the date was entered manually. In many cases, it appears because a formula returns 0 (or a blank result that gets treated as 0) and the result is displayed using a date format.

Some common situations where this happens include:

Using the TEXT function on a blank or zero value with a date format.
Formatting the number 0 as a date using Format > Number > Date.
Converting 0 to a date with the TO_DATE function.

Understanding why this happens makes it much easier to choose the right fix.

Table of Contents

Why Google Sheets Shows 1899-12-30

Google Sheets stores dates as serial numbers, where 1899-12-30 is serial number 0. Each day after that increases the serial number by one.

Serial Number	Evaluates to Date	Common Cause in Formulas
-1	1899-12-29	Negative time durations or manual offsets
0	1899-12-30	Blank cells, literal 0, or unmatched MINIFS/DMIN
1	1899-12-31	Hardcoded 1 formatted as a date
2	1900-01-01	Start of the modern century

When a formula returns 0, or when a blank result is interpreted as 0 and then formatted as a date, Google Sheets displays 1899-12-30. The exact solution depends on the function you’re using, which is why different formulas require different approaches.

In the examples below, you’ll learn formula-specific fixes as well as a reusable LET-based approach that works in many situations.

Fixing the 1899-12-30 Issue in Google Sheets

The 1899-12-30 date usually appears when a value of 0 or a blank result is formatted as a date. The solution depends on what caused the issue.

When Using the TEXT Function

Suppose you want to convert a date into a month and year using the TEXT function:

=TEXT(A1, "MMMM yyyy")

If cell A1 contains the date 2025-01-01, the formula returns January 2025. However, if A1 is blank, the formula returns December 1899.

To prevent this, first check whether A1 contains a valid date:

=IF(ISDATE(A1), TEXT(A1, "MMMM yyyy"), "")

The ISDATE function verifies that A1 contains a valid date before applying the TEXT function. If not, the formula returns a blank.

Although ISDATE accepts a range as its argument, it returns only a single TRUE or FALSE value instead of evaluating each row. To validate an entire column of dates, you can use the undocumented ISDATE_STRICT function:

=ARRAYFORMULA(IF(ISDATE_STRICT(A2:A), TEXT(A2:A, "MMMM yyyy"), ""))

This formats only valid dates and leaves blank cells blank.

When Using the MINIFS or DMIN Functions

Functions such as MINIFS and DMIN are often used to return the earliest date that matches a condition.

Assume the range A1:B contains sample data, where A1 and B1 contain the column labels Name and Date, respectively. The range A2:A contains employee names, and B2:B contains the corresponding advance dates.

Since an employee may receive advances multiple times, the same name can appear more than once.

To return Ben’s earliest advance date, you can use either of these formulas:

=MINIFS(B2:B, A2:A, "Ben")

=DMIN(A1:B, 2, {"Name"; "Ben"})

Both formulas return the earliest matching date as a date serial number. To display it as a date, wrap the formula in TO_DATE:

=TO_DATE(MINIFS(B2:B, A2:A, "Ben"))

=TO_DATE(DMIN(A1:B, 2, {"Name"; "Ben"}))

These formulas work correctly as long as a matching employee exists.

However, if Ben doesn’t exist in column A, MINIFS or DMIN returns 0. Since TO_DATE(0) corresponds to 1899-12-30, that’s what gets displayed.

To return a blank instead, use LET to test the result before converting it to a date:

=LET(f, MINIFS(B2:B, A2:A, "Ben"), IF(f=0,,TO_DATE(f)))

When using DMIN:

=LET(f, DMIN(A1:B, 2, {"Name"; "Ben"}), IF(f=0,,TO_DATE(f)))

These formulas return a blank when no matching date is found, preventing 1899-12-30 from appearing.

Conclusion

You’ve learned why 1899-12-30 appears in Google Sheets and how to prevent it from showing up unintentionally.

The key is to identify what causes the value to appear in the first place. Whether it’s the TEXT function, TO_DATE, MINIFS, DMIN, or another formula returning 0 or a blank result, you can use the appropriate technique to return a blank instead of displaying 1899-12-30.

Once you understand that 1899-12-30 represents the serial date 0, these unexpected dates become much easier to troubleshoot.

20 COMMENTS

Carina December 13, 2023 At 9:29 pm

Hi Prashanth,

I’m looking to find the most recent date among a selection of cells. I can’t just select the cell directly; I need to specify individual cells. From there, I want the formula to output the most recent date. If there are no entries, I want it to remain blank rather than displaying ’30/12/1899′.

I tried using the “LET” formula with the following code:

=LET(test,(MAX(J11,N11,R11,V11,Z11),if(test=0,,test)))

However, I’m encountering a “Formula Parse Error.” I attempted using the semicolon (;) between the individual cell names, but it didn’t work. I have a feeling I’m close but missing something.

Can you assist?

Thank you.

- Prashanth KV December 15, 2023 At 2:09 pm
  
  You were very close. The problem was with the incorrect use of parentheses. You can try this formula:
  
  =LET(test, MAX(J11, N11, R11, V11, Z11), IF(test=0, , test))
  
Satya Prakash October 5, 2023 At 12:22 pm

Hi Prashanth,

Please find the link below. The first tab, “IPOs”, has the issue in question.

[URL removed]

Thanks in advance.

- Prashanth October 5, 2023 At 1:36 pm
  
  Hi Satya Prakash,
  
  I don’t have access to your Sheet. I’ve sent a request twice. Please check your email.
  
  - Satya Prakash October 5, 2023 At 10:56 pm
    
    Thank you very much. I have just given you access to the Google Sheets file. Please do the needful.
    
    I’m sorry I didn’t see your earlier email.
    
    Thanks again,
    
    - Prashanth October 6, 2023 At 8:43 am
      
      Hi Satya Prakash,
      
      I’ve updated one of your formulas. Please find the formula in cell Z47.
      
      You can follow the same steps to modify other formulas. In the meantime, I’ll update this tutorial too.
      
      - Satya Prakash October 6, 2023 At 2:10 pm
        
        Hi Prashanth
        Thank you very much
        
Satya Prakash October 3, 2023 At 12:13 am

I have the same problem, but I can’t find the option to attach my Google Sheet for your perusal.

- Prashanth October 3, 2023 At 8:27 am
  
  Hi Satya Prakash,
  
  Just copy and paste the URL of the Sheet in question in your reply. I will not publish the URL.
  
Karen Brown September 9, 2021 At 8:13 pm

Hi,

Here is my formula.

=concatenate( text(vlookup(A7,StudentVaccineData!A:E,4,false),"mm/dd/yyyy"),", ", text(vlookup(A7,StudentVaccineData!A:E,5,false),"mm/dd/yyyy”),", ", vlookup(A7,StudentVaccineData!A:E,2,false) )

It returns this when it encounters the blank cell: “08/20/2021, 12/30/1899, sample_text.”

With your knowledge, I’m hoping you can help.

Thank you! Karen

- Prashanth September 11, 2021 At 12:11 pm
  
  Hi, Karen Brown,
  
  No need to require multiple Vlookups as you can use it to return multiple columns.
  
  Related: Multiple Values Using Vlookup in Google Sheets is Possible [How to].
  
  Then, use TEXTJOIN instead of CONCATENATE to remove blanks and join the output. That would eventually solve the date problem.
  
  Here is the formula that you can try.
  
  =ArrayFormula(IFNA(textjoin(", ", true,to_date(vlookup(A7,StudentVaccineData!A1:E,{4,5,2})))))
  
Binge May 9, 2021 At 11:18 pm

Hi! Trying to add deadline (+1) in the formula for a whole column but the rest of the column would have 31/12/1899 for unfilled rows. This is my formula. Can someone please help me leave the rest of the columns as blank if the row is still emptied?

=iferror(arrayformula(if(isblank(J2:J),"",(J2:J+1))))

I’m new to learning how the excel function works and been trying out different functions as to eliminate dates that haven’t filled their rows.

- Prashanth May 10, 2021 At 7:26 am
  
  Hi, Binge,
  
  Your formula seems correct. If you share a sample of your sheet, I may be able to assist you. If you wish, you can share the link via comment. I won’t publish that link.
  
Jeffri Hidajat November 19, 2020 At 5:47 pm

Hi,

I am working on a worksheet. In a cell in the worksheet I am working on, I am trying to get a value from another tab, let’s say “tab A”.

In the cell in “tab A” it is the date in text, but when linking the value to the cell in the worksheet I am working on, it shows the “date value”.

I am trying to do the trick by inserting the cell next to the cell I am working on and put the syntax as follow:

=arrayformula(if(row(Q:Q)=1,"CYOpenDate",TEXT(Q:Q,"DDD, DD MMM YYY")))

But when the cell in “tab A” is blank without ‘date in the text’, the cell in the worksheet that I am working on showing 30 December 1899.

How to leave the cell blank if there is no ‘date in the text’ in the “tab A”

Or actually,

If there is a simple way just to convert the “date value” to the ‘date in the text’ from the beginning.

It might be confusing to understand in detail. If you have an email, I can show you what I am working on.

- Prashanth November 21, 2020 At 8:39 am
  
  Hi, Jeffri Hidajat,
  
  Instead of sending via Email, you can share it via as a reply to this answer. The comment reply won’t be published.
  
laura futuro July 9, 2020 At 8:14 pm

Hope that works, I have a column to input with dates, and created a formula where I can have the range of the min and max dates. But if the cells are blank it will return the 1899 date, instead of the IFERROR command.

=IFERROR(CONCATENATE(TEXT(MIN(D7:J19),"mm/dd/yyyy")," - ",TEXT(MAX(D7:J19 ),"mm/dd/yyyy")),"Looking for dates")

If there are dates in the cell it works correctly giving me the following range text for example “04/27/2020 – 08/31/2020”.

Can I fix that so instead of “12/30/1899 – 12/30/1899” I get the text “Looking for dates”

- Prashanth July 10, 2020 At 11:27 am
  
  Hi, laura futuro,
  
  Instead of IFERROR, use the below IF logical formula.
  
  =if(COUNT(iferror(ArrayFormula(datevalue(D7:J19))))=0,"Looking for dates","your formula")
  
  Replace “your formula” with your MIN-MAX formula. That means you can use the below formula to skip the date “12/30/1899” if the range is blank or doesn’t contain any date values.
  
  =IF(COUNT(iferror(ArrayFormula(datevalue(D7:J19))))=0,"Looking for dates",(CONCATENATE(TEXT(MIN(D7:J19),"mm/dd/yyyy")," - ",TEXT(MAX(D7:J19 ),"mm/dd/yyyy"))))
  
  Best,
  
Neil November 28, 2018 At 3:40 am

When using this formula to achieve a date time stamp in a column that does not change every time I open or close the worksheet, it now returns the 12/30/1899 time instead of NOW.=IF(B522="",IF(A522="",NOW(),A522),"") or the NOW function is returning a 0 which then equates to the 12/30/1899 date. This formula was working fine for the first 1000 rows of entries but now it is returning the wrong date in my Google sheets workbook. Any ideas??

- Prashanth November 28, 2018 At 9:21 am
  
  Hi, Neil,
  
  A demo sheet can help me to understand the problem well and to offer you a possible solution.
  
- Ronak May 31, 2022 At 4:44 pm
  
  Same problem.