• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Aantal uren berekenen met meerdere variabelen

Status
Niet open voor verdere reacties.
J

Jeppiebru

Gebruiker
Lid geworden
14 dec 2015
Berichten
10
Goedemiddag Excelhelden,

Ik ben bezig en excel om aan de hand van Begin (datum+ Tijd) en Eind (datum+ Tijd) gegevens te berkenen hoeveel uur er buiten het profiel is gebruikt. Het lukt me echter niet om het voor elkaar te krijgen meerdere variabelen te ontleden, vooral het weekend en meerdere dagen geven problemen.

Het profiel wat niet meegeteld moet worden is op werkdagen tussen 06:00/19:00, dus alles tussen 00:00/06:00 en 19:00/24:00 en weekend/feestdagen geheel dienen opgeteld te worden.

De bedoeling is dat ik een goed overzicht krijg welke tijd er totaal buiten deze tijden zit tussen 2 data. Dus bijvoorbeeld uren buiten het profiel in Dag/Avond/Overnachting/Weekend kolommen, Ik loop echter vast en zie door de probeersels het overzicht niet meer, ik heb geprobeerd mijn formules in totaal factuur de ontleden in kolommen om zo een beter overzicht te krijgen, helaas.

Waar zit ik fout of welke formule ben ik vergeten?

Groet,

Jesper.
 

Bijlagen

Dank voor het bekijken van de vraag, hier mijn uitleg;

vr 4-12-2015 5:00 tot di 8-12-2015 5:00

vr 4-12-2015 5:00 >is het een weekdag of weekend/feestdag? >weekdag is het voor 06:00 of na 19:00? > verschil tellen
> weekend/feestdag tellen tot einde dag

di 8-12-2015 5:00>is de dag gelijk aan dag in > Nee >een weekdag of weekend/feestdag? >weekdag; is het na 19:00? > hele dag tellen MIN 06:00/19:00, anders alle uren tot 06:00 tellen >weekend/feestdag alle uren tellen.

Wel gelijk aan dag in> uren tellen voor 06:00/na 19:00. Er zou dan uit moeten komen, 1 uur verschil 1 + 24:00-19:00+ 48:00 weekend+ 13 uur + 5 uur werkdag = 72 uur buiten 06:00/19:00 werkdag.

za 12-12-2015 21:00 tot zo 13-12-2015 18:00

za 12-12-2015 21:00>is het een weekdag of weekend/feestdag? > weekend/feestdag; tellen tot einde dag>3 uur
Zo 13-12-2015 18:00>is de dag gelijk aan dag in > Nee >een weekdag of weekend/feestdag? >weekend/feestdag; alle uren tellen.>18 uur
=21 uur


Ik heb geprobeerd om de formules uit te splitsen om zo de berekening stappen te kunnen volgen, ik ben daar de mist in gegaan.
 
Laatst bewerkt:
Hele maandag= 24 uur -09:00/17:00=24-11uur=13uur buiten de tijdsspanne 06:00/19:00.

het gaat om de uren die vallen buiten 06:00/19:00.
 
van 0:00 tot 6:00 is 6 uur
en van 19:00 tot 24:00 is 5 uur
samen is dat 11 uur
in een hele dag zit buiten het interval 6:00 tot 19:00 dus 11 uur

ps hoeveel uur krijg je op za van 5:00 tot 17:00 ? tel je dan tot 24:00 ?
 
Laatst bewerkt:
excuus, het is inderdaad 24 uur - 13 uur ipv 24-11 uur.

In het weekend/feestdagen tellen het gehele aantal uren. Dus van 5:00 tot 17:00 (op dezelfde dag)= 12 uur, daar is er geen MIN periode 06:00/19:00 filtering nodig.

Bij datums van ma/vrij moet ik dus bij INRIT tijd alle uren weten voor 06:00 of 19:00, alle uitrit tijden na 19:00 wanneer dag = inrit, anders gehele uren vanaf 00:00 -06:00/19:00.

Bij weekenddagen/feestdagen de totale uren zonder aftrek 09:00/19:00,

Bij de combinatie, dus in de dagen tussen 2 data zitten weekdagen en weekenddagen, ga ik compleet de mist in.
 
Ik ben hier even aan het meelezen en ga ook een poging wagen, op basis van volgende denkpiste:
A: we tellen het totaal aantal uren (einde - begin)
B: we tellen het aantal uren dat op een werkdag tussen 6u en 19u valt
Resultaat: A - B is wat we zoeken, die "andere" uren?

