Results 1 to 5 of 5

Thread: Find Max Date between several date columns

  1. #1
    Seeker shellz's Avatar
    Join Date
    May 2016
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Find Max Date between several date columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    52
    Articles
    0
    Excel Version
    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.

  3. #3
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    99
    Articles
    0
    Excel Version
    2013, 2016, O365
    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?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  4. #4
    Seeker ExcelStarter's Avatar
    Join Date
    Jan 2018
    Posts
    19
    Articles
    0
    Excel Version
    2016
    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 by ExcelStarter; 2019-04-13 at 06:17 AM.

  5. #5
    Seeker shellz's Avatar
    Join Date
    May 2016
    Posts
    7
    Articles
    0
    Excel Version
    2016
    This worked great! I didn't think about using the List function. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •