Forum Discussion
Help with excluding after work hours from a date range
| TICKET_CREATED | TICKET_FINISHED |
| 01-02-2019 08:43:50 | 01-02-2019 08:48:33 |
| 01-02-2019 10:36:11 | 01-02-2019 11:08:40 |
| 01-02-2019 10:48:15 | 04-02-2019 12:18:01 |
| 01-02-2019 10:54:42 | 04-02-2019 10:52:32 |
| 01-02-2019 10:58:35 | 26-02-2019 11:29:12 |
| 01-02-2019 11:16:53 | 01-02-2019 11:17:12 |
| 01-02-2019 11:22:17 | 01-02-2019 15:20:15 |
| 01-02-2019 11:58:51 | 01-02-2019 12:03:57 |
| 01-02-2019 14:56:22 | 01-02-2019 14:56:39 |
| 01-02-2019 15:50:58 | 03-02-2019 17:10:28 |
| 04-02-2019 09:20:52 | 04-02-2019 09:21:40 |
| 04-02-2019 11:54:50 | 04-02-2019 11:55:19 |
| 05-02-2019 08:53:52 | 05-02-2019 11:26:49 |
| 05-02-2019 09:00:04 | 05-02-2019 09:00:33 |
If you check the table above, you have two different dates, but our work hours only range from 08:00-16:00. Can someone help me filtering out excess after hours? Weekend days are also holiday days and are therefore not included in the calculation either.
Hi Rosenmeyer ,
Based on my test, you could refer to below formulas:
Work hour filter = IF(HOUR('Table1'[TICKET_CREATED])>=8&&HOUR('Table1'[TICKET_FINISHED])<=16,1,0)Above formula could filter the work hour:
Week Days = WEEKDAY('Table1'[TICKET_CREATED])WEEKDAY function could return a number from 1 to 7 identifying the day of the week of a date:
https://docs.microsoft.com/en-us/dax/weekday-function-dax
If you problem could not be solved, could you please offer your desired result if possible?
Also you could download the pbix file to have a view.
Regards,
Daniel He
1 Reply
v-danhe-msftMicrosoft Employee
Hi Rosenmeyer ,
Based on my test, you could refer to below formulas:
Work hour filter = IF(HOUR('Table1'[TICKET_CREATED])>=8&&HOUR('Table1'[TICKET_FINISHED])<=16,1,0)Above formula could filter the work hour:
Week Days = WEEKDAY('Table1'[TICKET_CREATED])WEEKDAY function could return a number from 1 to 7 identifying the day of the week of a date:
https://docs.microsoft.com/en-us/dax/weekday-function-dax
If you problem could not be solved, could you please offer your desired result if possible?
Also you could download the pbix file to have a view.
Regards,
Daniel He