Exclude Weekends in date duration calculation ?

Mpumelelo Tsabedze shared this question 2 months ago
Answered

I would like to calculate the duration of days between 2 dates values but exclude weekend days ?

If possible in Yellowfin

Best Answer
photo

Hi Mpumelelo,

So I can understand your request a bit better, are you trying to create a calculated field that works out that number of days? We do have that feature which supports freehand SQL and custom functions.

Kind regards,

Chris

Comments (1)

photo
1

Hi Mpumelelo,

So I can understand your request a bit better, are you trying to create a calculated field that works out that number of days? We do have that feature which supports freehand SQL and custom functions.

Kind regards,

Chris

photo
1

Thanks Chris - got it just getting another error now when trying this

Error retrieving results

Form not found: 'null'. Note that form names are case sensitive and form names with spaces or special characters must be enclosed in backticks (`)

photo
1

Hi Mpumelelo,

What's the function or calculation you're using? Are you able to provide a screenshot, please?

Kind regards,

Chris

photo
1

Hi Chris,

please see below

dbfn('cast',dbfn('substring','1234555123444512333451222345111234500123450123455',(dbfn('datepart','dw',`Last Resolved Date`)-1)*7 + (dbfn('datediff','DD',`Submit Date`,`Last Resolved Date`)%7)+1,1),'as int')-1

photo
1

Hi Mpumelelo,

Hmm, it's pretty hard to tell when you're using a custom function why exactly that causes an error. Has it worked elsewhere?

It could be the part 'as int' which isnt using the back tick: `

Kind regards,

Chris

photo
1

Hi Chris,

It was the referenced field - had to add the Table name as well ....

However the results are different when applied on SR than on DB - could there be some setting that would cause offset perhaps ?

photo
1

Hi Mpumelelo,

Thanks for letting me know.

There shouldn't be an offset but you might need to modify the formula to counteract the difference in the results. I hope that's possible for you.

Kind regards,

Chris

photo