# To show a negative sign with date in a calculated field

• ### Question

• Hi,

I've been trying to work out on how to calculate the number of days under or over between 2 dates. The two fields are called CompleteBy and CompletedOn, so if the CompletedOn is under it would show a positive and if CompletedOn is over then it would show a negative.

I've got it working for the positive side of things but it returns #NUM! when the CompletedOn date is over:

For example CompleteBy is 19/03/15 and CompletedOn is 20/03/15 it should return -1 but instead it returns #NUM!

Here is the current code:

`=IF(CompletedOn="","",IF(AND((WEEKDAY(CompleteBy,2))<(WEEKDAY(CompletedOn,2)),((WEEKDAY(CompletedOn,2))-(WEEKDAY(CompleteBy,2)))>1),(((DATEDIF(CompletedOn,CompleteBy,"D")+1))-(FLOOR((DATEDIF(CompletedOn,CompleteBy,"D")+1)/7,1)*2)-2),(((DATEDIF(CompletedOn,CompleteBy,"D")+1))-(FLOOR((DATEDIF(CompletedOn,CompleteBy,"D")+1)/7,1)*2)))-1)`

So this formula works to show positive, but need to find a way how to work backwards and show minus.

Any help would be grateful.

Kind regards

James

Friday, March 13, 2015 11:49 AM