locked
To show a negative sign with date in a calculated field RRS feed

  • 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

Answers

  • Its throwing the error for negative numbers. Try and work your logic around error handling. Something like below.

    =IF(ISERROR(DATEDIF(Modified,[Created],”d”)),”LATE”,DATEDIF(Modified,[Created],”d”))

    Hope this helps.


    Please mark as 'Propose as Answer' or 'Vote' as appropriate.
    Regards,
    Sagir Kazi
    orgchartgoogleapi.codeplex.com

    Friday, March 13, 2015 7:21 PM