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.


[TABLE="width: 304"]
<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>[TR]
[TD="class: xl65, width: 137, bgcolor: transparent"]Start Work Date[/TD]
[TD="class: xl65, width: 137, bgcolor: transparent"]Create Date[/TD]
[TD="width: 130, bgcolor: transparent"]Elapsed time[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/24/2014 17:38[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/24/2014 17:01[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:37[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]10/14/2014 14:55[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/14/2014 14:27[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:28[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]10/20/2014 7:32[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/20/2014 6:47[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:45[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]10/22/2014 15:04[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/22/2014 14:43[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:20[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]10/23/2014 13:21[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/23/2014 13:14[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:07[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]10/23/2014 14:05[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/23/2014 13:30[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:35[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]7/8/2014 10:26[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7/2/2014 10:26[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]12:00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]7/30/2014 11:17[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7/30/2014 11:17[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]7/28/2014 12:20[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7/28/2014 12:14[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:06[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]7/30/2014 6:35[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7/30/2014 6:35[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:00[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 14:09[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 13:25[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:44[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 13:10[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 12:22[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:48[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 14:57[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/20/2014 14:19[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:37[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/13/2014 10:54[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/10/2014 13:46[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]0:08[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]12/1/2014 7:22[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/26/2014 15:37[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]18:45[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]9/11/2014 8:19[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]9/8/2014 7:55[/TD]
[TD="class: xl67, bgcolor: transparent, align: center"]3:23[/TD]
[/TR]
</TBODY>[/TABLE]

 

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