1

In my sharepoint list, I have a workflow that updates today date on a scheduler at 1 AM. When new records are generated Date 2(Create time stamp) is by default a later time than today date.

So as an example today may refresh at 1 AM and set the value to 2/6/2019 1:00 AM. Later a record is created that populates Date 2 with 2/6/2019 2:45 PM. When this formula is converted, it will display as a negative value.

=IF(Column1<>"",[Date1]-Date2,Today-Date2)

How can I change my if statement to say that when the value is 'negative' to just populate a blank value?

3

The IF has 3 arguments, so:

IF(A, B, C)

  • where A is a boolean expression
  • B is what's returned if A is true
  • and C is what's returned if A is false

So if you want a blank if the difference between the dates is negative, but otherwise the diff, then you want something like this:

=IF((Date-Created)>=0, Date-Created, "")

Which says if Date-Created is greater than or equal to 0, return Date-Created, otherwise return an empty string.

1

What's the data type of your Calculated Column?

This should always work:

=IF( IF(Column1<>"",[Date1]-Date2,Today-Date2) < 0, "", 
     IF(Column1<>"",[Date1]-Date2,Today-Date2) )

This is probably what you are looking for while testing Today vs. Date2:

=IF( Today-Date2 < 0, "", IF(Column1<>"",[Date1]-Date2,Today-Date2) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.