Tijd bereken tussen een bepaalde periode

[gpiket7]

Kan iemand mij helpen met het aanpassen van de volgende formule:

=ALS(K11=""|""|(NETTO.WERKDAGEN(H11+I11|J11+K11|feestdagen)*(eindtijdstip2-begintijdstip2)*24-ALS(EN(ISFOUT(VERGELIJKEN(GEHEEL(H11+I11)|feestdagen|0))|WEEKDAG(H11+I11|2)<6)|ALS((H11+I11-GEHEEL(H11+I11))<=begintijdstip2|0|ALS((H11+I11-GEHEEL(H11+I11))<=eindtijdstip2|((H11+I11-GEHEEL(H11+I11))-begintijdstip2)*24|(eindtijdstip2-begintijdstip2)*24)))-ALS(EN(ISFOUT(VERGELIJKEN(GEHEEL(J11+K11)|feestdagen|0))|WEEKDAG(J11+K11|2)<6)|ALS((J11+K11-GEHEEL(J11+K11))>=eindtijdstip2|0|ALS((J11+K11-GEHEEL(J11+K11))>begintijdstip2|(GEHEEL(J11+K11)+eindtijdstip2-(J11+K11))*24|(eindtijdstip2-begintijdstip2)*24))|0))/24)

Deze berekend het aantal uur dat ligt tussen bepaalde data excl. de feestdagen en de zaterdag en zondag. Nu wil ik de formule zo aanpassen dat ipv het weekend en de feestdagen niet meerekenen hij deze wel meerekend maar alleen de uren tussen 6:00 uur en 23:59 uur.

 

[gpiket7]

In het excel document heb ik even een voorbeeld en extra uitleg

 

[Thoralf]

Hallo Ger,

Reagerend op de formule in je eerste posting:
Je moet de formule aanvullen met twee delen:
het eerste deel is het aantal weekend-dagen en feestdagen in de periode van begin tot einddatum, de beide dat niet meegerekend. Dit volgt eenvoudig uit
((einddatum - begindatum - 1) - NETTO.WERKDAGEN(begindatum + 1 ; einddatum - 1 ; feestdagen)) * 18 uren
Het tweede deel moet komen uit het bepalen of begin en einddatum buiten Netto.werkdagen vallen (dus weekenddagen of feestdagen zijn). is dat niet het geval, dan heb je de gevraagde tijd. Is dat wel het geval, één van beide of beide dan vallen in het weekend of zijn feestdagen, dan zul je uitgaande van begintijdstip en eindtijdstip op de betrokken dagen nog de extra toe te voegen tijd moeten bepalen.

 

[gpiket7]

Bedankt voor je uitleg, maar ik snap er niet zo veel van.
Zou je het misschien in een voorbeeldje willen posten met uitleg erbij?

 

[Thoralf]

Hallo Ger,

De formule uit jouw eerste bericht laat je gewoon staan, in een hulpkolom zet je nu de formule

