Working hours between 2 dates in timeschedule

[Norbertus]

Hello,

I want to calculate in Powerpivot the workingtime between 2 fields.
Workdays have starttime at 08:00 uur and an endtime at 17:30 uur

Example 1:

Day 1: 2014-04-22 16:00
Day 2: 2014-04-23 09:00
​

Answer: 02:30

Example 2:

Day 1: 2014-04-22 16:00
Day 2: 2014-04-24 09:30
​

Answer: 12:30

When there is a weekend this time may not be calculated
Expample 3:

Day 1: 2014-04-18 16:00 (Friday)
Day 2: 2014-04-21 09:00 (Monday)
​

Answer: 02:30

Hope someone can help me
Greetings

 

[dfothers]

Hello,

I want to calculate in Powerpivot the workingtime between 2 fields.
Workdays have starttime at 08:00 uur and an endtime at 17:30

Did not attempt this in powerpivot but rather took to it with an older fashioned time formatting and text formula strategy that tests the mind but does get you the right answer.

									Working hours
						8:00	17:30		9:30	0.50
Day of the week	Date & Time in custom format	Convert to Number format	Truncate fractions of a day	Time value	After start	Before finish	Fractions of hours
Tuesday	15/04/2014 16:00	41744.67	41744.00		16:00	8:00	1:30
Monday	21/04/2014 9:00	41750.38	41750.00		9:00	1:00	8:30
Sunday	1	Sunday	0	Networking day
Monday	2	Monday	1	5		Hours in part days
Tuesday	3	Tuesday	1.5
Wednesday	4	Wednesday	9.5
Thursday	5	Thursday	9.5
Friday	6	Friday	9.5
Saturday	7	Saturday	0
			31
				Hours in whole working days between dates
				28.5
				Hours in part days (first and last day)			Numerical value in hours
				2:30		2	0.50		2.50
				Whole hours calculation				2.50
			The answer	31.00

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

​

Based on the following formulas

									Working hours
						0.333333333333333	0.729166666666667		=H2-G2	=MINUTE(J2)/60
Day of the week	Date & Time in custom format	Convert to Number format	Truncate fractions of a day		Time value	After start	Before finish	Fractions of hours
=TEXT(D4,"dddd")	41744.6666666667	=B4	=TRUNC(C4)		=C4-D4	=$F4-G$2	=H$2-$F4
=TEXT(D5,"dddd")	41750.375	=B5	=TRUNC(C5)		=C5-D5	=$F5-G$2	=H$2-$F5
Sunday	1	=A8	0	Networking day
Monday	2	=A9	1	=NETWORKDAYS(D4,D5)		Hours in part days
Tuesday	3	=A10	1.5
Wednesday	4	=A11	9.5
Thursday	5	=A12	9.5
Friday	6	=A13	9.5
Saturday	7	=A14	0
			=SUM(D8:D14)
				Hours in whole working days between dates
				=(E9-2)*9.5
				Hours in part days (first and last day)				Numerical value in hours
				=H4+G5		=HOUR(E21)	=MINUTE(E21)/60		=G21+H21
				Whole hours calculation					=J21+F11
			The answer	=E18+J25

<colgroup><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

Not what you wanted but well it works

Good luck

 

[dfothers]

Just something I forgot to mention about this method is that it only works when you are EXTRA careful about the cell formats. Unfortunately to make it work I have used a combination of custom cel formats for the initial date-time inputs which then I have recomposed using both Text, math and special date functions. Each of these needs its own formating to work and excel tends to default the format from the cell that the formula uses for inputs. If you get the format wrong it definitely will not work. Notice I also used Networkingday which has to be enabled from the Analysis toolpack in the add ins in 2010 and prior versions.

 

[theBardd]

This is how I would do it.

First create a dates table in Excel. List all of the necessary dates in column 1, labelled Date, and then add a column labelled IsWeekday with the following formula, =--(WEEKDAY([Date],2)<6).

Import this table into Power Pivot as a linked table (there is no need to setup a relationship between this and your table, we will not be filtering by date, and yoiu have two dates anyway).

With your two tables in Power Pivot, add a calculated column, WorkingHours, to your table with this formula
=( CALCULATE(
SUM( Calendar[IsWeekday] ),
DATESBETWEEN(
Calendar[Date],
Data[Day1],
Data[Day2]
)
)
- ( WEEKDAY([Day1],2) < 6 )*1
- ( WEEKDAY([Day2],2) < 6 )*1 ) *9.5 /24
+IF( WEEKDAY([Day1],2 ) > 5,0,"17:30:00"*1-MOD( Data[Day1],1 ) )
+IF( WEEKDAY([Day2],2 ) > 5,0,MOD( Data[Day2],1 )-"08:00:00"*1)

In essence, I am calculating the number of whole working days between the two dates, ignoring the start and end date, and multiplying this by 9.5 hours, then calculating the working hours for the start date and end dates separately and adding those in.

 

[Norbertus]

Hi "TheBardd"

Thanks for your post. Your solution to calculate is verry nice.
Last weekend i made the next calculation. It gives the same answer, but your one i like better

=
IF(CALCULATE(SUM(Datum[IsWorkday]);

DATESBETWEEN(Datum[Datum]; [inbehandeling];NOW()))= 1;


(TIMEVALUE(NOW())-TIMEVALUE([inbehandeling]))*24;


IF(CALCULATE(SUM(Datum[IsWorkday]);

DATESBETWEEN(Datum[Datum]; [inbehandeling];NOW()))=2;


(TIME("17";"30";"00") - TIMEVALUE([inbehandeling])+
TIMEVALUE(NOW())-TIME("08";"00";"00"))*24;


IF(CALCULATE(SUM(Datum[IsWorkday]);

DATESBETWEEN(Datum[Datum]; [inbehandeling];NOW()))>2;


(TIME("17";"30";"00") - TIMEVALUE([inbehandeling]) +
TIMEVALUE(NOW()) -TIME("08";"00";"00"))*24 +

(CALCULATE(SUM(Datum[IsWorkday]);

DATESBETWEEN(Datum[Datum]; [inbehandeling];NOW()))-2)*9,5)))


Thanks