2 Replies Latest reply on Dec 8, 2017 2:54 AM by Zhouyi Zhang

    Using IF Statement over datediff

    Dhwani C

      Hi guys,

       

      I'm having some trouble with the following formula.

       

      I am using the below formula to calculate net working days and this works perfectly by itself:

       

      (DATEDIFF("weekday", min([FD Date]) ,MAX([perioddate]))

      - (IF

      DATENAME('weekday',MAX([perioddate]))

      = 'Saturday' OR DATENAME('weekday',min([FD Date]))

      = 'Sunday' THEN 0 ELSE 1 END) - [Holidays])

       

      I need to put an IF statement over the above formula to get

      IF [Stage]="Closed" then

      (DATEDIFF("weekday", min([FD Date]) ,MAX([perioddate]))

      - (IF

      DATENAME('weekday',MAX([perioddate]))

      = 'Saturday' OR DATENAME('weekday',min([FD Date]))

      = 'Sunday' THEN 0 ELSE 1 END) - [Holidays])

       

      This is throwing an aggregate-non aggregate error and I'm not able to resolve this. Thanks for the help!