Calculation of networkdays

[Anonymous]

@

Hi

Can anybody please help urgently?

Need a simple solution

I want to calcuate the elapsed days i.e. networkdays between date raised and resolved date.

Simply using networkdays() doesnt give the right answer since some of the rows in raised and resolved date columns are right, center or even left aligned.

have tried the below query but doesnt seem to work for all the rows.

if(date([Resolved Date],'M/D/YYYY') > '', NetWorkDays(date([Date Raised],'M/D/YYYY'),date([Resolved Date],'M/D/YYYY')),NetWorkDays(date([Date Raised],'M/D/YYYY'), date(today(),'M/D/YYYY') )) as Elapsed_Days,

[Gysbert_Wassenaar]

since some of the rows in raised and resolved date columns are right, center or even left aligned.

That means some of your dates are not dates, but text strings. You have to convert these in the script in real dates. You can use the alt function to capture the dates than aren't numeric dates already and convert them into dates using the date# function:

LOAD

    date(alt(MyDate,date#(MyDate,'M/D/YYYY'),'M/D/YYYY') as MyDate,

    ...other fields....

FROM ...somewhere...

See this blog post for more information:  Get the Dates Right

[Anonymous]

Hi

Have tried this format to calculate networkdays() but it doesnt seem to be working . Is there any incorrect in my query

if(date(alt([Resolved Date],Date#([Resolved Date],'M/D/YYYY'),'M/D/YYYY'))>'', NetWorkDays(date(alt([Resolved Date],Date#([Resolved Date],'M/D/YYYY'),'M/D/YYYY')),date(alt([Date Raised],Date#([Date Raised],'M/D/YYYY'),'M/D/YYYY'))),
NetWorkDays(today(),date(alt([Date Raised],Date#([Date Raised],'M/D/YYYY'),'M/D/YYYY')))

[Anonymous]

Hi Gysbert

There was a problem with the source data.I had to correct the way it was being exported. The query works fine now