# Thread: Find Max Date between several date columns

1. ## 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. 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. 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?

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

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