=((J11-H11-1)-(NETTO.WERKDAGEN(H11+1;J11-1;feestdagen))*18+ALS(OF(WEEKDAG(H11>5;2);ISGETAL(VERGELIJKEN(H11;feestdagen;0)));ALS(1-I11>(2/3);18;(1-I11)*18);0)+ALS(OF(WEEKDAG(J11>5;2);ISGETAL(VERGELIJKEN(J11;feestdagen;0)));(MAX(1;K11-0,25+1)-1)*18;0)

Het resultaat van deze formule tel je op bij het resultaat van jouw eerdere formule.
Toelichting op de formule:
In het deel

((J11-H11-1)-(NETTO.WERKDAGEN(H11+1;J11-1;feestdagen))*18

wordt het aantal weekenddagen en feestdagen in de periode van de dag na de begindatum tot en met de dag voor de einddatum bepaald: eerst wordt er het aantal werkelijke dagen berekend en daar worden de netto werkdagen van af getrokken. Het resultaat wordt met 18 vermenigvuldigd om dat je aangeeft dat die dagen voor 18 uur moeten meetellen.
In het deel

ALS(OF(WEEKDAG(H11>5;2);ISGETAL(VERGELIJKEN(H11;feestdagen;0)));ALS(1-I11>(2/3);18;(1-I11)*18);0)

wort er eerst gekeken of de begindatum valt in het weekend of op een feestdag. Als dat zo is, wordt het aantal uren dat meetelt op de begindag bepaald (met een maximum van 18)
Ten slotte in het deel

ALS(OF(WEEKDAG(I11>5;2);ISGETAL(VERGELIJKEN(I11;feestdagen;0)));(MAX(1;K11-0,25+1)-1)*18;0)

gebeurt hetzelfde, maar nu voor de einddag.

Ik heb de formules moeten aanpassen omdat ik er te laat achter kwam dat je datum en tijdstip in twee cellen had staan.

 

[gpiket7]

Nee het klopt niet, want hij geet ook een waarde als het geen weekend of feestdag betrefd.
En dat kan niet. is het niet mogelijk om de formule aan te passen zodat hij niet meer kijkt naar feestdagen en weekenden?


Dat je iets krijgt als:

=ALS(K11=""|""|(H11+I11|J11+K11)*(eindtijdstip2-begintijdstip2)*24-ALS(EN(ISFOUT(VERGELIJKEN(GEHEEL(H11+I11)|0))|WEEKDAG(H11+I11|2)<6)|ALS((H11+I11-GEHEEL(H11+I11))<=begintijdstip2|0|ALS((H11+I11-GEHEEL(H11+I11))<=eindtijdstip2|((H11+I11-GEHEEL(H11+I11))-begintijdstip2)*24|(eindtijdstip2-begintijdstip2)*24)))-ALS(EN(ISFOUT(VERGELIJKEN(GEHEEL(J11+K11)|0))|WEEKDAG(J11+K11|2)<6)|ALS((J11+K11-GEHEEL(J11+K11))>=eindtijdstip2|0|ALS((J11+K11-GEHEEL(J11+K11))>begintijdstip2|(GEHEEL(J11+K11)+eindtijdstip2-(J11+K11))*24|(eindtijdstip2-begintijdstip2)*24))|0))/24)

(feestdagen zijn er nu uit, maar weet niet hoe ik het weekend er uit kan slopen.

 

[Thoralf]

Hallo Ger,

Bijgaand een voorbeeldbestandje met aangepaste formule en door splitsing van formule in delen hopelijk een idee van de opbouw en tevens mogelijkheid tot controle.

Formule uit m11 toevoegen aan de formule die je al had (gewoon jouw formule + de nieuwe formule)

 

[Thoralf]

Nog even een opmerking over het verwijderen van weekends en feestdagen uit jouw eerdere formule (die dus die niet is aangepast met de weglating van de feestdagen):
Dat werkt niet, omdat je voor werkdagen met 24 uur per dag rekent en voor weekend- en feestdagen met 18 uur per dag. Je zult dus je totale tijd dan ook moeten opbouwen vanuit die twee delen.
Jouw oorspronkelijke formule doet het dus voor de werkdagen en is daarmee dus het eerste deel. De formule in mijn voorbeeldmap in M11 berekent de tijd voor weekend- en feestdagen en geeft daarmee het tweede deel van de totale tijd.
De resultaten van beide formules moet je dus optellen.

 

[gpiket7]

Volgens mij klopt het nog niet, want als ik je formulier kopieer naar mijn werkblad.
Dan zegt deze dat de uitkomst is 36 bij een storing van
1-12-2009 - 16:00 - 2-12-2009 - 9:00 - 2:30 (storing tijdens werktijden)
dit zou dan 5,5 uur (van 17:30 tot 0:00 uur) uitval + 2 uur (6:00 tot 8:00 uur) uitval tijdens calamiteits uren. Daar zou dan 7,5 uur moeten verschijnen.

Het kan ook in 3 hulpkolommen, eentje die berekend tussen 17:30 en 0:00 (tijden werkdagen), eentje voor de uren tussen 6:00 en 8:00 (tijden werkdagen) en de laatste dan met de gegevens van het weekend + feestdagen tussen 6:00 en 0:00

 

[Thoralf]

Hallo Ger,

Er zat nog een foutje in mijn map1. Dat is nu hersteld (Kopie van Map1).
Dit is de uitwerking van jouw vraag in de eerste posting.
In jouw tweede posting, die jij voorstelt als een voorbeeld van de eerste blijkt dat je jouw vraag hebt bijgesteld: een aanvulling op de oorspronkelijke vraag: de 17:30 tot 24:00 periode voor de werkdagen en de 0:00 tot 6:00 uur voor alle dagen.
Tenslotte kom je in jouw laatste posting met, naast de terechte opmerking van een fout, een nieuwe invulling in het gehele proces: de periode van 6:00 tot 8:00 voor werkdagen.
Tevens zit er ook in de daarin gegeven uitwerking een rekenfout: 17:30 tot 24:00 uur is niet 5:30, maar 6:30 uur.
Maar goed.
De periode 17:30 tot 24:00 op werkdagen kan met dezelfde formule als de uren 8;00 tot 17:30 gedaan worden, maar dan moet je wel even een tweede set begin- en eindtijd (begintijd1 = 17:30 en eindtijd1 = 24:00) definiëren en die in de formule gebruiken.
Idem dito voor de periodes 0:00 tot 6:00 en 6:00 tot 8:00 op werkdagen (begintijd2 en eindtijd2 en begintijd3 en eindtijd3) .
Blijft dan nog over de weekend en feestdagen van 0:00 tot 6:00 uur.
Wellicht kun je deze laatste met hetgeen in Kopie van Map1 staat, en dan met name wat er in de cellen M13 tot en met M17 staat zelf construeren.

 

[gpiket7]

Bedankt Thoralf, nu werkt het!