uit geregistreerde tijden alleen uren tussen 8:30-17:00 tellen

[emc]

Hallo,
Ik moet het % beschikbaarheid van automaten tussen 08:30 en 17:00 tellen. Dat betekent wanneer een storing wordt gemeld om 01-01-2013 om 08:00uur en afgemeld op 03-01-2013 14:00uur, de uren buiten 08:30 en 17:00 niet mee opgeteld mogen worden. Is hier een formule in excel voor?

Alvast hartelijk dank, emc

 

[spaarie]

Als je een voorbeeldje plaatst, zodat wij niet hoeven na te bootsen, zal dit gerust mogelijk zijn...

 

[emc]

Voorbeeld

Bekijk bijlage Voorbeeld HM.xls

Dank voor je reactie. zie de bijlage.

In de kolom 'Uren buiten gebruik' moet dan het verschil tussen kolom c en d komen te staan, maar de uren buiten 08:30-17:00 mogen niet worden meegeteld.

 

[Ralbers]

Ik heb een poging gedaan.

Bekijk bijlage Voorbeeld HM1kopie.xls

Ik heb een zelfgeschreven formule toegevoegd die ik ooit met behulp van dit forum heb weten te maken.

Ik heb meerdere regels toegevoegd
Iedere regel gaat steeds iets beter
1e regel is als het op dezelde dag is
Bij de 2e regel zie je dat dit fout gaat omdat hij hier het verschil niet ziet tussen dagen
3e regel is er een test toegevoegd als dagen hetzelfde zijn kan gewoon formule uit 2e regel gebruikt worden anders word het 8:30 als starttijd gegeven. Deze 2 opgeteld zorgt dus voor de uren de 1e en laatste dag. Wat bij 2 dagen dus goed is.
4e rij is een als formule toegevoegd voor als het meerdere dagen zijn.
5e regel is ook toegevoegd dat ie dan gewoon moet toevoegen (aantal dagen (excl 1e en laatste dag) * (17:00-8:30)
De uitkomst hiervan is in tijd/datum formaat wat dus niet hoger kan dan 24 uur kan zijn
dus in de kolom ernaast (kolom I)doet hij

=UUR(H3)+DAG(H3)*24+MINUUT(H3)/60

wat er gewoon cijfers van maakt

In kolom J heb ik de 2 samengevoegd wat lijkt op een gigantische formule maar wat eigenlijk niks meer is dan de formule uit kolom H 3x invullen op de plekken van H3

=UUR(ALS(DAG(D3)=DAG(C3);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);ALS(DAG(D3)-DAG(C3)=1;PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+(DAG(D3)-DAG(C3)-1)*(Eindtijd-Starttijd))))+DAG(ALS(DAG(D3)=DAG(C3);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);ALS(DAG(D3)-DAG(C3)=1;PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+(DAG(D3)-DAG(C3)-1)*(Eindtijd-Starttijd))))*24+MINUUT(ALS(DAG(D3)=DAG(C3);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);ALS(DAG(D3)-DAG(C3)=1;PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden);PauzeOfWerkPerDag(TIJD(UUR(C3);MINUUT(C3);SECONDE(C3));TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+PauzeOfWerkPerDag(Starttijd;TIJD(UUR(D3);MINUUT(D3);SECONDE(D3));Werktijden)+(DAG(D3)-DAG(C3)-1)*(Eindtijd-Starttijd))))/60

Je moet zelf even goed testen of het in alle gevallen klopt en goed gaat
Ik hoop dat je er iets van kunt maken

Groeten Roel

 

[emc]

Bedankt, korte test ziet er veelbelovend uit, morgen ga ik er dieper op in!

 

[plongske]

emc,
Met 1 formule.

 

[Ralbers]

haha ja zo is het natuurlijk ook geniaal werkt denk ik iets beter dan mijn creatie en je hebt nie eens vba nodig

 

[plongske]

@Ralbers
Ben er toch een paar uurtjes aan bezig geweest.

 

[sylvester-ponte]

plonske, ik gebruik vba maar krijg niet altijd het zelf de antwoord.
zie voorbeeld

 

[plongske]

@Sylvester,
Er zit inderdaad nog een fout in mijn formule.
Ik was ervan uitgegaan dat er geen storing kon gemeld worden voor 8u.
ook wordt er geen rekening gehouden als er een weekend tussen zit

 

[sylvester-ponte]

in het weekeind drink je toch ook koffie:d

 

[popipipo]

Ik vond de aanname van plongske niet zo vreemd.
Buiten kantoor tijden de storing niet oplossen maar wel kunnen koffie drinken is misschien wel een beetje tegenstrijdig.
Aan TS of hij hiermee kan leven of toch de VBA oplossing van sylvester gebruiken.

 

[sylvester-ponte]

ok zonder weekend:

 

[plongske]

heb mijn formule fatsoenlijker gemaakt

=ALS((D5-C5)>TIJDWAARDE("15:30");(D5-C5)-TIJDWAARDE("15:30")-UUR(((D5-C5)-TIJDWAARDE("15:30"))/24)*TIJDWAARDE("15:30");D5-C5)

 

[emc]

Onzettend bedankt! Gelukt.

 

[sylvester-ponte]

heb je van 8-1-2013 8:00 tot 8-1-2013 8:34 getest ?
daar moet toch 4 min uit komen?

 

[emc]

Getest en dat klopt.

 

[plongske]

Met deze code klopt het ook.

=als(tijdwaarde(uur(c3)&":"&minuut(c3))<tijdwaarde("8:30");$d3-$c3-(netto.werkdagen(c3;d3)-1)*tijdwaarde("15:30")-(tijdwaarde("8:30")-(uur(c3)&":"&minuut(c3)));als(tijdwaarde(uur(c3)&":"&minuut(c3))>tijdwaarde("17:00");($d3-$c3-(netto.werkdagen(c3;d3)-1)*tijdwaarde("15:30"))+((uur(c3)&":"&minuut(c3))-tijdwaarde("17:00"));($d3-$c3-(netto.werkdagen(c3;d3)-1)*tijdwaarde("15:30"))))

Ik heb enkel nog het probleem om de weekends te overbruggen.

 

[sylvester-ponte]

en van 9-1-2013 16:30 tot 9-1-2013 17:34 moet toch 30 min opleveren?

 

[plongske]

@ sylvester,
Klopt
Ik geef het op. Blijkbaar toch moeilijker dan ik dacht. De formule is al zo lang dat het moeilijk wordt om nog het overzicht te bewaren.
Bedankt om aan te duiden dat er toch nog iets fout was in de formule.