Find Max Date between several date columns

shellz

New member
Joined
May 26, 2016
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2016
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 1Date 2Date 3Date 4Date 5Max Date
04/05/201904/11/201904/10/2019 04/08/2019
04/11/201903/28/201903/28/201904/02/2019
03/08/201903/25/201903/11/201903/19/201903/08/20193/25/2019
04/11/201903/05/201902/28/201903/05/201904/08/20194/11/2019
02/26/201903/11/201903/14/201903/05/201902/26/20193/14/2019
 
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.
 
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:
Code:
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
Code:
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?
 
Code:
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
 
Last edited:
This worked great! I didn't think about using the List function. Thank you
 
Back
Top