Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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" ) )
Proud to be a Super User!
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")
Hi,
Thanks for getting back to me. I added the code given. Still having the same issue, but the formula makes more sense logically.
What Error Is u getting? and please post expected output
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
@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" ) )
Proud to be a Super User!
Excellent!
Thank you very much Chris 🙂
Try this :
Could you possibly share the csv file?
User | Count |
---|---|
85 | |
75 | |
71 | |
69 | |
55 |
User | Count |
---|---|
98 | |
97 | |
92 | |
78 | |
70 |