I'm trying to calculate number of days without weekends, can someone please help?

Nino
Nino Member
in Magic ETL

Hi! 

 

I'm currently trying to calculate the number of days between the creation of an application and the current day, but I need to subtract weekends, does anyone know how? I do know I can do it under beast mode DateDiff, but I don't know how to remove the weekends.

 

Thanks you!

Best Answers

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    The lack of sleep was getting to me last night (In my side of the planet).

     

    Here is a Simplified version of the beast mode :

     

    (DATEDIFF(DATE('End Date'),DATE('Start Date')) -
    ((WEEKOFYEAR('End Date')-WEEKOFYEAR('Start Date'))*2)-
    (CASE WHEN DAYOFWEEK('End Date')  = 7  THEN 1 ELSE 0 END) -
    (CASE WHEN DAYOFWEEK('Start Date') = 1 THEN 1 ELSE 0 END))

     

    For start daste of 2017/06/01 and end date of 2017/06/23 the result is 16 work days. You may have to adjust to your calculation requirements (IF you want to take the start date into acount, when does your week start,....)

     

    Please try this one, i confirm that the version i gave you early was bugged for dates out of the same year week.

     

    Hope it Helps!

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • RGranada
    RGranada Contributor
    Answer ✓

    Was not counting with dates in diferent years

     

    Try like this:

     

    (DATEDIFF(DATE('EndDate'),DATE('StartDate')) -
    ((WEEKOFYEAR('EndDate')-WEEKOFYEAR(StartDate))+((YEAR('EndDate')-YEAR('StartDate'))*52))*2
    -
    (CASE WHEN DAYOFWEEK('StartDate') = 7 THEN 1 ELSE 0 END) -
    (CASE WHEN DAYOFWEEK('EndDate') = 1 THEN 1 ELSE 0 END))

     

    It will have some degree of problems transversing years with 53 weeks, but let's see how it goes!

     

    Hope it helps.

     

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member
    Answer ✓

    Hi Shane,

    Not quite.  I ended up having Support help me with a solution.  

     

    This version has been working.  Using DateCreated as your start date and Invoice Date as your end date, you should be able to apply the same logic... 

     

    AVG(CASE WHEN `DateCreated` = `InvoiceDate` THEN 0 ELSE DATEDIFF(

    CASE -- move end date to friday if on weekend
    WHEN dayofweek(`InvoiceDate`) = 1 THEN SUBDATE(`InvoiceDate`, INTERVAL 1 DAY)
    ELSE `InvoiceDate`
    END
    ,

    CASE -- move start date to monday if on weekend
    WHEN dayofweek(`DateCreated`) = 7 THEN ADDDATE(`DateCreated`, INTERVAL 2 DAY)
    WHEN dayofweek(`DateCreated`) = 1 THEN ADDDATE(`DateCreated`, INTERVAL 1 DAY)
    ELSE `DateCreated`
    END)

    -

    (-- finds number of weeks and then times by 2 to get number of weekend days
    FLOOR((DATEDIFF(
    CASE -- move end date to friday if on weekend
    WHEN dayofweek(`InvoiceDate`) = 7 THEN SUBDATE(`InvoiceDate`, INTERVAL 1 DAY)
    WHEN dayofweek(`InvoiceDate`) = 1 THEN SUBDATE(`InvoiceDate`, INTERVAL 2 DAY)
    ELSE `InvoiceDate`
    END,

    CASE -- move start date to monday if on weekend
    WHEN dayofweek(`DateCreated`) = 7 THEN ADDDATE(`DateCreated`, INTERVAL 1 DAY)
    WHEN dayofweek(`DateCreated`) = 2 THEN SUBDATE(`DateCreated`, INTERVAL 1 DAY)
    WHEN dayofweek(`DateCreated`) = 3 THEN SUBDATE(`DateCreated`, INTERVAL 2 DAY)
    WHEN dayofweek(`DateCreated`) = 4 THEN SUBDATE(`DateCreated`, INTERVAL 3 DAY)
    WHEN dayofweek(`DateCreated`) = 5 THEN SUBDATE(`DateCreated`, INTERVAL 4 DAY)
    WHEN dayofweek(`DateCreated`) = 6 THEN SUBDATE(`DateCreated`, INTERVAL 5 DAY)
    ELSE `DateCreated`
    END

    ) / 7)) * 2)

    END)

  • Gimli
    Gimli Domo Employee
    Answer ✓

    Hello All, 

    Here is a great article in the knowledge base that has the same information. 
    http://knowledge.domo.com?cid=datediff

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

