Find Max Date between several date columns

[shellz]

is it possible to get the max date between 5 columns. If there is not a date in all columns, then Max Date should be blank

Date 1	Date 2	Date 3	Date 4	Date 5	Max Date
04/05/2019	04/11/2019	04/10/2019		04/08/2019
04/11/2019	03/28/2019	03/28/2019	04/02/2019
03/08/2019	03/25/2019	03/11/2019	03/19/2019	03/08/2019	3/25/2019
04/11/2019	03/05/2019	02/28/2019	03/05/2019	04/08/2019	4/11/2019
02/26/2019	03/11/2019	03/14/2019	03/05/2019	02/26/2019	3/14/2019

 

[Heyjoe]

Assume your first row of dates is on row 2 columns A through E.
Max(A2:E5) would give you 4/11/2019.

But your instructions say that the Max Date should be blank if any of the 5 dates is blank. Therefore you need an if statement to say if there are 5 dates use the max function, otherwise the result should be blank.

IF(COUNT(A2:E2)=5,MAX(A2:E2)," ") The result of this formula should be blank for the first row of dates.

 

[Ron Coderre]

Using your sample data, except the MaxDate col....

Here's some M-Code that does what you want.
With more time I could probably optimize it a bit more:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}, {"Date 4", type date}, {"Date 5", type date}}),
    MaxDate = Table.AddColumn(SetDataTypes, "New Column Name", 
        each
            if List.MatchesAny(List.Range(Record.ToList(_),0,5), each _ = null) 
            then null
            else 
                List.Max(
                    List.Range(Record.ToList(_),0,5)
                 ), type date)

in
    MaxDate

Explanation:
- Record.ToList()...Converts the record values into a list
- List.Range()......Returns of sequence of list values
                        -beginning at the Col_Offset column (zero based)
                        -Including the number of columns indicated

Is that something you can work with?

 

[ExcelStarter]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Date 1", type date}, {"Date 2", type date}, {"Date 3", type date}, {"Date 4", type date}, {"Date 5", type date}}),
    AddCustom = Table.AddColumn(ChangeType, "Max Date",
                            each if List.NonNullCount(Record.ToList(_))<5 then null else List.Max(Record.ToList(_)),type date)
in
    AddCustom

 

[shellz]

This worked great! I didn't think about using the List function. Thank you