Count the number of saturdays & sundays between two dates

[#1]

Hi folks,

I've a small issue here. I'm calculating the count of saturdays and sundays between two dates. I've generated master calendar iun my qvw. I used the following expression to calculate the desired:

=count({1<CalendarWeek={Sat,Sun},CalendarDate={">=$(=Date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}CalendarDate)

which is returning zero.

Also I've used the expression =Count({1<CalendarDate={"<=$(=date#('31/10/2014','DD/MM/YYYY'))>=$(=date#('16/03/2013','DD/MM/YYYY'))"}>}CalendarMonth)

to calculate the number of months between two dates, which is returning 595(no way near the result.

can you guys let me know where I'm going wrong. I've attached my qvw as well.

Thanks in advance

Surendra

[Anonymous]

Your second expression would count the number of days between the dates. There is apparently one CalendarMonth value for each date so 31 times Jan for january etc.

I would suggest creating a flag in your calendar where you flag all Saturdays and Sundays as 1 under a field called WeekendFlag for example. Then you can just sum up the field WeekendFlag for your date range.

[Anonymous]

Yes Johannes is correct,

you can do it by using this expression

in  load script.

if (weekday(yourdatefield)='Sat' or weekday(yourdatefield)='Sun' , 'A')  AS Weekendflag

then use this expression count(Weekendflag)

Regards,

anant

[nirav_bhimani]

HI,

Try this Expression

=count({1<CalendarWeek={'Sat','Sun'},CalendarDate={">=$(=Date#('01-01-2000','DD/MM/YYYY'))<=$(=Date#('31/01/2000','DD-MM-YYYY'))"}>}CalendarDate)

It will display correct result for that month.

Now replace those date with your date .

Regards,

Nirav Bhimani

[#5]

Hi All,

Thanks for your quick response.

@Johannes,anantmaxx :

I've created the weekend flag as you said. But when I use the expression:

=sum({1<CalendarDate={">=$(=date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}Weekendflag) in the front end it's not working. There is no error in the script and the 'Weekendflag' field is displaying 1 for all saturdays and sundays.

@Nirav:

It's working absolutely fine with date range you've taken.But when I enter my date fields it's displaying wrong value.

=count({1<CalendarWeek={'Sat','Sun'},CalendarDate={">=$(=Date#('01-01-2013','DD/MM/YYYY'))<=$(=Date#('31/12/2014','DD-MM-YYYY'))"}>}CalendarDate).

Seems like a problem with date interpretation.

Because when I enter year range instead of date range , it's showing correct result for the above expressions , we've discussed.

[nirav_bhimani]

Hi,

try Following Expression,

if (weekday(yourdatefield)='Sat' or weekday(yourdatefield)='Sun' , 1, 0)  AS Weekendflag

=sum({1< Weekendflag={1} ,CalendarDate={">=$(=date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}Weekendflag)

Regards,

Nirav Bhimani

[#7]

Hi Nirav,

That is returning zero.

[#8]

Try the following Formula

The Following will give you count of all Mondays

sum(

  if(

  mod((leaveTo-leaveFrom),7)

  >=

  if(

  (weekday(leaveTo)-/* Change for day of week */0)<0,

  weekday(leaveTo)-/* Change for day of week */0+7,

  weekday(leaveTo)-/* Change for day of week */0)

  ,floor((leaveTo-leaveFrom)/7)+1

  ,floor((leaveTo-leaveFrom)/7))

  )