We can consider using a few functions or combo array/non-array formulas to get the max date in each row in Google Sheets.
The two main functions for this are Max and Large. But we can also consider using Query and Dmax.
What about combinations? Here are a few of them.
- Sortn + Transpose.
- Sort + Transpose + Array_Constrain.
- Dmax + Transpose.
All the above discussions were about non-array formulas.
What about an array formula to return the max date in each row in Google Sheets?
In that case, we can try the Dmax + Transpose combination formula.
But now, i.e., since the introduction of the new function bundle, we can use the Max function with Byrow Lambda to get the latest dates row-wise.
In this tutorial, you can get the three best formulas to return the recent/latest date in each row in a column.
They are;
- Max – Non-Array Formula (we will use this function in a combination formula to remove text strings, blanks, and zero values in the range).
- Dmax + Transpose – Array Formula.
- Max + Byrow (the array version of option # 1 above) – Recommended!
Sample Data and Non-Array Formula
Assume you have a table that contains a few columns of data.
The first column is for serial numbers, and the second is for employee names.
The first row, after the first two columns, contains activity names. The completion dates by different employees/assignees are below them.
I 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:- The formula is for cell I2. We must copy-paste it to the range I3:I10.
Formula # 1:
=iferror(MAX(filter(C2:G2,datevalue(C2:G2))))
Out of five activities of “Ryan” (please see the range B2:G2), his latest completion date is 10-Sept-21.
He has completed the other three activities before that.
Why have I used a combination formula above instead of the Max alone?
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 evaluation.
If you want to find the second or nth latest date, replace Max with LARGE.
Formula # 1.1:
=iferror(LARGE(filter(C2:G2,datevalue(C2:G2)),n))Replace n with 1, 2, or 3.
Get Max Date in Each Row Using DMAX Array Formula 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 the First and Second Highest Values in Each Row in Google Sheets.
We will adopt the same approach here.
Formula # 2 (I2:I10 must be blank beforehand):
=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 no submitted 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 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.
Get Max Date in Each Row Using MAX Lambda Array Formula – New!
Please scroll up and see the first formula under the subtitle “Sample Data and Non-Array Formula.”
We can use the BYROW Lambda Helper function to expand that Max formula result.
So, to get the max activity date in each row, we can use the following array formula.
Formula # 3 (I2:I10 must be blank beforehand):
=byrow(C2:G10,lambda(r, iferror(MAX(filter(r,datevalue(r))))))How to get the second or nth latest date in each row in Google Sheets?
Then use Byrow with Formula # 1.1 as below.
=byrow(C2:G10,lambda(r, iferror(LARGE(filter(r,datevalue(r)),2))))That’s all. Thanks for the stay. Enjoy!
Resources:
