Forum Discussion
Exclude Weekend and working hours
Dear Team
appreciate your assist to upgrade below formula to exclude Weekend and working hours attached test file
i have been used the below formula
Time Diff2 = VAR PreviousTime=TOPN(1,Filter(Table1,Table1[Orderno]=earlier(Table1[Orderno])&&Table1[LogDatetime]<earlier(Table1[LogDatetime])),[LogDatetime],desc)
RETURN
DATEDIFF(MINX(PreviousTime,Table1[LogDatetime]),Table1[LogDatetime],SECOND)
Duration = VAR TotalSeconds=SUM(Table1[Time Diff2]) VAR Days =TRUNC(TotalSeconds/3600/24) VAR Hours = TRUNC((TotalSeconds-Days*3600*24)/3600) VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60) VAR Secs = CEILING(MOD(TotalSeconds,60),1) return IF(DAYS=0,"",IF(DAYS>1,DAYS&" days "))&IF(Hours<10,"0"&Hours,Hours)&" hours "&IF(Mins<10,"0"&Mins,Mins)&" minutes "&IF(Secs<10,"0"&Secs,Secs)&" seconds "
10 Replies
Dear Team need your urgnet help
Hi shado26
As I understand, when excluding Weekend and working hours, the second row of "Time Diff2" would change to be 0 instead of 38.
Since excluding Weekend and working hours means excluding any time in Saturday and Sunday, along with time period of 8:00 -18:00 of each day from Monday to Friday.
Is my understanding right?
Best Regards
Maggie
actually our Weekends are Saturday and Friday , working hours between 9 AM till 6 PM
the above formula are measure the time difference between actions but weekend and working hours are not exclude
can we do something about this ???
Hi shado26
Yes, it can be achieved, but it will take sometime for me to give proper answer in your scenario.
You could refer to this link which is similar to your case to make a try on your site.
https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255/highlight/true
I'll come back as soon as I complete it.
Best Regards
Maggie
