Forum Discussion

SindyHanner's avatar
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

Sort By
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 years ago
    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
    ------------------------------
    • SindyHanner's avatar
      SindyHanner
      Qrew Member
      2 years ago

      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

       

       

       



    • SindyHanner's avatar
      SindyHanner
      Qrew Member
      2 years ago
      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
      ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    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's avatar
      MikeTamoush
      Qrew Commander
      2 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        2 years ago
        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
        ------------------------------