SindyHanner
Qrew Member
2 years ago

# Weekdaysub - Count end date

Hello,

I'm trying to calculate the number of weekdays in a range but don't want the count to stop the day before the end date.

Example date ranges:
9/16-9/30 has 11 weekdays (starts on Friday and ends on Friday)
10/01-10/15 has 10 weekdays (starts on Saturday and ends on Saturday)

Formula:
WeekdaySub([Pay Period End Date],[Pay Period Start Date])

Returns the correct count for the October date range however it does not count correctly for the September range. Not sure what direction to go from here.... Please help!

------------------------------
Sindy Hanner
------------------------------

### 8 Replies

• WeekDay Sub subtracts and counts weekdays in the interval, but not including the last day. So it is not counting your last day. Your October one doesn't matter because the last day is Saturday, but in September it matters.

"WeekdaySub (Date d2, Date d1)

Description: Returns the number of weekdays in the interval starting with d1 and ending on the day before d2"

I think if this may work, but untested:

WeekdaySub([Pay Period End Date] +Days(1), [Pay Period Start Date])

------------------------------
Mike Tamoush
------------------------------
• Perfect! Thank you! I was adding a +1 but to the end which made my Oct 11 M-F days.

Sindy Hanner

Sr NOC Operations Support Specialist

Network Operations Control

(469) 603-0939

HDQ-NOC

• This solution worked. I tried adding the +1 but put it after the second date instead of the first. Thank you so much!

------------------------------
Sindy Hanner
------------------------------
• I think you need to add on an extra day if the last day is not on a weekend.

WeekdaySub([Pay Period End Date],[Pay Period Start Date])
+
If(
DayOfWeek([Pay Period End Date])<>0
and
DayOfWeek([Pay Period End Date])<>6, 1,0)

------------------------------
mark.shnier@gmail.com
------------------------------
• Mark,

I believe the issue is when it ends on a Weekday, we are one day short. If it ends on a weekend, we are good.

Saturday to 2 weeks forward saturday should yield a result of ten: M T W TH F M T W TH F = 10

But Wed 2 weeks forward to the next Wednesday should yield 11, because you are counting both Wednesdays: W TH F M T W TH F M T W = 11

With your formula, I think you want to add an extra day if the final day is a weekday....correct?

I think my formula will also accomplish this? I don't have time to test. Based on history, I expect you to be right but I am hoping maybe this once I am?! :)

------------------------------
Mike Tamoush
------------------------------
• I think my formula is correct but my words were wrong. I have edited my words to say this.

I think you need to add on an extra day if the last day is not on a weekend.

------------------------------