How to count number of work days in a date range

bnelson
bnelson Member
in Magic ETL

My first Dojo question. I hope it is worthy. 

I need to create a simple table that shows the number of work days since the begining of the quarter, The total number of work days in the quarter and use those two numbers to calculate the percent of the quarter completed. 

 

Is there a way to count the days in a date range that are not weekends or holidays?

 

Thanks,

Bill

Best Answer

  • AS
    AS Coach
    Answer ✓

    Hi Bill

    We have many table cards that do something similar (Ship Day is roughly equivalent to business days):

    days elapsed.JPG

    In order to do this we have to bring in a dataset in our dataflow which has a calendar of what days count as business days.  Then we can flag the transaction day as a business day or not. We have another column that just shows how many business days there are per month, qtr, and year.  So for every transaction row we can determine how far along the timeline that particular transaction day was.

     

    The key is the extra dataset. We have our own Oracle table for this, but Domo can also get you access to an informative calendar dataset.  It's a long list of days for years back and forward in time, and each day is classified under many categories: weekday, weekend, holiday, day of week, day number of year, etc.  It can be very helpful and you can probably find many uses for it.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    Hi Bill

    We have many table cards that do something similar (Ship Day is roughly equivalent to business days):

    days elapsed.JPG

    In order to do this we have to bring in a dataset in our dataflow which has a calendar of what days count as business days.  Then we can flag the transaction day as a business day or not. We have another column that just shows how many business days there are per month, qtr, and year.  So for every transaction row we can determine how far along the timeline that particular transaction day was.

     

    The key is the extra dataset. We have our own Oracle table for this, but Domo can also get you access to an informative calendar dataset.  It's a long list of days for years back and forward in time, and each day is classified under many categories: weekday, weekend, holiday, day of week, day number of year, etc.  It can be very helpful and you can probably find many uses for it.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • bnelson
    bnelson Member

    Hi Aaron,

    Thanks for the information. Have a good holiday.

    Bill

  • AS
    AS Coach

    Thanks and you're welcome.  Same to you.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MattE_SC
    MattE_SC Member

    @AndrewLacy  Would this solution work for any of our workday count needs?

     

  • Techy007
    Techy007 Member

    Please  tell me how to find the Networking days ie.the working days between the initial date and End Date.

Categories