Forum Discussion

DanielFuhriman's avatar
DanielFuhriman
Qrew Member
5 years ago

Duration formula that only counts Monday-Saturday, excludes Sundays.

Hi! I was wondering if anyone knew a duration formula that only counts Monday- Saturday, excluding Sundays? I am aware of the formula WeekdaySub that counts only weekdays.  Any help would be app...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
5 years ago
Just for clarity, you don't mean duration, right?  You mean to count the number of days between two dates based on your 6 day week?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
DanielFuhriman's avatar
DanielFuhriman
Qrew Member
5 years ago
Yes. I am working on an app that tracks time off request. We are wanting to know what the difference is between two dates excluding Sundays.

I am not sure if this is possible or not, but to complicate matters worst. The manager only wants to count Saturdays off as a half a day. If this 2nd step is not do able, the first request is fine.

------------------------------
Daniel Fuhriman
------------------------------
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    So, just for clarity, WeekdaySub is a function, not a formula in and of itself. A formula would be how you end up using that - or other - functions to perform your calculation.

    For your example, you'll need to do several different calculations to come up with your final solution. If you're using this as part of Time Off calculations, I have to think there would be a better/easier way. Can you put up a screen-shot of your request form and explain a bit more about what this calculation is supposed to represent? Maybe we can figure out an alternative solution.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
    • DanielFuhriman's avatar
      DanielFuhriman
      Qrew Member
      5 years ago
      The current formula that I am using is: ToDays([To Date]-[From Date])+1

      It would be nice to be able to exclude Sunday's if possible, so in this example it would only show 7 days taken off instead of 8. Another example would be if someone took off from 1-12-21 to 1-26-21 it would show 13 days were take off since two Sundays were in the timeframe.


      ------------------------------
      Daniel Fuhriman
      ------------------------------
      • BlakeHarrison's avatar
        BlakeHarrison
        Qrew Captain
        5 years ago
        I realize your setup is definitely easier for your users, but have you considered other options for how your requests might work? I recently just setup a PTO process and ended up with a form that allows the user to enter different data for each day of the week. So, for example, they need a 1/2 day off on Thursday, but then a full day off on Friday. Here's how I laid that out.




        The benefit to this - or something similar - is that you can more easily determine PTO used and provide a more flexible layout for the user. Granted, the user is now limited to a single week at a time, but every solution will have some caveats.

        For this setup, I have an approval process that kicks off a Pipeline that creates individual Approved PTO records for each day the user has been approved for. I then do Summary fields up to their Annual record to keep track of how much of each PTO type they've used, how much is left, etc.


        ------------------------------
        Blake Harrison
        bharrison@datablender.io
        DataBlender - Quick Base Solution Provider
        Atlanta GA
        404.800.1702 / http://datablender.io/
        ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      Daniel
      There was a similar question posted here. https://community.quickbase.com/communities/community-home/digestviewer/viewthread?MessageKey=8aa8e191-3f48-4079-a4d0-dd021df8dcbe&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer#bm8aa8e191-3f48-4079-a4d0-dd021df8dcbe

      That formula could probably be modified to work for you.  Contact me directly by email to discuss.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------