Determining if a day/time is outside of business hours

[Anonymous]

Very new to Power BI.  Just trying to nail down a true or false column for determining if a day/time is outside of business hours.  My business hours are Monday to Friday, 9am to 5pm.  I have a column of existing data that reports when a case is opened 'Case'[CreatedDate] - shown as 6/29/2017 2:19:47 PM.  I have a table that also includes all our corporate closure days (holidays).  So I'm trying to create a column that shows

IsAfterHours = If 'Case'[CreatedDate].[Day] = "Saturday OR Sunday" ,True, "False"  OR If 'Case'[CreatedDate].[Time] >5:00:00 PM,<9:00:00, True, False

ideally, I'd also tie in my Afterhours calc to the Holiday table also, but that I have that displaying already. 

Excel makes this sort of easy, =IF(OR(WEEKDAY(CELL)=1,WEEKDAY(CELL)=7),TRUE,IF(OR(CELL<9/24,CELL>17/24),TRUE,""))  I'm looking to create the same thing in Power BI. 

[Ashish_Mathur · Accepted solution]

Hi,

Created this calculated column formula

=IF(WEEKDAY(INT('Case'[CreatedDate]),2)>=6,TRUE(),IF(OR(MOD('Case'[CreatedDate],1)<TIME(9,0,0),MOD('Case'[CreatedDate],1)>TIME(17,0,0)),TRUE(),FALSE()))

Hope this helps.