Requesting help with a max date formula in customizing a field.
We have several custom date fields, using “Max($C_D1, $C_D2, ect..” To get the maximum date out of these fields but receive an error code if a NULL is present with in the dataset.
How can we write a formula that ignores NULL and will display NULL if no data is present in all data fields?
Thank you
Please sign in to leave a comment.
Hi Dennis,
It appears that Max() does not work with NULL, so maybe replacing NULL with an old date would work.
Maybe something like this:
If(Max(If(IsNull($C_D1), Date(1910, 1, 1), $C_D1), If(IsNull($C_D2), Date(1910, 1, 1), $C_D2)) = Date(1910, 1, 1), NULL, Max(If(IsNull($C_D1), Date(1910, 1, 1), $C_D1), If(IsNull($C_D2), Date(1910, 1, 1), $C_D2)))
This would not work with dates before or equal to January 1st, 1910.
I hope this helps.
Roland
Roland,
That worked out perfectly, thank you!
I am surprised that the MAX function does not account for NULL inputs.