5 Replies Latest reply on Feb 22, 2016 7:37 AM by Daniel Vincent

# Counting the number of Sundays in a date range?

I need to determine the number of working days in a day range, but my company operates 6 days a week. Mon-Sat. So I was thinking of just using the normal datediff function but then subtracting the number of Sundays from that range so I just get the 6 days a week I need.

Any suggestions?

Thanks

• ###### 1. Re: Counting the number of Sundays in a date range?

Something like this work for your needs?

IF DATEPART('weekday', [Order Date])=1 THEN 0 ELSE 1 END

• ###### 2. Re: Counting the number of Sundays in a date range?

How can or should I modify the calculated field I have created.

I currently have:

int([Amount])/DATEDIFF('day', [Start], [Ready])

That fields give me \$s per day of work. In the denominator, I need to credit back the number of Sundays.

Thanks again

• ###### 3. Re: Counting the number of Sundays in a date range?

Here is a calculation to exclude Sunday.

[d1 without Sunday]

if datename('weekday',[Date1])="Sunday" then [Date1]+1 else [Date1] END

[d2 without Sunday]

if datename('weekday',[Date2])="Sunday" then [Date2]-1 else [Date2] END

[days in between]

([d2 without Sunday]+1-[d1 without Sunday])

-int(([d2 without Sunday]+1-[d1 without Sunday])/7)

[days in between (direct)]

((if datename('weekday',[Date2])="Sunday" then [Date2]-1 else [Date2] END)+1

- (if datename('weekday',[Date1])="Sunday" then [Date1]+1 else [Date1] END))-

int(((if datename('weekday',[Date2])="Sunday" then [Date2]-1 else [Date2] END)+1

- (if datename('weekday',[Date1])="Sunday" then [Date1]+1 else [Date1] END))/7)

Thanks,

Shin

9.0 attached

• ###### 4. Re: Counting the number of Sundays in a date range?

Hi Jeffrey,

You can use WINDOW_COUNT to calculate the number of weekdays in a range.

• ###### 5. Re: Counting the number of Sundays in a date range?

To get your denominator I would do something like this...

DATEDIFF('weekday',[Start Date], [End Date]) - (DATEPART('week',[End Date]) - DATEPART('week',[Start Date]))

Attaching 9.1 workbook for you to review.