Urenberekening binnen kantoortijden

[NihihigO]

Ik ben op zoek naar iemand die me kan helpen met een formule in Excel die het aantal uren gaat berekenen tussen een begindatum en tijd en einddatum en tijd. Hierbij moeten weekenden en feestdagen niet worden meegeteld en er moet rekening gehouden worden met “kantoortijden” van 07:00 tot 17:00.

Begindatum en tijd (en dus ook einddatum en tijd) mogen 2 verschillende cellen zijn, dus begindatum =A2 en begintijd = B2. (einddatum =C2 en eindtijd = D2)

Voor de feestdagen wil ik zelf graag een kolom met data invoeren om bepaalde data "uit te filteren".

Voorbeeld:
Begindatum en tijd: 27-12-2019 10:00
Einddatum en tijd: 02-01-2020 14:00

Uitkomst hier moet zijn 34 uur, omdat:
Vr 27-12-2019 = 7 uur (van 10.00 tot 17.00)
Za 28-12-2019 = 0 uur (weekend)
Zo 29-12-2019 = 0 uur (weekend)
Ma 30-12-2019 = 10 uur (van 07.00 tot 17.00)
Di 31-12-2019 = 10 uur (van 07.00 tot 17.00)
Wo 01-01-2020 = 0 uur (feestdag)
Do 02-01-2019 = 7 uur (van 07:00 tot 14.00)

Wie kan me hierbij helpen?
#Excelvraag

 

[JEC.]

Kun je een voorbeeldbestand plaatsen?

 

[Senso]

Hier kan je wellicht wat voorbeelden vinden.

 

[NihihigO]

Hierbij een voorbeeld bestand.

De zelf ingevoerde feestdagen staan in het tabblad Feestdagen.

 

[JEC.]

Ik heb in ieder geval voor cel G2 en G3 een formule voor je.

Cel: G2

=NETWORKDAYS(B2;E2;Feestdagen!$A$2:$A$7)-1&" dagen"&" en "&IF(C2>TIME(17;0;0);0;TEXT(ABS(F2-C2);"u:mm")&" uur")

 

[sylvester-ponte]

zo iets? zie gele cellen:

 

[NihihigO]

Zal er morgen naar kijken. Bedankt alvast!

 

[VenA]

Of zo

 

[sylvester-ponte]

VenA, ik denk dat een Max op de verkeerde plaats staat , probeer de tijden uit mijn voorbeeld eens uit.

hier mijn voorbeeld: (zonder bengin en eind datum test) dus eenvoudiger en korter:

ps bij mijn oplossing kan de eerste max eventueel ook weg. (als het begin maar eerder valt dan de eind tijd)

 

[Piet Bom]

Hier nog een variant.
Bij deze kun je tijdstippen invoeren die buiten werktijden vallen.
Er zijn 2 hulpkolommen gebruikt, maar die kunnen vervallen als je de datum en tijd in 1 cel stopt.

 

[NihihigO]

Bedankt voor de hulp! Ik heb uiteindelijk die van Sylvester gebruikt. :thumb:

Zal later die van Piet nog eens bekijken, maar ben voor nu eerst geholpen.

 

[sylvester-ponte]

ik hoop die uit post 6 , wand die andere heb ik niet gecontroleerd.

 

[Piet Bom]

ik hoop die uit post 6 ,

Ja die uit post #6 is beter dan die van #9
Onze beider formules zijn nagenoeg hetzelfde
Ik heb die kennelijk in het verleden van jou overgenomen.
Via een omweg komt het goede toch weer bij elkaar

 

[sylvester-ponte]

hoi Piet, waar zitten de verschillen?
ik ben benieuwd.

 

[sylvester-ponte]

ik zag wel wat foutjes in mijn bestand: deze doet het beter
de gele kolommen kunnen verwijderd worden, ze zijn om makkelijk te kunnen controleren of de formule klopt.

 

[VenA]

VenA, ik denk dat een Max op de verkeerde plaats staat

Werkt toch prima in het voorbeeld van de TS? Als er meer variabelen waren dan had dat wel vermeld mogen worden.
Alle basisfuncties voor dit soort vragen staan erin om dan alle mogelijke opties uit te proberen mag de TS ook zelf doen.

Maar je hebt gelijk dat het niet monkeyproof is:d

 

[sylvester-ponte]

weer foutjes gevonden: nu maar gelijk met een eenvoudige vba function:
ik ben heel benieuwd of er nog foutjes in zitten

 

[Piet Bom]

of er nog foutjes in zitten

Dit draadje staat nog open, maar ik weet niet of de TS voordeel ziet in een VBA oplossing.

@Sylvester,
Vergeleken met mijn function heb ik geen verschillen gevonden. Dat wil uiteraard niet zeggen dat beide functions goed zijn.
Deze function is een verder ontwikkelde variant van jouw function, waarbij de werkdag ook met maximaal 3 pauzes gedefinieerd kan worden.
Ook op zaterdagen en zondagen, en er wordt rekening gehouden met feestdagen.

 

[sylvester-ponte]

Piet , bedankt voor het testen. :thumb: