2 Replies Latest reply on Nov 21, 2016 9:58 AM by Ramya Sadagopan

# Calculate weekdays within relative date range (on runtime)

I would like to calculate the number of weekdays (Mon thru Fri)  based off of the relative Date selection.

For example I have a relative Date range filter and today's date 1-Nov-2016 Tuesday

if my Date range is current month THEN Number of Weekdays = 1

if my Date range is Current week THEN Number of Weekdays = 2

if my Date range is Previous week THEN Number of Weekdays = 5

Any thoughts on how do i achieve this real time as I change this date range on dashboard?

• ###### 1. Re: Calculate weekdays within relative date range (on runtime)

Hey Ramya,

Do you have a packaged workbook you could share or calculations you've tried?

Thanks,

-Diego

• ###### 2. Re: Calculate weekdays within relative date range (on runtime)

Hi Diego,

Thanks for your response! I already solved this by using the formula -

DATEDIFF('day',[StartDate],[EndDate])) //total days

-

(DATEDIFF('week',[StartDate],[EndDate]),'monday') * 2) //minus weekends

+1

Also you can replace the Start date with MIN[dateAttr) and EndDate with Max[dateAttr] or Today()

So in that case the formula would look like,

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

-

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

+1

OR

DATEDIFF('day',MIN[dateAttr],Today())) //total days

-

(DATEDIFF('week',MIN[dateAttr],Today()),'monday') * 2) //minus weekends

+1