Why does MAX return 0 for a column of dates?
Excel formula
=MAX(DATEVALUE(B2:B7))The dates are text, and MAX ignores text. Convert them inside the formula: =MAX(DATEVALUE(B2:B7)).
- Result on the example
- 4/27/2026 in
D2 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
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.
=MAX(DATEVALUE(B2:B7))Result46139
B2:B76 rows × 1 col: "3/2/2026", "11/14/2025", "4/27/2026", "1/9/2026", …A range: 6 cells in one column.
DATEVALUE(B2:B7){6 rows × 1 col} 46083, 45975, 46139, 46031, …DATEVALUE: Converts date text to a date.- date_text
B2:B76 rows × 1 col: "3/2/2026", "11/14/2025", "4/27/2026", "1/9/2026", …
- date_text
MAX(DATEVALUE(B2:B7))46139MAX: Largest number.- number1
DATEVALUE(B2:B7){6 rows × 1 col} 46083, 45975, 46139, 46031, …
- number1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(MAX(DATEVALUE(B2:B7))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(--B2:B7) | 4/27/2026 | Converts with -- instead. In Google Sheets, wrap it in ARRAYFORMULA. |
| =MIN(DATEVALUE(B2:B7)) | 11/14/2025Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why does MAX return 0 for a column of dates
D2, return the latest shipping date from the date text in B2:B7.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.