Working hours column is only returning true value.

[Anonymous]

Hi guys,
I'm currently working on a report where I need to measure the time in minutes and seconds between 2 date columns (Task Reported Date, Task Saved Date)  considering only working days and working hours 08:00-18:00 Mon - Fri. 
To achieve this I have created a calculated column with an IF statement which seperates the working days from non working days as a simple Yes or No.
However, only the value for true is returned. Regardless  of the filters. And I am unsure why this is the case. When I add the column to my report, it only shows "No" and not the other results.

Here is the code for the column

Working Hours = IF(OR(FM_Task_Detail[Task Reported Date] < TIME(08,00,00), FM_Task_Detail[Task Reported Date] > TIME(18,00,00)), "No", "Yes")

I will also attach a small sample of the data so you can see what is happening.

[Anonymous]

You are looking in between 8-18 Hrs < I think you should chnage logic :

Working Hours = IF(OR(FM_Task_Detail[Task Reported Date] > TIME(08,00,00), FM_Task_Detail[Task Reported Date] < TIME(18,00,00)), "No", "Yes")

[Anonymous]

Hi,
Thanks for getting back to me. I added the code given. Still having the same issue, but the formula makes more sense logically.

[Anonymous]

What Error Is u getting? and please post expected output

[Anonymous]

The data source for this file is Direct Query, so I wouldn't be able to share unfortunately (Corporate Data).

Also, I'm not receiving any kind of error. The column is just not returning the expected output.
This is an example of what I would expect the output to be.

Anything between Monday to Friday 08:00-18:00, excluding weekends. Working Hours should return "Yes".
Before 08:00 and after 18:00,Including weekends should return "No".

Here is the column code also

Working Hours = IF(OR(FM_Task_Detail[Task Reported Date] < TIME(08,00,00), FM_Task_Detail[Task Reported Date] > TIME(18,00,00)), "No", "Yes")

[ChrisMendoza · Accepted solution]

@Anonymous -

Try:

Column 3 =
IF (
    WEEKDAY ( Table1[TaskReportedDate], 3 ) < 5,
    IF (
        AND (
            TIME ( HOUR ( Table1[TaskReportedDate] ), MINUTE ( Table1[TaskReportedDate] ), 0 )
                > TIME ( 8, 0, 0 ),
            TIME ( HOUR ( Table1[TaskReportedDate] ), MINUTE ( Table1[TaskReportedDate] ), 0 )
                < TIME ( 18, 0, 0 )
        ),
        "yes",
        "no"
    )
)

[Anonymous]

Excellent! 
Thank you very much Chris 🙂 

[Anonymous]

Try this :

if = if(OR(hour(FM_Task_Detail[Task Reported Date])<8 ,hour(FM_Task_Detail[Task Reported Date])>18),

"NO","Yes"

)

[Anonymous]

Could you possibly share the csv file?