12 Replies Latest reply on Sep 28, 2018 12:18 AM by meenu choudhary

# Count number of sundays between two dates

Hey all,

Its been a day that   I am trying to calculate number of sundays between two dates(including the end date) , However this is not the exact problem in  the real scenerio but if this could solved it will be of great help.

Thank You,

Regards,

Manish Chauhan

Tag: Mavis Liu Amyl  (I have been looking at your responses on diff posts ,Please look into it.)

• ###### 1. Re: Count number of sundays between two dates

Hi Manish,

You can try below solution:

Create two parameter , [start date] and [end date]

Create

Number of Sundays

= SUM (IF ([DATE] > [Start Date] and  [DATE] < [End Date]  ) AND (DATENAME('weekday',[Date]) ="Sunday") THEN 1 else 0 end)

1 of 1 people found this helpful
• ###### 2. Re: Count number of sundays between two dates

Hi Meenu.

Thanks for the response , i guess  have not elaborated the problem correctly . I will do it again .

In my case let's say i have dates till today in my data source , I want to count the number of sundays not just for the dates in my dataset but also for the remaining dates of the month which are not even in data set.

For this what I did was, I used today() for start date and got the last date of the current month using a calculation .Now i want to get the number of sundays , between these two dates.

To simplify it further, Just create two parameters for dates irrespective of whether the date column is there in dataset or not , Then just calculate the number of sundays between two dates passed in parameter. (Please do mind the leap years also while trying it. )

If this could be done , then it will be very helpful.

Thank You ,

Regards,

Manish Chauhan

• ###### 3. Re: Count number of sundays between two dates

Hi Manish,

You can try below logic:

number of total Sundays =   int( (DATEDIFF('day',[Order Date],[last date])+DATEPART('weekday',[Order Date])) /7 )

+ (if DATEPART('weekday',[Order Date]) = 1 then 1 else 0 end) - (if DATEPART('weekday',[last date]) = 7 then 1 else 0 end)

You can refer below link for more details descriptions:

NOTE : Replace [Order Date] with your date.

1 of 1 people found this helpful
• ###### 4. Re: Count number of sundays between two dates

Hi Meenu ,

Thanks a lot again for the response .

See this is very close to the solution.

But this  approach is still giving wrong responses at few places. The one I noticed is , if I Change the order date with the parameter of date and  pass a date from year 2020 it is giving me wrong responses. Specially from feb 2020.(I guess the Leap year part is creating some issues here)

You please try doing it and see why it is happening . This is will be of great help.

I appreciate your efforts for the same.

Thank you ,

Regards,

Manish Chauhan

• ###### 5. Re: Count number of sundays between two dates

Hello Manish,

• ###### 6. Re: Count number of sundays between two dates

Hi Meenu ,

We actually do not need dataset in this case , just make one parameter for date  on any dataset and  from the  date passed in parameter  to the end of the month of same date ,calculate the number of sundays.

Thank You ,

Regards,

Manish Chauhan

• ###### 7. Re: Count number of sundays between two dates

Adding to it , the problem here has no dependency on dataset , we are just passing a date in parameter and from that date to the end of the month of the same date we need the number of sundays.

Thank you

• ###### 8. Re: Count number of sundays between two dates

Hope this helps.

Shin

1 of 1 people found this helpful
• ###### 9. Re: Count number of sundays between two dates

Hi Manish!

I have a workbook which I got from one article few months back, I hope it helps you.

Cheers!

Regards

Kashish Bhola

4 of 4 people found this helpful
• ###### 10. Re: Count number of sundays between two dates

Hi Shin ,

Thanks a lot for the help. It worked for me.

Thank you ,

Manish Chauhan

• ###### 11. Re: Count number of sundays between two dates

Hi Meenu ,

The problem have benn solved , you can also have look on the solution in the trailing posts.

Thank you for the efforts , All your inputs really helped to get an understanding of the problem.

Thanks,

Manish Chauhan

• ###### 12. Re: Count number of sundays between two dates

Thanks a lot Manish.

Looking into both the solution provided.