# Can Powerpivot transform this data



## bertusavius (Aug 15, 2012)

I have a source table that contains time information about shifts people work:

EFGH37Source Table38namedatestart-timestop-time39John1-1-201213:0015:4540Frank15-8-20128:4510:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


I would like to transform this information into a pivot table using PowerPivot in order to get the following result:

NOPQ28Pivot Table29JohnFrankJames301-1-2012311300-14001:00321400-15001:00331500-16000:453415-8-2012350800-09000:15360900-10001:00371000-11000:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


What would be a good way to approach this problem?


----------



## powerpivotpro (Aug 19, 2012)

I love this problem, been turning it over in my head for a few hours now.  But I've concluded that PowerPivot isn't a big help for this particular problem.  PowerPivot itself is not a good "data shaper" and what you need here is more granularity in your rows of data - you need three rows for John where you currently only have 1.  PowerPivot is an aggregator - it helps you turn more rows into less, not vice versa.  Just like normal pivots, but with lots more benefits.


----------



## bertusavius (Aug 19, 2012)

Ok, I get what you mean. 
I would use PowerPivot the wrong way like this.

I guess I need to work my dataset over using regular excel formulas to create the required granularity before I launch it into PowerPivot.

A bit of a disappointment nontheless.


----------



## Fazza (Aug 20, 2012)

I don't have power pivot, but assume a normal Excel approach can be used. And for sure can be adopted for pivot pivot.

I've given the base table the normal defined name (for example via CTRL-F3) of "Details". Not a dynamic named range.

For convenience, set up another table. I called it "tblHours" with fields "start-time" and "stop-time".

0:00 and 1:00 for the first record
1:00 and 2:00 for the second record
and so on through the hours of the day until
23:00 and 24:00 for the final (24th) record

Save & close the file. From a new file, start the pivot table wizard (I'm in Excel 2003, btw, and use shortcut ALT-D-P). At the first step choose 'external data source' then follow the wizard: "Get Data", Excel files, OK, choose the source data file, OK, see the defined names. Select anything (ADD), just to get to the next step. Select the 'SQL' button and replace whatever is there by the SQL below. OK to enter that, see the results dataset, hit the 'open door' icon to exit MS Query, now choose the option to make the pivot table. Format the pivot table to achieve the exact arrangement desired. Exiting MS Query can take a while. Not just because of the Cartesian product in the query. If it hangs up, please use CTRL-ALT-DEL to bring up the task manager, which can then be dismissed. This might need to be done a couple of times.

I didn't test this much, so it might need a slight tweak of the SQL still - I'm thinking of hours near the limits; either the first or last hours of the day as they can need special attention to get the math correct. Please modify as required.

regards


```
SELECT D.[Date] AS [MyDate], FORMAT(H.[start-time],'hh:mm') & ' - ' & FORMAT(H.[stop-time],'hh:mm') AS [MyTime], IIF(D.[stop-time]<H.[STOP-TIME],D.[STOP-TIME],H.[STOP-TIME]) IIF(D.[start-time] ->H.[start-time],D.[start-time],H.[start-time])  AS [Value], D.name
FROM Details D, tblHours H
WHERE HOUR(D.[start-time]) <= HOUR(H.[start-time]) AND HOUR(D.[stop-time])+1 >= IIF(HOUR(H.[stop-time]) = 0, 24, HOUR(H.[stop-time]))
```


----------



## Laurent C (Aug 24, 2012)

Using PowerPivot V2, you could define a measure like that (Not thoroughly tested):
=SUMX(
        CROSSJOIN('dim time buckets';source);
         IF('dim time buckets'[start] >source[end-time] 
                 || 'dim time buckets'[end]<source[start-time] ;BLANK();
                      IF('dim time buckets'[end]<source[end-time]
                              ;'dim time buckets'[end];source[end-time]) -
                      IF('dim time buckets'[start]>=source[start-time]
                              ;'dim time buckets'[start];source[start-time])
         )  
  )
This requires a dimension table containing your buckets (something like the following)
bucket            start   end
08:00 - 09:00 08:00 09:00
09:00 - 10:00 09:00 10:00
10:00 - 11:00 10:00 11:00
......

No relationship required.

EDIT: for the sake of simplicity, I worked with time not dates. If you choose this approach, you may have to adapt the measure definition or your fact table accordingly.</source[end-time]
</source[start-time]>


----------



## JavierGuillen (Aug 26, 2012)

You can achieve the outcome you need entirely in PowerPivot by using a linked table and DAX measures:

First, import the following table as a linked Table (call it Time Range Lookup):


time rangesstart timeend time0700-08007:00:00 AM8:00:00 AM0800-09008:00:00 AM9:00:00 AM0900-10009:00:00 AM10:00:00 AM1000-110010:00:00 AM11:00:00 AM1100-120011:00:00 AM12:00:00 PM1200-130012:00:00 PM1:00:00 PM1300-14001:00:00 PM2:00:00 PM1400-15002:00:00 PM3:00:00 PM1500-16003:00:00 PM4:00:00 PM

<tbody>

</tbody>

(you should add all time ranges but i only did here the relevant ones)


2)  Add a DAX measure (identify checkin/checkout times):

