Tijdverschil berekenen (in uren) op basis van datums~

Hey Jan,

De oorzaak was dat ik in de formule zonder pauzes overal 7,75 geplaatst had (ook bij de MIN-functies). uu:mm wordt ook gelezen door de Engelse Excel, hier lag het niet aan.

Ik ben er mee geholpen, dankjewel!

Klaas,

Ik vrees dat je de formule niet helemaal juist hebt overgenomen.

Als ik de formule kopiëer en dan plak in bv G19 en zet jouw voorbeeldwaarden in C19 en D19, dan krijg ik de juiste uitkomst van 2 uur.

De engelse versie van de formule:

=(NETWORKDAYS(C19,D19)-2)*7.75+MIN(((15.5/24)-TEXT(C19,"uu:mm"))*24,8.5)+MIN((TEXT(D19,"uu:mm")-(7/24))*24,8.5)-(MIN(MAX(10-TEXT(C19,"uu:mm")*24,0),0.25)+MIN(MAX(13-TEXT(C19,"uu:mm")*24,0),0.5)+MIN(MAX(TEXT(D19,"uu:mm")*24-9.75,0),0.25)+MIN(MAX(TEXT(D19,"uu:mm")*24-12.5,0),0.5))

hoewel ik denk dat dan "uu:mm" iets moet worden als: "hh:mm"

Jan

Klaas,

Onderstaande formule zou dit moeten doen.

De eerste en laatste dag worden op 8,5 uur gesteld en vervolgens worden de (binnen de aangegeven tijd vallende) pauzes er vanaf getrokken.

=(NETTO.WERKDAGEN(C19;D19)-2)*7,75+MIN(((15,5/24)-TEKST(C19;"uu:mm"))*24;8,5)+MIN((TEKST(D19;"uu:mm")-(7/24))*24;8,5)-(MIN(MAX(10-TEKST(C19;"uu:mm")*24;0);0,25)+MIN(MAX(13-TEKST(C19;"uu:mm")*24;0);0,5)+MIN(MAX(TEKST(D19;"uu:mm")*24-9,75;0);0,25)+MIN(MAX(TEKST(D19;"uu:mm")*24-12,5;0);0,5))

Dit gedeelte is nieuw en behelst dus de pauzes.

-(MIN(MAX(10-TEKST(C19;"uu:mm")*24;0);0,25)+MIN(MAX(13-TEKST(C19;"uu:mm")*24;0);0,5)+MIN(MAX(TEKST(D19;"uu:mm")*24-9,75;0);0,25)+MIN(MAX(TEKST(D19;"uu:mm")*24-12,5;0);0,5))

De verschillende termen.

bijdrage ochtendpauze eerste dag: MIN(MAX(10-TEKST(C19;"uu:mm")*24;0);0,25)

bijdrage middagpauze eerste dag: MIN(MAX(13-TEKST(C19;"uu:mm")*24;0);0,5)

bijdrage ochtendpauze laatste dag: MIN(MAX(TEKST(D19;"uu:mm")*24-9,75;0);0,25)

bijdrage middagpauze laatste dag: MIN(MAX(TEKST(D19;"uu:mm")*24-12,5;0);0,5))

Jan

In de formule die we hier op het forum bedacht hebben (en waarvan ik nu gebruik maak) wordt de doorlooptijd berekend door de tijd tussen de start- en einddatum van het product te berekenen.

In de formule staat 1 werkdag gelijk aan 8,5 uren. Men is namelijk 8,5 uren aanwezig (07.00-15.30u.).

Nu wil ik de beschikbare werktijd tussen een start- en einddatum weten: met andere woorden bepalen hoeveel tijd er beschikbaar was om aan het product te werken.

De beschikbare werktijd is 8,5-0,75 (pauzetijd van in totaal drie kwartier) = 7,75 uur per dag.

• Ik gebruik de formule die we eerder bedacht hebben om de beschikbare werktijd te berekenen:

=(NETWORKDAYS($C$4;$D$4)-2)*7,75+MIN(((15,5/24)-TEXT($C$4;"uu:mm"))*24;7,75)+MIN((TEXT($D$4;"uu:mm")-(7/24))*24;7,75)

Het resultaat is 76,1 uren:

Startdatum            Einddatum             Beschikbare werktijd, berekend door Excel (in uren)

07-09-15  09:10     21-09-15  07:02     76,1

Deze formule houdt dus geen rekening met pauzes. Als hij wel rekening met pauzes hield dan zou de uitkomst 75,33 uren moeten zijn. Het in rekening brengen van de pauzes is dus het probleem.

De pauzeperioden zijn: 09.45-10.00 uur (0,25 uur) en van 12.30-13.00 uur (0,5 uur).

Hoe kan ik de hoofdformule uitbreiden zodanig dat de pauzes van de startdatum en einddatum afgetrokken worden van de hoofdformule?

Ik ben zelf ook al bezig geweest om de formule uit te breiden zodat de pauzes in rekening gebracht worden.

De volgende voorwaarden had ik opgeschreven:

1. Als C4 (cel met startdatum, zie mijn tabelletje) lager is dan 09.45 uur dan 0,75 bij de hoofdformule aftrekken

2. Als C4 hoger is dan 10.00 uur dan 0,5 uur aftrekken

3. Als C4 hoger is dan 13.00 uur dan geldt de berekende tijd uit de hoofdformule

4. Als D4 (cel met einddatum) hoger is dan 10.00 uur en lager dan 12.30 uur dan 0,25 uur aftrekken

5. Als D4 hoger is dan 13.00 uur dan 0,75 uur aftrekken

Voor voorwaarde 1 had ik de volgende formule bedacht:

=E4-ALS(TEKST(C4;MM:UU)<9,75;[0,75];[0,5]) waarbij E4 de cel is met de hoofdformule.

Hier gaat het al fout, want ik krijg de melding dat de ingevoerde naam niet geldig is.

Klaas,

Mooi dat het naar je zin is en ook prettig dat er wordt meegedacht, in dit geval door JP.

De opmerking van JP dat ik toch eigenlijk die 12 uur zou moeten aanhouden heb jij blijkbaar ter harte genomen door de werkelijke begin- en eindtijd van een werkdag op te nemen.

Er is nog een kleine aanpassing van de formule nodig denk ik, voor het geval iemand na werktijd een binnenkomend en/of voor werktijd een vertrekkend product noteert, dit zou negatieve tijden opleveren voor begin- en/of einddag.

De formule zou dan moeten worden:

=(NETTO.WERKDAGEN(C19;D19)-2)*8,5+MAX(MIN(((15,5/24)-TEKST(C19;"uu:mm"))*24;8,5);0)+MAX(MIN((TEKST(D19;"uu:mm")-(7/24))*24;8,5);0)

tenzij je natuurlijk de invoercellen hebt beperkt tot tussen 7:00 uur en 15:30 uur.

Jan