Latest date out of 3 date columns

That could be:

LatestDate: IIf([Date1] > [Date2] And [Date1] > [Date3], [Date1], IIf([Date2] > [Date3], [Date2], [Date3]))

Select allOpen in new window

Thanks for your reply Gustav,

I could see your formula does not work if one value is null I am afraid. It returns null.

Ta
A

For what it's worth, I only got a NULL as the result of that calculated column if all three columns were NULL.  If just one or two were it still returned the largest date of the others.  Based on that you might be able to just do:

LatestDate: Nz(IIf([Date1] > [Date2] And [Date1] > [Date3], [Date1], IIf([Date2] > [Date3], [Date2], [Date3])),0)

Select allOpen in new window

---

»bp

Thank you Bill, that worked for me.

Great, glad that helped.

---

»bp

Bill, what is the difference between your 1st and 2nd formulas. I did not see the 2nd one, I just spotted now.

Can I still use 1st?

Thank you
A

Try them both, if they both solve your problem for all cases where NULL shows up then I'd go with the second, it's a bit more efficient.

---

»bp

Thanks Bill

I use a function, which looks like:

Public Function fnMax(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Select allOpen in new window

With this function, you would simply write:

LatestDAte: fnMax([Date1], [Date2], [Date3])

It accepts a parameter array, so you can pass as few or as many values or fields into it as you want.
You can use it with dates, numbers, even strings to find the maximum, although I've never used it with strings.

your formula does not work if one value is null

That you didn't mention.

That is why I use the function, it ignores NULL values passed to it.