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 ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendI 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
------------------------------
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
------------------------------
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 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
------------------------------
- MarkShnier__YouI 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
------------------------------I have static dates for the most part. Always 1-15 or 16-last day of the month. I don't know if that will make a difference. The +Days(1) worked for the September example but depending on the day of the week that the last day of the pay period falls will be a moving target.
Sindy Hanner
Sr NOC Operations Support Specialist
Network Operations Control
(469) 603-0939
HDQ-NOC
- MarkShnier__You@Sindy Hanner Did you try my suggested solution?
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
------------------------------
