Weekdaysub - Count end date

Question

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
------------------------------

miketamoush · Answer

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
------------------------------

markshnier__you · Answer

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 (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

miketamoush · Answer

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 = 10But 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 = 11With 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------------------------------

markshnier__you · Answer

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.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

sindyhanner · Answer

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

Forum Discussion

Weekdaysub - Count end date