4 Replies Latest reply on Nov 7, 2016 8:48 AM by Ramya Sadagopan

# Calculate number of weekdays within the date filter on runtime

I have a Relative Date filter on my Dashboard and I would like to calculate the number of weekdays based on the date range selected in my filter.

For example, Today's Date: Nov 1st 2016 Tuesday

If my Date filter is Today THEN number of weekdays = 1

If my Date filter is This Week THEN number of weekdays = 2

If my Date filter is This Month THEN number of weekdays = 1

If my Date filter is Previous Week THEN number of weekdays = 5

etc

Any thoughts on how to achieve this in real time as I change the date filter?

Thanks

Ramya

• ###### 1. Re: Calculate number of weekdays within the date filter on runtime

Hi Ramya,

it can be achieved fairly easy as long as you can work with the parameter and not the filters.

Create parameter 'Date Selector' with String list values:

Today, This Week, This Month, Previous Week

and then Calculated field with formula:

case [Date Selector]

when "Today" then 1

when "This Week" then DATEDIFF("day",datetrunc("week",today()),today())

when "This Month" then DATEDIFF("day",datetrunc("month",today()),today())

when "Previous Week" then 5

END

I don't get then Previous Week part - why it's 5 days? From the description you provided it sound like you want to calculate number of weekdays not the business days.. Please elaborate more on those 5 days so we can support you further

br,

Maciek.

---

• ###### 2. Re: Calculate number of weekdays within the date filter on runtime

Maciek,

I am specifically looking for number of Weekdays calculation based on this filter -  I mean Mon - Fri as weekdays. Business days may or may not include the holidays, so to avoid the confusion I am trying to calculate only the weekdays Monday-Friday.

To give you more info, the problem that I am trying to solve is -

I have a total number of tickets that my team completed for given a date range. I need to identify the avg tickets they completed (for only Mon-Fri weekdays with in the date range selected)

• ###### 3. Re: Calculate number of weekdays within the date filter on runtime

Hi!

There's a nice blog post:

showing how (apart of other things) to calculate business days (Mon-Fri)

for a specific period. I think it might give you a hint. If you just adjust

time range from 00:00 to 00:00 I think thus should do the trick.

On Tue, Nov 1, 2016 at 8:51 PM, Ramya Sadagopan <

• ###### 4. Re: Calculate number of weekdays within the date filter on runtime

Thanks for the help!

I was able to solve this by,

DATEDIFF('day',MIN([DATE]),MAX([DATE])) //total days

-

DATEDIFF('week',MIN([DATE]),MAX([DATE]),'monday') * 2 //minus weekends

+1