cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Calculate working time between 2 timestamp in difference day

Hello,

I'm newbie here please let me know if i miss anything.

I want to calculate working time (minute) between 2 timestamps

but not count time if it not in working hour (9.00 - 18.00)

the data have both finish in same day and finish next day.

If Time A is over 18.00, not count and let start counting on next day till time B

or if Time A start before 9.00, not count and let start counting on 9.00 onward till time B

or if Time A and Time B are in different day, need it to count till 18.00 and break then start counting next day 9.00

 Time A Time B Processing time (Min) 27-06-22 16:32 28-06-22 9:28 27-06-22 16:32 28-06-22 11:28 27-06-22 16:32 28-06-22 0:35 28-06-22 15:13 29-06-22 14:52 30-06-22 11:48 30-06-22 14:10 01-07-22 13:23 01-07-22 13:47 01-07-22 18:11 01-07-22 15:47

I try to use IF and DATEDIFF function in many ways but still can't calculate it correctly

Very appreciate if someone can help

Thank you

1 ACCEPTED SOLUTION
Community Support

Hi @NawaphonTH ,

I suggest you to try this code to create a calcualted column.

``````Processing time (Min) =
VAR _DAYDIFF =
DATEDIFF ( 'Table'[Time A], 'Table'[Time B], DAY )
VAR _REST_MIN = ( 6 + 9 ) * 60
VAR _STARTDATE =
IF (
TIMEVALUE ( 'Table'[Time A] ) < TIME ( 9, 0, 0 ),
DATEVALUE ( 'Table'[Time A] ) + TIME ( 9, 0, 0 ),
IF (
TIMEVALUE ( 'Table'[Time A] ) > TIME ( 18, 0, 0 ),
DATEVALUE ( 'Table'[Time A] ) + TIME ( 18, 0, 0 ),
'Table'[Time A]
)
)
VAR _ENDDATE =
IF (
TIMEVALUE ( 'Table'[Time B] ) < TIME ( 9, 0, 0 ),
DATEVALUE ( 'Table'[Time B] ) + TIME ( 9, 0, 0 ),
IF (
TIMEVALUE ( 'Table'[Time B] ) > TIME ( 18, 0, 0 ),
DATEVALUE ( 'Table'[Time B] ) + TIME ( 18, 0, 0 ),
'Table'[Time B]
)
)
VAR _PROCESSTIME1 =
DATEDIFF ( _STARTDATE, _ENDDATE, MINUTE )
RETURN
_PROCESSTIME1 - _REST_MIN * _DAYDIFF``````

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
New Member

Hi @v-rzhou-msft

It worked totally Perfect!

I'm really appreciated and Thank you very much your help Master.

Community Support

Hi @NawaphonTH ,

I suggest you to try this code to create a calcualted column.

``````Processing time (Min) =
VAR _DAYDIFF =
DATEDIFF ( 'Table'[Time A], 'Table'[Time B], DAY )
VAR _REST_MIN = ( 6 + 9 ) * 60
VAR _STARTDATE =
IF (
TIMEVALUE ( 'Table'[Time A] ) < TIME ( 9, 0, 0 ),
DATEVALUE ( 'Table'[Time A] ) + TIME ( 9, 0, 0 ),
IF (
TIMEVALUE ( 'Table'[Time A] ) > TIME ( 18, 0, 0 ),
DATEVALUE ( 'Table'[Time A] ) + TIME ( 18, 0, 0 ),
'Table'[Time A]
)
)
VAR _ENDDATE =
IF (
TIMEVALUE ( 'Table'[Time B] ) < TIME ( 9, 0, 0 ),
DATEVALUE ( 'Table'[Time B] ) + TIME ( 9, 0, 0 ),
IF (
TIMEVALUE ( 'Table'[Time B] ) > TIME ( 18, 0, 0 ),
DATEVALUE ( 'Table'[Time B] ) + TIME ( 18, 0, 0 ),
'Table'[Time B]
)
)
VAR _PROCESSTIME1 =
DATEDIFF ( _STARTDATE, _ENDDATE, MINUTE )
RETURN
_PROCESSTIME1 - _REST_MIN * _DAYDIFF``````

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors