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...

Monday

How to Get Max Date in Each Row in Google Sheets [Array Formula]

Posted by   on

To get the max date in each row in Google Sheets, we can think about using a few functions and combo array/non-array formulas.


The two main functions for this are Max and Large. But we can also consider using Query and Dmax.


What about combinations?


We can try;



  1. Sortn + Transpose

  2. Sort + Transpose + Array_Constrain

  3. Dmax + Transpose.


What about an array formula to return the max date in each row in Google Sheets?


Then I will recommend the last combo, i.e., Dmax + Transpose.


In this tutorial, you can get the two best formulas to return the recent/latest date in each row in a column.


They are;



  1. Max – Non-Array Formula (we will use this function in a combination formula to remove text strings, blanks, and zero values in the range).

  2. Dmax + Transpose – Array Formula.


Sample Data and Non-Array Formula


Assume you have a table in Sheets that contains a few columns of data.


In that, the first column occupies serial numbers (Sr. No.), and the second column the employee names (Assignee).


The first row, after the first two columns, contains activity names. The completion dates by different employees/assignees are below them.


Want to find the latest completion date of the activities by assignees in every row?


In such a case, we can use a MAX-based combination formula to find the max date in each row as below.


Note:- Formula is for cell I2. We must copy-paste it to the range I3:I10.


=iferror(MAX(filter(A2:E2,datevalue(A2:E2))))

Max Date in Each Row in Google Sheets

Out of the five activities, Ryan’s (please see the range B2:G2) latest completion date is 10-Sept-21.


He has completed the other three activities before that, and one is still pending.


Why have I used a combination formula above?


The above data is not suitable enough to use Max or Large alone because the date range contains blanks, texts, and zeros.


When we use the functions such as Max or Large in such data types, we may encounter two types of errors – #NUM!, and an invalid date, i.e., 30-Dec-99.


To avoid them, I have used FILTER and DATEVALUE functions to filter out those unwanted values before max.


If you want to find the second or nth latest date, replace Max with LARGE.


=iferror(LARGE(filter(C2:G2,datevalue(C2:G2)),n))

Replace n with 1, 2, or 3.


We can replace the Filter, Datevalue, and Max combination above with a Dmax array formula. You can find that below.


Array Formula to Get Max Date in Each Row in Google Sheets


As you may already know, DMAX is a database function. Its role is to return max from columns, not from rows.


But we can use TRANSPOSE to change the data orientation from rows to columns and use Damx in such data.


I have already explained such usage here – Return First and Second Highest Values in Each Row in Google Sheets.


We will adopt the same approach here.


Empty I2:I10 if already filled with the above non-array formula.


Then insert the following Dmax + Transpose combo in I2.


=ArrayFormula( TO_DATE( DMAX( transpose(C1:C10,C1:G10), sequence(rows(C2:C10),1,2), "Date 1";if(,,) ) )
)

It will return the max date in each row but with some invalid dates.


Please see the cells I5, I6, I8, I9, and I10 for the date 30/12/1899.


It’s because the assignees have not submitted any dates below their activities.


We want the formula to return blank in those rows.


How?


Let’s call the above formula base_dmax.


We can follow an IF logical test as below to sort out our issue.


IF(base_dmax >0, base_dmax,)

Let’s code a new formula based on it.


=ArrayFormula( TO_DATE( if( DMAX( transpose(C1:C,C1:G), sequence(rows(C2:C),1,2), "Date 1";if(,,) )>0, DMAX( transpose(C1:C,C1:G), sequence(rows(C2:C),1,2), "Date 1";if(,,) ), ) )
)

Replace the I2 array formula with the above one.


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


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!!