Answers

  • RGranada
    RGranada Contributor

    Hi,

     

    Try this beastmode :

     

    CASE WHEN YEARWEEK(`EDate`) = YEARWEEK(`SDate`) THEN ((DATEDIFF(DATE(`EDate`),DATE(`SDate`)) - (CASE WHEN DAYOFWEEK(`SDate`) = 1 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`EDate`) = 7 THEN 1 ELSE 0 END))) ELSE ((DATEDIFF(DATE(`EDate`),DATE(`SDate`)) - DAYOFWEEK(`EDate`)-(7-DAYOFWEEK(`SDate`)))*2) + DAYOFWEEK(`EDate`) + (7-DAYOFWEEK(`SDate`)) - (CASE WHEN DAYOFWEEK(`EDate`) = 7 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`SDate`) = 1 THEN 1 ELSE 0 END)-2 END

     

    Replace `EDate` with your end date and `SDate` with your start date. It will give you the number of business days between dates, i think that's what you are trying to get.

     

    Hope it Helps

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    Hi Richard,

     

    Coincidentally, I had the same question... How many "business days" between start date and end date...  But it doesn't seem to be working as my # of days goes up instead of down.

     

    Additionally, I'm trying to get to the average # of business days between start date & end date in a time period (like quarter) and am stumped there as well.

     

    Any help is greatly appreciated.

     

    Micah

  • RGranada
    RGranada Contributor
    Are you applying the creation of the application date to the start date, and current date to end date ? I have this beast mode working on several scenarios...

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    Hi Richard,

     

    Thanks for your quick reply.

     

    Yes, Date Created is my start date, but Invoice Date is actually my end date.  The transactions are unrelated to today's date, as I'm looking to track "Lead Time" for orders to be completed.

     

    Hopefully that helps clarify.  Would that make a difference in the calculation?

     

    Micah

  • RGranada
    RGranada Contributor
    I think that should have worked .
    Please verify the beast mode again :

    CASE WHEN YEARWEEK(`End Date`) = YEARWEEK(`Start Date`) THEN ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END))) ELSE ((DATEDIFF(DATE(`End Date`),DATE(`Start Date`)) - DAYOFWEEK(`End Date`)-(7-DAYOFWEEK(`Start Date`)))*2) + DAYOFWEEK(`End Date`) + (7-DAYOFWEEK(`Start Date`)) - (CASE WHEN DAYOFWEEK(`End Date`) = 7 THEN 1 ELSE 0 END) - (CASE WHEN DAYOFWEEK(`Start Date`) = 1 THEN 1 ELSE 0 END)-2 END


    I'm writing from my phone I may have missed something.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    Yup, verified.  My only changes were your Start Date is my DateCreated, and End Date is my InvoiceDate.

     

    It may be in my application of "Average" that's throwing the numbers off?  I'm just aggregating the Average once I add the field to my graph.  Should we be averaging the results within the beast mode?

  • RGranada
    RGranada Contributor
    From my point of view your average application is ok.

    What do you mean by "But it doesn't seem to be working as my # of days goes up instead of down“

    Your lead time should be the number of business days since creation of order to invoice date, am I right?

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    Hi Richard,

     

    Thanks again for your quick reply.

     

    Yes, you are correct, the lead time should be the number of business days from creation to invoice... 

     

    I applied a simple calculation at first, showing the average lead time including weekends, and the numbers were significantly lower.  Currently, with the weekends removed formula, min average time is 5 days and max at 13.1.  

    When I apply the simple formula AVG(DATEDIFF(`InvoiceDate`,`DateCreated`)), min average is 4.5, and max average is down around 9.2.  

     

    I would think that not counting the weekend days against us, our average would go down.  

     

    Hopefully that helps.  Please let me know if there's any other info I can provide to help clarify.

     

    Thanks!

    Micah

  • Micah
    Micah Member

    Or...

     

    Does this formula make more sense to get to the average...

     

    sum(DATEDIFF(`InvoiceDate`,`PromiseDate`))

    /

    COUNT(DATEDIFF(`InvoiceDate`,`PromiseDate`))

     

    And can we apply the "less weekends" formula to this?

  • Nino
    Nino Member

    Yes, I do! I have the start date as the app created date and the end date as today! 

     

    Thank you so much for the help! 

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

     

    The lack of sleep was getting to me last night (In my side of the planet).

     

    Here is a Simplified version of the beast mode :

     

    (DATEDIFF(DATE('End Date'),DATE('Start Date')) -
    ((WEEKOFYEAR('End Date')-WEEKOFYEAR('Start Date'))*2)-
    (CASE WHEN DAYOFWEEK('End Date')  = 7  THEN 1 ELSE 0 END) -
    (CASE WHEN DAYOFWEEK('Start Date') = 1 THEN 1 ELSE 0 END))

     

    For start daste of 2017/06/01 and end date of 2017/06/23 the result is 16 work days. You may have to adjust to your calculation requirements (IF you want to take the start date into acount, when does your week start,....)

     

    Please try this one, i confirm that the version i gave you early was bugged for dates out of the same year week.

     

    Hope it Helps!

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    Hi Richard,

     

    Thanks again for your help on this.

     

    It seems to be working, except when we cross over to a new year.  For example, if a "Start Date" is in December 2015, and the "End Date" occurs in January 2016, the numbers are greatly inflated.

     

    Any ideas?  

     

    Micah

  • RGranada
    RGranada Contributor
    Answer ✓

    Was not counting with dates in diferent years

     

    Try like this:

     

    (DATEDIFF(DATE('EndDate'),DATE('StartDate')) -
    ((WEEKOFYEAR('EndDate')-WEEKOFYEAR(StartDate))+((YEAR('EndDate')-YEAR('StartDate'))*52))*2
    -
    (CASE WHEN DAYOFWEEK('StartDate') = 7 THEN 1 ELSE 0 END) -
    (CASE WHEN DAYOFWEEK('EndDate') = 1 THEN 1 ELSE 0 END))

     

    It will have some degree of problems transversing years with 53 weeks, but let's see how it goes!

     

    Hope it helps.

     

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Micah
    Micah Member

    It works!  Thank you very much!!!

  • ShaneB
    ShaneB Member

    Does this beastmode remove weekend days?

  • Micah
    Micah Member
    Answer ✓

    Hi Shane,

    Not quite.  I ended up having Support help me with a solution.  

     

    This version has been working.  Using DateCreated as your start date and Invoice Date as your end date, you should be able to apply the same logic... 

     

    AVG(CASE WHEN `DateCreated` = `InvoiceDate` THEN 0 ELSE DATEDIFF(

    CASE -- move end date to friday if on weekend
    WHEN dayofweek(`InvoiceDate`) = 1 THEN SUBDATE(`InvoiceDate`, INTERVAL 1 DAY)
    ELSE `InvoiceDate`
    END
    ,

    CASE -- move start date to monday if on weekend
    WHEN dayofweek(`DateCreated`) = 7 THEN ADDDATE(`DateCreated`, INTERVAL 2 DAY)
    WHEN dayofweek(`DateCreated`) = 1 THEN ADDDATE(`DateCreated`, INTERVAL 1 DAY)
    ELSE `DateCreated`
    END)

    -

    (-- finds number of weeks and then times by 2 to get number of weekend days
    FLOOR((DATEDIFF(
    CASE -- move end date to friday if on weekend
    WHEN dayofweek(`InvoiceDate`) = 7 THEN SUBDATE(`InvoiceDate`, INTERVAL 1 DAY)
    WHEN dayofweek(`InvoiceDate`) = 1 THEN SUBDATE(`InvoiceDate`, INTERVAL 2 DAY)
    ELSE `InvoiceDate`
    END,

    CASE -- move start date to monday if on weekend
    WHEN dayofweek(`DateCreated`) = 7 THEN ADDDATE(`DateCreated`, INTERVAL 1 DAY)
    WHEN dayofweek(`DateCreated`) = 2 THEN SUBDATE(`DateCreated`, INTERVAL 1 DAY)
    WHEN dayofweek(`DateCreated`) = 3 THEN SUBDATE(`DateCreated`, INTERVAL 2 DAY)
    WHEN dayofweek(`DateCreated`) = 4 THEN SUBDATE(`DateCreated`, INTERVAL 3 DAY)
    WHEN dayofweek(`DateCreated`) = 5 THEN SUBDATE(`DateCreated`, INTERVAL 4 DAY)
    WHEN dayofweek(`DateCreated`) = 6 THEN SUBDATE(`DateCreated`, INTERVAL 5 DAY)
    ELSE `DateCreated`
    END

    ) / 7)) * 2)

    END)

  • Gimli
    Gimli Domo Employee
    Answer ✓

    Hello All, 

    Here is a great article in the knowledge base that has the same information. 
    http://knowledge.domo.com?cid=datediff

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

    Thanks for the help!! I do not see an "accept as solution"

  • [Deleted User]
    [Deleted User] Member

    @ShaneB,

     

    Since you are not the original author you would not be able to see the Accept as Solution button. 

    I went ahead and added credit for @Micah and @ilikenno for their posts.

     

    Regards,

  • user09445
    user09445 Member

    this is super helpful! thanks for posting.

Categories