cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Microsoft 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?

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft 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?

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.