CheckIn - CheckOut Time:=IF(
	HASONEVALUE( 'Time Range Lookup'[time ranges] ) , 
	IF(VALUES(Table1[start time]) >= VALUES('Time Range Lookup'[start time] ) && 
	VALUES(Table1[start time]) < VALUES('Time Range Lookup'[end time] )  , 
		"CheckIn", 
	IF(VALUES(Table1[end time]) >= VALUES('Time Range Lookup'[start time] ) && 
	VALUES(Table1[end time]) < VALUES('Time Range Lookup'[end time] )  , 
		"CheckOut" )  
	)  
)


3)  Identify work hours in between:

WorkHourRange:=IF(
	[CheckIn - CheckOut Time] <> BLANK(), 
	[CheckIn - CheckOut Time], 
		IF( 
			IF(
		HASONEVALUE( 'Time Range Lookup'[time ranges] ) ,  
			CALCULATE(  
				IF ( 
					CONTAINS( ADDCOLUMNS( VALUES('Time Range Lookup'[time ranges] ), "check", [CheckIn - CheckOut Time] ), [check], "CheckIn" ) && 
					NOT( CONTAINS( ADDCOLUMNS( VALUES('Time Range Lookup'[time ranges] ), "check", [CheckIn - CheckOut Time] ), [check], "CheckOut" )  )  
				, 1  )   , 
		FILTER( ALL('Time Range Lookup'), 
		COUNTROWS( 
			FILTER( 'Time Range Lookup', 
				EARLIER( 'Time Range Lookup'[start time] ) < 'Time Range Lookup'[start time] ) ) 
				) 
			)	
		)
	<> BLANK(), "Work" 
	) 
)

4) Create the final calculation for worked hours:

WorkedHours:=SWITCH( 
	[WorkHourRange], 
		"CheckIn", 
			FORMAT ( VALUES('Time Range Lookup'[end time]) -  VALUES( Table1[start time] ), "H:MM")   , 
		"Work", "1:00" , 
		"CheckOut", 
			FORMAT ( VALUES(Table1[end time]) -  VALUES('Time Range Lookup'[start time]) , "H:MM")  
	)


In the pivot table, place the employee names on columns, dates (from Table1, your data) & time ranges (from the imported lookup table) on rows, and then DAX measure [WorkHourRange] on values.  Remember to modify the format of the measure to be of Date type and display only the hours (you can do that in the measure editor window)


Javier Guillen
Senior BI Consultant
PowerPivot Blog: http://javierguillen.wordpress.com
Twitter: @javiguillen


----------



## bertusavius (Sep 2, 2012)

Just back to let you guys know that I explored all your suggestions and that I solved the problem.
In the end I adopted the crossjoin solution by Laurent C. 


SUMX(
      CROSSJOIN('Tabel13';'Tijd');
      IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd]) 
               ; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd]) 
                 - 
                 if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
               ;0) *(24)
  )


I really appreciate the time and effort by the thread contributors.



Only problem I'm facing now is performance issues although the source data is only about 20K records.
When I change a slicer of the PT, I sometimes have to wait for a full 5 minutes for the PT to refresh.
"OLAP query processing" it says.

I'm off trying to remove some unused measures to see if this helps.


----------



## Laurent C (Sep 2, 2012)

5 minutes is indeed too long. The CROSSJOIN might be too much.

You might want to try this variation:
=SUMX('Tijd';
           SUMX('Tabel13';
                           IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd]) 
                                    ; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd]) - 
                                       if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
                              ;0) *(24)
           ))


----------



## Laurent C (Sep 3, 2012)

