Forum Discussion
Date & Time functions
I am trying to work on a dashboard that gives me the number of hours taken to complete a task. I need to take into consideration US Holidays, Weekends, and the regular workoing hours from 8:00 am to 5:00 pm. If a request comes in on a holiday it should move to the next working day starting at 8:00 am. If a request comes in on a Friday after 5:00 pm, it should start from Monday at 8:00 am. The dashboard needs to be accurate to hours since some requests need to be completed in 2 hours and some have a 3 day limit.
My data is coming from Sharepoint which has the create date and the completion date. PLEASE HELP !
7 Replies
Please provide sample tables and expected output in a table.
Also, read this blog - How to Get Your Question Answered Quickly
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thank you for your reply, for the input i really do not have anything other than the create date, completed date and Completed by from sharepoint. For the output if i can get the number of hours spent on a task i can create the visualization.
The dates (request recieved and completed) are in the following format.
DateReceived DateCompleted CompletedBy 5/14/2020 11:49:00 AM 5/14/2020 03:21:00 PM NS 7/10/2020 10:00:00 AM 7/15/2020 04:11:00 PM JP
nadirS - See my Net Work Duration quick measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M182
2 issues here:
1) it is giveing me a -67 value for the entire column - I do not know why that is
2) if a request is coming in after 5:00 pm on Monday, the start time needs to be updated to 8:00 am on a Tuesday.
Hi , nadirS
Not fully sure what are your issues.
Can you elaborate a bit more, provide some screenshots to explain what you are trying to do.
It would be better if you can share sample files for testing.
Best Regards,
Community Support Team _ Eason
