Doorlooptijd offertes tijdens kantooruren berekenen op basis van begin- en einddatum

[Veenstar]

Hallo,

Ik wil graag de netto doorlooptijd tijdens kantooruren berekenen en heb al wat voorbeeldjes op de forum geprobeerd, maar ik kom er niet uit welke formule ik moet hanteren.

In kolom A en B van het voorbeeldbestand heb ik de begin- en einddatum staan.
In kolom C staat de bruto doorlooptijd.
In de kolom D wil ik graag de netto doorlooptijd hebben op basis van kantooruren door middel van een formule.
In kolom E heb ik neergezet wat de uitkomst van de formule in D zou moeten zijn.

We zijn van ma. t/m vr. geopend van 8.30-17.00, waarbij iedereen tussen 12.30 en 13.00 pauze heeft. De uren dat het bedrijf gesloten (buiten kantooruren en feestdagen) is moeten niet meegerekend worden in de doorlooptijd, omdat de medewerker op dat moment niet invloed kan uitoefen op de doorlooptijd van een offerte.

Wie kan mij helpen met de juiste formule? (hulpkolommen toevoegen of het anders indelen van het document is verder geen probleem).

:d

 

[roeljongman]

Dat is een mooi lijstje wensen
Mijn oplossing bestaat uit twee kolommen, belangrijkste reden is dat je 2 werkdagen (van 8 uur) wilt presenteren als 2 kalender dagen daarom is een extra kolom nodig met omrekening om te voorkomen dat de hele formule 2 moet worden opgenomen in de totaal formule. De formule om de netto doorlooptijd te berekenen is vrij lang

Het is opgebouwd uit de volgende elementen
- Als de begin en einddatum op dezelfde dag liggen =ALS(INTEGER(A8)=INTEGER(B8);B8-A8-ALS(REST(A8;1)<12;5/24;0;5/24;0)
- Een formule om de uren van de dag offerte aangemaakt berekent (INTEGER(A8)+17/24-A8-ALS(REST(A8;1)<12;5/24;0;5/24;0))
- Een formule die voor alle tussenliggende hele dagen 8 uur optelt +(MAX(0;NETTO.WERKDAGEN(INTEGER(A8);INTEGER(B8)-1;$A$15:$A$20)-1)*8/24)
- Een formule die voor de dag verstuurd de uren berekent +(B8-(INTEGER(B8)+8;5/24)-ALS(REST(B8;1)>12;5/24;0;5/24;0))

samen in 1 formule ziet dat er zo uit

=ALS(INTEGER(A8)=INTEGER(B8);B8-A8-ALS(REST(A8;1)<12;5/24;0;5/24;0);(INTEGER(A8)+17/24-A8-ALS(REST(A8;1)<12;5/24;0;5/24;0))+(MAX(0;NETTO.WERKDAGEN(INTEGER(A8);INTEGER(B8)-1;$A$15:$A$20)-1)*8/24)+(B8-(INTEGER(B8)+8;5/24)-ALS(REST(B8;1)>12;5/24;0;5/24;0)))

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#808080]Offerte aangemaakt[/td][td=bgcolor:#808080]Offerte verstuurd[/td][td=bgcolor:#808080]Doorlooptijd bruto[/td][td=bgcolor:#808080]Doorlooptijd kantooruren[/td][td=bgcolor:#808080]Uitkomst zou moeten zijn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]14-3-2019 13:16[/td][td]

18-3-2019 16:56​

[/td][td]

04d03:40​

[/td][td]

00:19:40​

[/td][td]

02:03:40​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td][/td][/tr][/table]
Bij de eerste regel uit je voorbeeld kom je dan op 19 uur en 40 minuten netto doorlooptijd en om die te vertalen naar de gewenste 02:03:40 heb je nog de volgende formule nodig

=INT(D2/(8/24))+MOD(D2;8/24)

letop dat je voor bepalen van het gemiddelde de berekening maakt op basis van kolom D anders wordt je gemiddelde veel te hoog door de kunstmatige ophoging van het aantal dagen.

zie bijlage voor verdere details

 

[Veenstar]

Wauw, dit is helemaal wat ik wil!!:thumb:

INTEGER en REST is nieuw voor mij, maar met hulp van google en wat testen, begrijp ik dat dit ervoor zorgt dat de datum en tijd worden gesplitst, omdat het beide in 1 cel staat. Dit wetende samen met je super duidelijk uitleg kan ik de formule mooi ontleden en begrijp ik wat er gebeurd. Chapeau hiervoor!!

Het enige wat ik niet helemaal kan plaatsen is de tweede -1 in het gedeelte: +(MAX(0;NETTO.WERKDAGEN(INTEGER(A8);INTEGER(B8)-1;$A$15:$A$20)-1)*8/24). Wat doet dit? De eerste -1 snap ik, want anders telt hij één werkdag teveel aangezien hij twee dezelfde datums ook als 1 werkdag ziet.

Mijn dank is in ieder geval groot! :thumb:

 

[roeljongman]

Graag gedaan, was een leuk puzzeltje vooral om die pauze er ook goed in te krijgen.

Integer en rest zijn inderdaad om het hele getal (datum) en de decimalen te vinden (tijd) heel handige functies om snel bepaalde delen uit een timestamp te halen.

De extra min 1 aftrek is nodig omdat ik het derde deel van de formule de uren van de dag van versturen laat berekenen. dus moet het aantal (volle) werkdagen 1 minder zijn

feitelijk had ik ook achter de formule ook -2 kunnen zetten en bij de einddatum weglaten want in dit geval wil je dat de nettowerkdagen formule 2 minder is zodat de eerste dag en laatste dag niet meetellen in aantal keer 8 uur omdat die begindag uren en verstuurdag uren apart worden berekend.

 

[Veenstar]

Ja dat wil ik wel begrijpen, dat stukje koste mij al behoorlijk wat moeite om te ontleden, laat staan te bedenken .

Ah natuurlijk! Nu snap ik hem helemaal en kan ik hem ook volledig toelichten op het werk.

Nogmaals super bedankt!! (als een kind zo blij)