Help with excluding after work hours from a date range

[Rosenmeyer]

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.

[v-danhe-msft · Accepted solution]

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