Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I've the expression like below
count(DISTINCT(if(YTD_FLG3=1 and [App Date]>0 and FAC <>'Y', if(Qte>0 or AI>0 or CarrierDate>0 or DeclinedDate>0 or RiskDate>0 ,
if($(vMinDate)-num(Date#([App Date], 'YYYYMMDD'))>5,IF(Customer#=Customer#1 and Prod=Prod1 and Eff Date=Eff Date1if(Neflag3=1,Key4))))))
Here vMinDate is Earliest date From Qte ,AI,Carrier Date ,Declined Date and [Risk Date] . Subtracting earliest date with [App Date] here we have to consider only network dates. ie if the vminDate is 20160410 and AppDate is 20160401 then the difference has to be 5(Has to exclude Weekend days).
Please anyone help me its an urgent requirement in my project.
Thanks,
Anusha K
You can use Networkdays() function to exlcude saturdays and sundays:
...
if(NetWorkdays(Date#([App Date], 'YYYYMMDD'), $(vMinDate) /*Additional holidays can be put here, comma separated*/ )>5,
...
. ie if the vminDate is 20160410 and AppDate is 20160401 then the difference has to be 5(Has to exclude Weekend days).
Wouldn't this be 6 working days (Networkdays is counting including start and end date)? Maybe add +1 to the start date if you don't want to count the start date itself.