Where has the the Edit button gone?

Anyway, you might also want to try the following:
=SUMX(
     GENERATE(Tijd;  
          FILTER(Tabel13;  'Tabel13'[Starttijd] < 'Tijd'[Eind tijd] && 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd] )
     )
        ;   if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd])
                                         -  if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
) * 24


----------



## JavierGuillen (Sep 3, 2012)

Im a bit confused.  I thought the output needed to be a break down by time range as shown in the initial post.   Is this the case still?  Or is the idea to get the total hours worked per day?

When I apply Laurent's expression, with names on columns and dates on rows, I get 2.75 for John (on 1/1/2012) and 1.75 for frank on (8/15/2012).  Should the output be a total per day, fractional representation of hours worked, or should it be the time worked broken by hour range?


----------



## bertusavius (Aug 15, 2012)

I have a source table that contains time information about shifts people work:

EFGH37Source Table38namedatestart-timestop-time39John1-1-201213:0015:4540Frank15-8-20128:4510:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


I would like to transform this information into a pivot table using PowerPivot in order to get the following result:

NOPQ28Pivot Table29JohnFrankJames301-1-2012311300-14001:00321400-15001:00331500-16000:453415-8-2012350800-09000:15360900-10001:00371000-11000:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


What would be a good way to approach this problem?


----------



## Laurent C (Sep 6, 2012)

What you get with the formula is the time expressed in hours (1,75 = 01:45) broken down by hour range, as already done by bertusavius.

If you have days on rows and names on columns, then you should indeed get the total hours worked per day & user.


----------



## JavierGuillen (Sep 7, 2012)

Laurent,

I tested your expression and I like it a lot. Great solution using CROSSJOIN to set the granularity of the calculation.

I also tested your original formula as well as the one using GENERATE on 20,000 records and I got the result back (in a pivot table) in aprox 10 seconds.

The dataset was 100 employees for 200 days.

bertusavius, could that be a realistic representation of your data set? Or is it structured in a different way?

Also, I removed the multiplication (* 24), as you won't need it when setting the format of the measure to Date and format string H:mm


----------



## bertusavius (Sep 13, 2012)

Guys, first of all I should mention that the performance issue was the result of another measure I used to expand the model. I'm really sorry I didnt mention this earlier.
I used this measure to pull data from a table with cost information. (actually wages)

SUMX(
CROSSJOIN( CROSSJOIN('Tabel13';'Tijd') ; 'Tarieven' );
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd]) 
; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd]) 
- 
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
;0) * (24) * if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag] ; 0)
)
-
SUMX( CROSSJOIN( CROSSJOIN('Tabel13';'Tijd') ; 'Tarieven' ) ;
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd]) 
; ((Tabel13[Pauze]/60) / ('Tabel13'[Stoptijd] - 'Tabel13'[Starttijd] ))
* 
(
if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd]) 
- 
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
) 
* if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag];0)
; 0))

It seems that this "double crossjoin" is a bit too much, even with recent hardware.
For now I'm using an average wage as alternative, although i'm still open to suggestions regarding code that works faster. 




More important secondly:
I discovered that as soon as shifts run after midnight, the calculation doesn't work. 
It doesnt seem to calculate anything at all, even the hours before midnight.
This is quite a problem because a substantial amount of shifts get affected by this.


----------



## Laurent C (Sep 13, 2012)

1. Note that you may have more than 2 tables in a cross join;
     if(Tarieven[Tarief] = Tabel13[ID] ;'Tarieven'[Bedrag];0)
This part of the expression indicates you have a natural relationship between your Tarieven table and Table13. Use it in your model, then you will not need any cross-join and can use RELATED('Tarieven'[Bedrag]).

Point 2: you should take the date into account within your calculation, otherwise, it will indeed not work.


----------



## bertusavius (Sep 14, 2012)

1) You are absolutely right about the fact that there is a relationship between Tarieven table and Table13. It was also my first approach to simply use RELATED('Tarieven'[Bedrag])
However the PT 'imploded' every time I tried. 

2)The priority atm lies at this problem. My time data should be correct before I connect it to cost date.
Here I have an example of a shift that symbolises all the challenges in one. _Notice the shift in date when the end-time exceeds 0200 hour.

