Have you ever been frustrated trying to find the minimum date in a range of dates in Google Sheets while ignoring any blank cells? Well, worry no more! In this article, I’ll share with you some handy tricks to get the minimum dates that ignore blanks in each row. Trust me, these tips will save you time and frustration!
Why Ignoring Blanks is Important
Imagine this scenario: you have a range of dates in cells B2 to F7, and you want to find the minimum date in each row while ignoring any blank cells. Sounds simple enough, right? Well, not quite. If there is at least one date in each row and the range only contains dates, you can use the simple MIN formula in cell H2 to get the correct result. Just copy and paste it down the column, and voila!
=MIN(B2:F2)However, things get a bit trickier when there are blank cells in each row. The MIN formula won’t work as expected, and you’ll end up with 0 or a default date like 30/12/1899, depending on the formatting of the result cell. Not exactly what you want, right? But don’t worry, I have a solution that will give you the desired result.
Array Formulas to the Rescue
Solution A: DMIN-Based Array Formula
One option is to use a DMIN-based array formula. Although it might seem a bit complex at first glance, trust me, it’s worth the effort. Here’s the formula to insert in cell H2 (leave the range H2:H7 empty):
=ArrayFormula(TO_DATE(DMIN(transpose({B1:B7,B1:F7}),sequence(rows(B2:B7),1,2),{"Date 1";if(,,)})))This formula uses the DMIN function to find the minimum date in each row, while the ArrayFormula allows it to work across multiple rows. Don’t worry if you don’t fully understand it right away. You can refer to my guide, “Row-Wise MIN Using DMIN in Google Sheets,” for a detailed explanation.
Solution B: MIN Lambda Array Formula
If you prefer a simpler solution, you can use the MIN Lambda Array Formula. This formula leverages the BYROW Lambda helper function to accomplish the task. Just like the previous formula, insert it in cell H2 (leave the range H2:H7 empty):
=byrow(H2:H7,lambda(r,min(r)))This formula is more concise and efficient than the DMIN-based one. It uses the lambda(r,min(r)) function to calculate the minimum date in each row.
What Happens When Blank Rows Are Present?
But what if you have blank rows within your date rows? Will the formulas still work? Unfortunately, no. In such cases, instead of getting blank cells in the corresponding result cells, you will get 0 or 30/12/1899, depending on the formatting of the result cells.
To demonstrate this, follow these steps:
- Empty the range B5:F6.
- Select B5:F5 and go to the menu Format > Number > Number.
- Select B6:F6 and go to the menu Format > Number > Date.
Now, insert the Min formula or the DMIN formula in cell H2 and copy it down. You will notice the unexpected output. These formulas are unable to handle blank rows within the date rows.
Non-Array Solution
If you prefer a non-array solution and want to filter out blank cells in rows, you can use the FILTER function along with MIN. Here’s the formula:
=IFERROR(MIN(FILTER(B2:F2,DATEVALUE(B2:F2))))This formula filters out the blank cells in the range B2:F2 using the FILTER function and then calculates the minimum date using MIN. Make sure to select the outputs and format the date values as dates.
Impact of Zero Values
While it’s not common to have zero values between dates, it may happen when you import data processed within Sheets. In such scenarios, you can still use the array and non-array formulas mentioned above to get the minimum/smallest date while ignoring blanks.
For the DMIN formula, you need to replace the instances of B1:F (appears twice) with the following formula part:
=IFERROR(DATEVALUE(B1:F))And there you have it! With these formulas in your arsenal, you can effortlessly find the minimum date in each row while ignoring any blanks or zero values.
To learn more about Google Sheets formulas and explore additional resources, head over to Crawlan.com.
Enjoy exploring the possibilities of Google Sheets, and feel free to reach out if you have any questions. Happy spreadsheet-ing!
Example Sheet 170921
