business days formula to show negatives

[CRogers22]

I need my business day formula to show negatives. Currently It only pulls positive number of days and the report I'm working on could have negative days between. My current formula is below.

IIF([Initial Disclosure Date]>[Intent_To_Proceed_Date],
Null(),
1 +
((DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days")*5 - ([IDD]-[ITPD])*2) / 7) +
IIF([IDD]==6,-1,0) +
IIF([ITPD]==0,-1,0)
)
 

Is there a way to update the formula to capture Negative number of days?

Thank in advance.

[binuacs]

@CRogers22 If your Initial Disclosure Date date is greater than Intent_To_Proceed_Date then you should see negative value, may be you can test few scnario where initial discolsure date > intetnt to proceed date

[KGT · Accepted solution]

Just put your DateTimeDiff formula in place of the Null(). Currently, if Disclosure Date > Intent to proceed date, then Null() will be returned.

DateTimeDiff will return negatives.

[CRogers22]

Thank you both for your time

[CRogers22]

@KGT 

I understand why this would work but I cant get the formula to work. I must be doing something wrong. I keep getting Parse errors but the formula doesn't show where the error is as usual.

From

IIF([Initial Disclosure Date]>[Intent_To_Proceed_Date],
Null(),
1 +
((DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days")*5 - ([IDD]-[ITPD])*2) / 7) +
IIF([ITPD]==6,-1,0) +
IIF([IDD]==0,-1,0)
)

To

IIF( ((DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days")*5 - ([IDD]-[ITPD])*2) / 7) +
IIF([ITPD]==6,-1,0) +
IIF([IDD]==0,-1,0)

Any Idea what is wrong?

[KGT · Accepted solution]

Try this. I'm not sure of your exact expected output and so the formula may need work:

From

IIF([Initial Disclosure Date]>[Intent_To_Proceed_Date],
Null(),
1 +
((DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days")*5 - ([IDD]-[ITPD])*2) / 7) +
IIF([ITPD]==6,-1,0) +
IIF([IDD]==0,-1,0)
)

To:

From

IIF([Initial Disclosure Date]>[Intent_To_Proceed_Date],
(DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days"),
1 +
((DateTimeDiff([Intent_To_Proceed_Date],[Initial Disclosure Date],"days")*5 - ([IDD]-[ITPD])*2) / 7) +
IIF([ITPD]==6,-1,0) +
IIF([IDD]==0,-1,0)
)

[CRogers22]

@KGT 

Thank you very much! that worked!