_
*from:*
ID
datum
start
stop
9999
1-1-2012
21:30
2:45
*to:*
ID
datum
bucket
value
etc
9999
1-1-2012
2000-2100
0
9999
1-1-2012
2100-2200
0,5
9999
1-1-2012
2200-2300
1
9999
1-1-2012
2300-2400
1
9999
1-1-2012
2400-2500
1
9999
1-1-2012
2500-2600
1
9999
2-1-2012
0200-0300
0,75
9999
2-1-2012
0300-0400
0
etc


<TBODY>

</TBODY>

The latest attempt at code doenst give a right answer too; shifts that start and stop before midnight are calculated wrongly now. 

SUMX(
CROSSJOIN('Tabel13';'Tijd');
IF(AND( 
IF(AND(Tabel13[Starttijd]>=if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd]);
Tabel13[Stoptijd] >Tijd[Start tijd]
)
;
Tijd[Start tijd]
;
Tabel13[Starttijd]
) < if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd]) 
;
IF(AND(Tabel13[Stoptijd] <=Tijd[Start tijd];
Tabel13[Starttijd]<IF(TIJD[START tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
)
;
if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
;
Tabel13[Stoptijd]
) > Tijd[Start tijd]
)
;
IF(AND(Tabel13[Stoptijd] <=Tijd[Start tijd];
Tabel13[Starttijd]<IF(TIJD[START tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
)
;
if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd])
;
if(Tabel13[Stoptijd]>Tijd[Eind tijd];Tijd[Eind tijd];Tabel13[Stoptijd])
)
-
IF(AND(Tabel13[Starttijd]>=if(Tijd[Start tijd]>Tijd[Eind tijd];1;Tijd[Eind tijd]);
Tabel13[Stoptijd] >Tijd[Start tijd]
)
;
Tijd[Start tijd]
;
if(Tabel13[Starttijd]>Tijd[Start tijd];Tabel13[Starttijd];Tijd[Start tijd])
)
;
0 
) * 24 
)


----------



## Laurent C (Sep 14, 2012)

From your results, the measure is ok. 

The only problem I see is with the date itself, which actually comes from the data. 

Anything fancy about the "Datum" field in your pivot table?


----------



## bertusavius (Sep 14, 2012)

Oh, the above tables were examples.
They were meant to illustrate the desired result.

The real output is certainly not correct. 
If you are interested, I could send you the file of course. It would surely make it less abstract. 

The datum field is always a date.

Looking back, I should have used English names instead of Dutch ones.


----------



## Laurent C (Sep 14, 2012)

If you post a link to your table (Skydrive or whatever ...) I will take a look at it.

Why do you want to have this shift date, if the end time is past 02:00 ? 

More specifically, how would you define the meaning of the "Datum" field?

In any case, if you want to obtain this result, I recommend either importing your data with the date as you want it to be defined, or create a calculated column and adjust the relationship between your table and your date dimension, if you have one.


----------



## bertusavius (Sep 14, 2012)

It is correct that I need the date to change after two o clock. 
The meaning of the datum field is the start date of the shift.
I want to be able to say that a certain shift runs into another working day.
The situation i'm working in regrettetly defines that another working dat starts at two o clock in the morning.

I just sent you a PM btw.


----------



## bertusavius (Sep 15, 2012)

I think I nailed it with this code 

