cancel
Showing results for
Did you mean:
bolabuga 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```
bolabuga 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!!

Announcements   