cancel
Showing results for
Search instead for
Did you mean:
Member

## [ Useful Days time (in minutes) between 2 dates ]

Hello everyone.

I would like help in calculating the time between opening and closing date of a service order.

I need this time for "useful days" only, ignoring the time outside the useful day range.

I will post the link for the a sample file, that already have a date table with the classification for holidays, weekends and useful days.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: [ Useful Days time (in minutes) between 2 dates ]

Hello @bolabuga

Well, it's long but it should work for you.

```TOTAL MINUTES IN USEFUL DAYS =
VAR OpenDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ) )
VAR CloseDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ) )
VAR SingleDayMin =
IF ( OpenDate = CloseDate,
IF ( LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
DATEDIFF (
'ORDERS DATE'[SERV. ORDER OPENING DATE],
'ORDERS DATE'[SERV. ORDER CLOSING DATE],
MINUTE ) ) )
VAR OpenDateMin =
IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
DATEDIFF (
TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), 0 ),
TIME ( 0, 0, 0 ) + 1,
MINUTE ) )
VAR CloseDateMin =
IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], CloseDate ) = 0, 0,
DATEDIFF (
TIME ( 0, 0, 0 ),
TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), 0 ),
MINUTE ) )
VAR MidDaysMin =
CALCULATE (
SUM ( 'DATE TABLE'[WORKDAYFLAG] ),
FILTER ( 'DATE TABLE', 'DATE TABLE'[DATE] > OpenDate && 'DATE TABLE'[DATE] < CloseDate )
) * 24 * 60
RETURN
OpenDateMin + CloseDateMin + MidDaysMin + SingleDayMin```
2 REPLIES 2
Super User

## Re: [ Useful Days time (in minutes) between 2 dates ]

Hello @bolabuga

Well, it's long but it should work for you.

```TOTAL MINUTES IN USEFUL DAYS =
VAR OpenDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ) )
VAR CloseDate = DATE ( YEAR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MONTH ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), DAY ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ) )
VAR SingleDayMin =
IF ( OpenDate = CloseDate,
IF ( LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
DATEDIFF (
'ORDERS DATE'[SERV. ORDER OPENING DATE],
'ORDERS DATE'[SERV. ORDER CLOSING DATE],
MINUTE ) ) )
VAR OpenDateMin =
IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], OpenDate ) = 0, 0,
DATEDIFF (
TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER OPENING DATE] ), 0 ),
TIME ( 0, 0, 0 ) + 1,
MINUTE ) )
VAR CloseDateMin =
IF ( OpenDate = CloseDate || LOOKUPVALUE ( 'DATE TABLE'[WORKDAYFLAG], 'DATE TABLE'[DATE], CloseDate ) = 0, 0,
DATEDIFF (
TIME ( 0, 0, 0 ),
TIME ( HOUR ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), MINUTE ( 'ORDERS DATE'[SERV. ORDER CLOSING DATE] ), 0 ),
MINUTE ) )
VAR MidDaysMin =
CALCULATE (
SUM ( 'DATE TABLE'[WORKDAYFLAG] ),
FILTER ( 'DATE TABLE', 'DATE TABLE'[DATE] > OpenDate && 'DATE TABLE'[DATE] < CloseDate )
) * 24 * 60
RETURN
OpenDateMin + CloseDateMin + MidDaysMin + SingleDayMin```
Member

## Re: [ Useful Days time (in minutes) between 2 dates ]

Thanks!!!

I did serveral tests and it is working.

Lets see how it will performe in my much larger data base :).

I have some other bussiness rules, like if the service was open and closed at a weekend / holiday, instead of "zero", i should use the total time needed to solve the client problem, but i think i can, from your code, solve the other "rules".

Thanks again!!

## Helpful resources

Announcements

#### New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,771)