Post

2 followers Follow
0
Avatar

Max date formula with NULL data

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

Dennis Miller Answered

Please sign in to leave a comment.

2 comments

1
Avatar

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 Pumputis 1 vote
Comment actions Permalink
0
Avatar

Roland,

That worked out perfectly, thank you!

I am surprised that the MAX function does not account for NULL inputs.

Dennis Miller 0 votes
Comment actions Permalink