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

Reply
Visitor

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

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! 

Major Brown Belt

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

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.
White Belt

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

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

Major Brown Belt

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

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.
White Belt

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

It works!  Thank you very much!!!

Yellow Belt

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

Does this beastmode remove weekend days?

White Belt

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

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)

Major Brown Belt

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

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"
Yellow Belt

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

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

Dojo Admin

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

@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,

Dani aka "Mr.Dojo"

Dojo Admin
**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"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!