12 Replies Latest reply: Sep 17, 2014 8:48 AM by Sebastian Pereira

# Date Difference Excluding weekends

Hi all

I want to be able to see how many working days are letf in a month, for example what I have below is;

But I want to remove the count for the weekend days or even just Sunday if possible.

Any ideas?

I want this as a caculated dimension in a pivot chart.

=MONTH_END-today()

Thanks

Aidan

• ###### Re: Date Difference Excluding weekends

Including Weekends.

=Interval(MonthEnd(Today())-Today(),'dd')

Excluding Weekends.

=NetWorkDays(Today(),MonthEnd(Today()))

• ###### Re: Date Difference Excluding weekends

Thansk you this seems to have worked for me, is their anyway I can discount any day that is Sunday on this?

Thanks again,

Aidan

• ###### Re: Date Difference Excluding weekends

Unfortunately networkdays included no possibility for saturday or sunday. If you needs this you need a more complex expression which additionally counted these day. Easier will be to use a special field within the master-calendar like:

if(num(weekday(date)) = 6, 0 ,1) as working_day

and the use a simply sum(working_day) within your chart.

- Marcus

• ###### Re: Date Difference Excluding weekends

What is meant by Discount Sunday?

Please elaborate little more on this !

• ###### Re: Date Difference Excluding weekends

So I want to know how many working days are left in the month, so today being the 16th, we have 13 work days left including today but excluding the last 2 Sundays of the month.

• ###### Re: Date Difference Excluding weekends

=CEIL(Interval(MonthEnd(Today())-Today(),'dd') -  Div(MonthEnd(Today())-Today(),7) + (weekday(Today()) > WeekDay(MonthEnd(Today()))))

• ###### Re: Date Difference Excluding weekends

Thank you kindly, worked perfectly

• ###### Re: Date Difference Excluding weekends

Wow Manish, while you are typing this solution, i was thinking about 2 hours in my solution (on Bottom)...  Your one is very simple and "beautiful", but mine is ugly!!!!  I don't want to correct your solution, the time between the answers is because im in Argentina.

Is amazing to keep learning from people like you!!! Thanks.

• ###### Re: Date Difference Excluding weekends

=NetWorkDays(today(), Month_End)

• ###### Re: Date Difference Excluding weekends

What you mean that you need as calculated dimention?

You want to see like one row by every "non sunday" day? What you have in MONTH_END field?

• ###### Re: Date Difference Excluding weekends

So the below is what I have but I want this to only exclude Sunday and include Saturday

Excluding Weekends.

=NetWorkDays(Today(),MonthEnd(Today()))

• ###### Re: Date Difference Excluding weekends

Ok, you need only the number of days between 2 dates without the sundays. Try:

/*1*/ =NetWorkDays(Today(),MonthEnd(Today())) +

/*2*/ Div(Floor(MonthEnd(Today())-Today())+1,7)+

/*3*/ if(WeekDay(Today())>=WeekDay(MonthEnd(Today())+2)

/*4*/ and WeekDay(Today())<>6,1, 0)

/*1*/ Days Monday to Friday next

/*2*/ Complete following weeks always have one Sat each one

/*3*/ If weekday(today) is grater than weekday(monthend+2), it means that you have left a weekend plus the complete weeks

/*4*/ With this, you don't plus the Sundays

Sorry if my english is not good.