How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Friday

Get Min Date Ignoring Blanks in Each Row in Google Sheets

Posted by   on

I have an array and non-array solutions to get min dates that ignoring blanks in each row in Google Sheets.


Ignoring blanks is very important in this context. Do you know why?


Assume you have a date range B2:F7.


You want to get min dates ignoring blanks in each row in this range.


If all or some of the cells have value in each row, there is no issue in getting the correct min date from this range.


You can use the below MIN formula for that in cell H2 and copy-paste it down.


A] MIN Non-Array Formula:


=min(B2:F2)

You can also use a DMIN based array formula for that.


B] DMIN Array Formula (Empty H2:H7 and then insert it in H2 alone):


=ArrayFormula( TO_DATE( DMIN( transpose(B1:B7,B1:F7), sequence(rows(B2:B7),1,2), "Date 1";if(,,) ) )
)


If you want to learn the above DMIN array formula, please read my guide – Row-Wise MIN Using DMIN in Google Sheets.


But do note that the above formula is slightly different from the formula explained in the linked tutorial.


Here I have brought some performance enhancements which are self-explanatory.


What Happens When Blank Rows Are Present between Date Rows?


If all the cells are blanks in any row, you won’t get blank using MIN, SMALL, or whatever array formula you are using.


Instead, when using MIN, you will get 0 if the cell range is formatted to number or 30/12/1899 if the cell range is formatted to date.


When Using DMIN, you will get 30/12/1899 irrespective of the formatting.


So you won’t be able to use the above formulas for getting the min dates ignoring blanks in each row in Google Sheets.


Let’s test it by following the below steps.



  1. Empty the range B5:F6.

  2. Select B5:F5 and go to the menu Format > Number > Number.

  3. Select B6:F6 and go to the menu Format > Number > Date.


We have formatted two rows differently.


Insert the above Min formula in cell H2 and copy down and see the bizarre output.


Drag-Down Min Formula in Date Range and Issues

Also, try with the above DMIN.


What are the solutions?


Formulas to Get Min Date Ignoring Blanks in Each Row


Non-Array Solutions


C] If you prefer a non-array solution, use the FILTER function to filter out blank cells in rows and use MIN.


=iferror(min(filter(B2:F2,datevalue(B2:F2))))

Select the outputs and format the date values to dates. It’s applicable to the below combination too.


D] Another option is using the SMALL with COUNTIF.


=iferror(SMALL(B2:F2,COUNTIF(B2:F2,0)+1))

We can use SMALL to find the nth smallest date in the given date range.


The COUNTIF counts the 0’s. We add 1 to it and that serves as nth.


Array Formula to Get Min Date Ignoring Blanks in Each Row


Here is the array formula to get the min date, ignoring blanks in each row in Google Sheets.


Generic Formula: IF(DMIN_Array_Formula >0, DMIN_Array_Formula,)


E] Formula:


=ArrayFormula( TO_DATE( if( DMIN( transpose(B1:B,B1:F), sequence(rows(B2:B),1,2), "Date 1";if(,,) )>0, DMIN( transpose(B1:B,B1:F), sequence(rows(B2:B),1,2), "Date 1";if(,,) ), ) )
)

Output:


Min Dates in Each Row Ignoring Blanks and Zeros

Since the formula is capable of ignoring blank cells between dates, I have opened the range.


I mean, replaced B7 and F7 with B and F, respectively.


Impact of Zero Vlaues


I won’t expect a user would enter 0 between dates instead of leaving cells blank.


But it may happen when he has an imported data processed within Sheets.


In such scenarios, there won’t be an issue if you use formulas C or D.


I mean with the two non-array formulas that we have used above to get the min / smallest date ignoring blanks in each row.


Regarding formula E, the complex-looking array formula, you should replace B1:F (appears twice) with the below formula part.


iferror(datevalue(B1:F))

That’s all. Thanks for the stay. Enjoy!


Example Sheet 170921


Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!