5 Replies Latest reply on Dec 19, 2016 11:20 AM by Shinichiro Murakami

# Help required on No. of days calculation

Hi All,

My requirement is to show the no. of days by calculating the difference between start and end date after excluding saturdays and sundays that fall between those 2 dates.

For Ex: If start date and end date are same then No. of days should be 1, else the difference between start and end date excluding saturday and sunday.

Below is the desired result we are expecting:

As per the above screenshot, I need a calculation to show 19 days difference between 28-Nov & 22-dec after excluding Saturday and Sundays. But my current dashboard is showing 23 days (which is actual difference).

Thanks in advance...

• ###### 1. Re: Help required on No. of days calculation

Hello Srinija!

I think this formula will get you the numbers you're looking for:

AVG(

DATEDIFF('weekday', [start_date], [end_date])

- 2 * (DATEPART('week', [end_date]) - DATEPART('week', [start_date]))

+ (IF DATENAME('weekday', [end_date]) = 'Saturday' OR DATENAME('weekday', [start_date]) = 'Sunday'

THEN 0

ELSE 1

END))

This KB article might be helpful too if you want to explore some more options with excluding days:

Thanks!

• ###### 2. Re: Help required on No. of days calculation

Hi Sanchez

Its not working correctly , when we have different years.could you please revise the logic and send us

In the below screen shot it should give 29 days but it is giving 135 .Please check

• ###### 3. Re: Help required on No. of days calculation

Hi Praveen

Go this link.

Thanks,

Shiin

1 of 1 people found this helpful
• ###### 4. Re: Help required on No. of days calculation

Hi Shiin

The code which you have sent is perfectly worked for us

Your help is much appreciated

Thanks alot

Regards

Praveen Reddy

• ###### 5. Re: Help required on No. of days calculation

Hi Praveen

Great to know it works as common solution.

Thank you for the feedback.

Could you mark my answer as correct, if you think it's solved.

Thanks,

Shin

1 of 1 people found this helpful