Data Time Difference Error - Formula Error : "[Null]" is not a valid Date or Time

[bridrod]

Hi,

I am doing a simple date calculation, but the Null cells in "closed date" field is breaking it. I tried replacing it with a specific date, use datetimenow(),  or skip it, but it's not working.

Could you help me with samples of how to fix or improve my test using formula as a solution? if there are multiple solutions, I would love to learn them. Thanks

Rod

[AGilbert · Accepted solution]

Hi Rod, 

The null values in the Closed Date column are string values. Therefore, the isnull() function was not correctly identifying them. I changed your function to match if [Closed Date] = "[Null]". See attached. 

[Qiu · Accepted solution]

@bridrod 

You data is not Null, rather it is having the value of "[Null]".

[ShankerV · Accepted solution]

Hi @bridrod 

Please use the below formula.

IF [Closed Date] = "[Null]"
THEN DateTimeNow()
ELSE [Closed Date]
ENDIF

 

Many thanks

Shanker V

[bridrod]

Oh wow! I would never have guessed! Thanks all! 😊