Forum Discussion
Working hours column is only returning true value.
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.
I will also attach a small sample of the data so you can see what is happening.
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" ) )
