Difference between Date & Time - with working time or hours of operation- Elapsed time

[kF1894]

I'd like to request help in converting the following formula to DAX/PowerPivot: =(NETWORKDAYS(B2,A2)-2)*0.375+MOD(A2,1)-"09:00"+"18:00"-MOD(B2,1) it would also be great if you could help me determine how to deal with changes in the hours of operation.
I have also not yet figured out how to determine if the Elapsed time is within parameters; such as <=2:00 Hrs, Any and all help is greatly appreciated.

Start Work Date	Create Date	Elapsed time
11/24/2014 17:38	11/24/2014 17:01	0:37
10/14/2014 14:55	10/14/2014 14:27	0:28
10/20/2014 7:32	10/20/2014 6:47	0:45
10/22/2014 15:04	10/22/2014 14:43	0:20
10/23/2014 13:21	10/23/2014 13:14	0:07
10/23/2014 14:05	10/23/2014 13:30	0:35
7/8/2014 10:26	7/2/2014 10:26	12:00
7/30/2014 11:17	7/30/2014 11:17	0:00
7/28/2014 12:20	7/28/2014 12:14	0:06
7/30/2014 6:35	7/30/2014 6:35	0:00
11/20/2014 14:09	11/20/2014 13:25	0:44
11/20/2014 13:10	11/20/2014 12:22	0:48
11/20/2014 14:57	11/20/2014 14:19	0:37
11/13/2014 10:54	11/10/2014 13:46	0:08
12/1/2014 7:22	11/26/2014 15:37	18:45
9/11/2014 8:19	9/8/2014 7:55	3:23

<COLGROUP><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4384" span=2 width=137><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4160" width=130><TBODY>
</TBODY>

 

[barry houdini]

Sorry I don't know anything about DAX/Powerpivot but you can certainly simplify that formula a little, which might make it easier to convert. You can get the same results with this formula

=(NETWORKDAYS(B2,A2)-1)*0.375+MOD(A2,1)-MOD(B2,1)

....although neither version will cater for start/end times outside normal hours

I notice that in your example list the third entry down has both times before 09:00 - wouldn't you want the result to be zero in that case?

 

[scottsen]

I might be able to help... if I knew what that did in like... english

That said, you will need a Calendar table in your Power Pivot model, to somehow how know about weekdays/weekends -- cuz at least I am *mostly* sure, there isn't something directly comparible to NETWORKDAYS in DAX.