如果答案可能是负数，如何计算Sharepoint中两个日期之间的经过时间？

I am working on a Sharepoint 2013 formula that will calculate the time in days between two dates. I have successfully done so with this formula:

我正在研究一个Sharepoint 2013公式，它将计算两个日期之间的天数。我用这个公式成功地做到了：

=DATEDIF(Created,[Sold On],"d")

= DATEDIF（创建，[已售出]，“d”）

However, there will be some instances when the Created date comes AFTER the Sold On date, thus the days will be a negative number. I have attempted the following formulas:

但是，有些情况下，创建日期是在售出日期之后，因此日期将是负数。我尝试了以下公式：

1) =IF(ISERROR(DATEDIF(Date1,Date2,"d")), -DATEDIF(Date2,Date1,"d"))

2) =IF(ISERROR(DATEDIF(Created,Sold On,"d")), -DATEDIF(Sold On,Created,"d"),DATEDIF(Created,Sold On,"d"))

3) =IF(ISERROR(DATEDIF(Created,[Sold On],"d")), -DATEDIF(Sold On,[Created],"d"))

4) =IF(ISERROR(DATEDIF(Created,[Sold On],"d")), -DATEDIF(Sold On,[Created],"d"),DATEDIF(Created,[Sold On],"d"))

I found these examples on the Sharepoint forums. None of these formulas worked for me. Sharepoint threw a syntax error for each. I even thought I detected a missing closing parenthesis at the end of each of the above and tried each that way. Still threw syntax errors. Any suggestions?

我在Sharepoint论坛上找到了这些示例。这些公式都不适合我。 Sharepoint为每个提出了语法错误。我甚至以为我在上面的每个结尾都检测到一个缺少的右括号，并尝试了每一种方式。仍然抛出语法错误。有什么建议么？

1 个解决方案

#1

DATEDIF returns an error if the result is negative, so in the second DATEDIF in the IF, reverse the order of the dates.

如果结果为负，则DATEDIF返回错误，因此在IF的第二个DATEDIF中，反转日期的顺序。

While the test for error might work, this is a bit cleaner:

虽然错误测试可能有效，但这有点清晰：

=IF(created<[Sold On], DATEDIF(created, [Sold On],"d"), DATEDIF([Sold On],created,"d"))

To return a negative when Created is after Sold date:

要在创建时间后返回负数，请在售出日期之后：

=IF(created<[Sold On], DATEDIF(created, [Sold On],"d"), -DATEDIF([Sold On],created,"d"))

#1