=SUMX(
* * * * CROSSJOIN('Tabel13';'Tijd');
IF(AND(
* * * * * *IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
* * * * * *;
* * * * * *IF(Tabel13[Stoptijd]<=(1/24*4);Tabel13[Stoptijd]+1;Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * )
* * * * *;
* * * * * if(IF(Tabel13[Stoptijd]<=(1/24*4);Tabel13[Stoptijd]+1;Tabel13[Stoptijd]) > IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
* * * * * * * ;*
* * * * * * * IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
* * * * * * * ;
* * * * * * * IF(Tabel13[Stoptijd]<=(1/24*4);Tabel13[Stoptijd]+1;Tabel13[Stoptijd])
* * * * * * )
* * * * * *-
* * * * * *IF(IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * * * *;
* * * * * * * *IF(Tabel13[Starttijd]<((1/24)*4);Tabel13[Starttijd]+1;Tabel13[Starttijd])
* * * * * * * *;
* * * * * * * *IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
* * * * * * *)
* * * * *;
* * * * *0
* * * ) * 24)


----------



## bertusavius (Aug 15, 2012)

I have a source table that contains time information about shifts people work:

EFGH37Source Table38namedatestart-timestop-time39John1-1-201213:0015:4540Frank15-8-20128:4510:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


I would like to transform this information into a pivot table using PowerPivot in order to get the following result:

NOPQ28Pivot Table29JohnFrankJames301-1-2012311300-14001:00321400-15001:00331500-16000:453415-8-2012350800-09000:15360900-10001:00371000-11000:30

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>*Blad4*​


What would be a good way to approach this problem?


----------



## Laurent C (Sep 15, 2012)

Have you checked that the dates are correct? 

After seeing your model, I suggest you add a date dimension to your model (with year,  month, day name, ...) instead of adding them as separate tables.

You might as well add a calculated column directly in the model, for the end date.

Also, in the file you sent, the very last bucket was not correct (23:00 to 00:00 instead of 23:00 to 24:00), resulting in lost data.


----------



## bertusavius (Sep 16, 2012)

Just here to let you know that the problem is sufficiently solved.

In the end I also needed to subtract some time for each shift (pauze). 
I also added a column that defines the date for buckets of shifts that run past midnight.


=SUMX(
        CROSSJOIN('Tabel13';'Tijd');

        (if(or(Tabel13[Starttijd]>= ((1/24)*4);Tabel13[Starttijd] > Tabel13[Stoptijd]);
 IF(AND(
           IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
           ;
           IF(Tabel13[Stoptijd]<=(1/24*<wbr>4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
           )
         ;
          if(IF(Tabel13[Stoptijd]<=(1/<wbr>24*4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
              ; 
               IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
              ;
              IF(Tabel13[Stoptijd]<=(1/24*4)<wbr>;Tabel13[Stoptijd]+1;Tabel13[<wbr>Stoptijd])
            )
            -
           IF(IF(Tabel13[Starttijd]<((1/<wbr>24)*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
               ;
                IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd])
               ;
               IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
              )
         ;
         0
      )
   ;
   IF(AND( Tabel13[Starttijd] < Tijd[Eind tijd] 
          ;Tabel13[Stoptijd]  > Tijd[Start tijd]
          )
       ;
         if(Tabel13[Stoptijd] < Tijd[Eind tijd];Tabel13[Stoptijd];Tijd[<wbr>Eind tijd])
         -
         if(Tabel13[Starttijd] > Tijd[Start tijd];Tabel13[Starttijd];Tijd[<wbr>Start tijd])
        ;
       0
      )                                                                                        
   )
   -
   if(or(Tabel13[Starttijd]>= ((1/24)*4);Tabel13[Starttijd] > Tabel13[Stoptijd]);
 IF(AND(
           IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) < IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
           ;
           IF(Tabel13[Stoptijd]<=(1/24*<wbr>4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
           )
         ;
          if(IF(Tabel13[Stoptijd]<=(1/<wbr>24*4);Tabel13[Stoptijd]+1;<wbr>Tabel13[Stoptijd]) > IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
              ; 
               IF(Tijd[Eind tijd]<=(1/24*4);Tijd[Eind tijd]+1;Tijd[Eind tijd])
              ;
              IF(Tabel13[Stoptijd]<=(1/24*4)<wbr>;Tabel13[Stoptijd]+1;Tabel13[<wbr>Stoptijd])
            )
            -
           IF(IF(Tabel13[Starttijd]<((1/<wbr>24)*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd]) > IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
               ;
                IF(Tabel13[Starttijd]<((1/24)<wbr>*4);Tabel13[Starttijd]+1;<wbr>Tabel13[Starttijd])
               ;
               IF(Tijd[Start tijd]<(1/24*4);Tijd[Start tijd]+1;Tijd[Start tijd])
              )
         ;
         0
      )
   ;
   IF(AND( Tabel13[Starttijd] < Tijd[Eind tijd] 
          ;Tabel13[Stoptijd]  > Tijd[Start tijd]
          )
       ;
         if(Tabel13[Stoptijd] < Tijd[Eind tijd];Tabel13[Stoptijd];Tijd[<wbr>Eind tijd])
         -
         if(Tabel13[Starttijd] > Tijd[Start tijd];Tabel13[Starttijd];Tijd[<wbr>Start tijd])
        ;
       0
      )                                                                                        


   )*(if(isblank(Tabel13[Pauze])<wbr>;0;(Tabel13[Pauze]/60) / (Tabel13[Gewerkt] + (Tabel13[Pauze]/60)))))


----------

