# Why does MAX return 0 for a column of dates?

The dates are text, and MAX ignores text. Convert them inside the formula: =MAX(DATEVALUE(B2:B7)).

**Excel:** `=MAX(DATEVALUE(B2:B7))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(MAX(DATEVALUE(B2:B7)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Shipment | Shipped on (text) |  | Latest |
| SH-41 | 3/2/2026 |  |  |
| SH-42 | 11/14/2025 |  |  |
| SH-43 | 4/27/2026 |  |  |
| SH-44 | 1/9/2026 |  |  |
| SH-45 | 4/3/2026 |  |  |
| SH-46 | 12/22/2025 |  |  |

Formula in D2: `=MAX(DATEVALUE(B2:B7))`

Result: 4/27/2026

## How it works

MAX ignores text, and these dates are text, so it finds no numbers at all and returns 0, which a date format shows as 1/0/1900. DATEVALUE converts the whole range to real dates first, and MAX returns the latest, April 27, 2026.

## Other ways

- `=MAX(--B2:B7)`: Converts with -- instead. In Google Sheets, wrap it in ARRAYFORMULA.
- `=MIN(DATEVALUE(B2:B7))`: The earliest date works the same way. (Returns the earliest date, 11/14/2025.)

## Common mistakes

- A result of 0, or 1/0/1900, from MAX, MIN or SUM is the classic sign of numbers or dates stored as text.
- Format the result cell as a date, or it shows a serial number like 46139.
- In Excel 2019 and earlier, confirm the formula with Ctrl+Shift+Enter.

## Questions

**How do I get the latest date for one customer?**

Convert the column to real dates first; then MAXIFS works: `=MAXIFS(B2:B7,A2:A7,"SH-43")`.

**Why does Google Sheets show 12/30/1899 instead?**

That is how Google Sheets displays 0 as a date. The cause is the same.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/max-of-dates-returns-zero