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

# Using IF Statement over datediff

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!

• ###### 1. Re: Using IF Statement over datediff

Wrap [Stage] in ATTR(). i.e. ATTR([Stage])

1 of 1 people found this helpful
• ###### 2. Re: Using IF Statement over datediff

Hi, Dhwani

Please try

(DATEDIFF("weekday", min(IF [Stage]="Closed" then [FD Date] end) ,MAX(IF [Stage]="Closed" then [perioddate] end))

- (IF

DATENAME('weekday',MAX(IF [Stage]="Closed" then [perioddate]))

= 'Saturday' OR DATENAME('weekday',min(IF [Stage]="Closed" then [FD Date] end))

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

ZZ