Let wel: in de formules hou ik nog geen rekening met verlofdagen, dat moet nog ingebouwd worden, in het voorbeeld valt de enige verlofdag toevallig in het weekend dus de fout valt niet op in het eindresultaat (Blad3 AA19:AA28).
 

Bijlagen

Die van Sylvester ziet er net iets gestroomlijnder uit!
 
Het blijft leuk puzzelen. Alleen heb ik geen grijze cellen gebruikt ;) maar blauwe.
En ik heb geen tussenkolommen gebruikt.

Wat heb ik wél gedaan:
de basis is de volledige tijd tussen begin- en eindtijd (D19-C19), daarvan afgetrokken:
het aantal werkdagen tussen C19 en D19 (beide inclusief) vermenigvuldigd met 13:00 uur: -NETTO.WERKDAGEN(C19;D19;$V$3:$V$15)*"13:00"
dan is er teveel afgetrokken en moet weer opgeteld worden:
a. Als begonnen is op een werkdag: de tijd tussen 6:00 uur en de begintijd van de werkzaamheden op die dag (minimaal 0 en maximaal 13:00 uur)
+NETTO.WERKDAGEN(C19;C19;$V$3:$V$15)*MAX(0;MIN(--"13:00";REST(C19;1)-"6:00"))
en
b. Als geëindigd is op een werkdag: de tijd tussen de eindtijd van de werkzaamheden op die dag en 19:00 uur (minimaal 0 en maximaal 13:00 uur).
+NETTO.WERKDAGEN(D19;D19;$V$3:$V$15)*MAX(0;MIN(--"13:00";"19:00"-REST(D19;1)))

En zo ben ik gekomen op de formule in AC19 en naar beneden gekopieerd:
Code: 
=(D19-C19)-NETTO.WERKDAGEN(C19;D19;$V$3:$V$15)*"13:00"+
NETTO.WERKDAGEN(C19;C19;$V$3:$V$15)*MAX(0;MIN(--"13:00";REST(C19;1)-"6:00"))+
NETTO.WERKDAGEN(D19;D19;$V$3:$V$15)*MAX(0;MIN(--"13:00";"19:00"-REST(D19;1)))
 

Bijlagen

Laatst bewerkt:
Tja, daar staan we dan met onze 57 hulpkolommen.....:o
 
Mijn dank is groot,

ik ga er morgen verder mee puzzelen en kijken of de oplossing er tussen zit. Ik ga er bijvoorbeeld wat tijdsperiodes met weekenden/feestdagen en wwrkdagen ingooien om te testen (hierdoor raakte ik de weg kwijt met mijn oplossing die niet bleek et werken).

Ik zie in ieder geval dat ik veel te moeilijk denk en te snel enorme koppelingen maak in 1 cel.

Voor de nieuwsgierigen, het gaat om een uitzoekwerk voor het na facturen van abonnementhouders, die alleen betalen voor de periode 06:00/19:00 op werkdagen.

Ik kom met bovenstaande oplossingen al een heel stuk verder
 
Marcel en WHER,
het zou handig zijn als je ipv de getallen 6:00 en 19:00 verwijzingen gebruikt naar cellen waar deze waarden staan.
 
Ik heb de formule gebruikt zonder kolommen en ipv "13:00" "06:00" "19:00" heb ik 2 cellen met de tijdspanne ingevuld, zodat de waardes in de formules cel verwijzingen worden en de "13:00" cel met 19:00 - cel met 06:00.

Morgen worden de formules getest met een heleboel data. Als alles klopt zorg ik voor een oplossing excel zodat iedereen er baat bij heeft.

Door de uitsplitsing van kolommen bij eerdere pogingen weet ik nu hoe de formule in elkaar zit,

Bedankt voor het puzzelen.
 
Dank voor de terugkoppeling.

In 'Factuur' gebruik je directe referenties naar 'Ruwe_Data'. Dat gaat alleen goed zolang je in 'Ruwe_Data' geen regels tussenvoegt of verwijdert, anders kloppen de verwijzingen niet meer (je mag best data toevoegen/leeg maken, zolang je maar geen hele regels toevoegt/verwijdert).
 
Laatst bewerkt:
Dat is inderdaad het geval. Ik ververs elke maand de data d.m.v. kopiëren, dit vanwege de afhankelijkheid van een in en uit actie, deze kan ook 2 maanden tussentijd hebben (in theorie). Het enige euvel wat er nu nog in zit is dat de gegevens een datumweergave bevatten ../../.. die niet direct herkend wordt door excel met de huidige formules en vergt een extra tussenstap (ctr+f,/vervangen voor -). Het is nog niet helemaal netjes, maar de formules doen het in ieder geval. Bedankt voor de waarschuwing.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan