• 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.

tijd berekenen op basis van huidige tijd, dag van de week.

Status
Niet open voor verdere reacties.
H

hospelino

Gebruiker
Lid geworden
14 sep 2018
Berichten
28
Is er een formule die weer geeft op basis van de huidige tijd (zie cel A1) hoeveel tijd in uren ik nog over heb voor die week.
Om te checken of het aantal capaciteits uren (in dit geval 28,5 uur) nog haalbaar is voor die week?


Zie bijgevoegd documentjeBekijk bijlage test tijden.xlsx
 
De vraag lijkt eenvoudig, maar het antwoord vinden is knap lastig.
Het rekenen met datums en tijden is 1 van de onderdelen van mijn project "Planning in Excel"
Tijdens mijn vakantie heb ik 3 VBA UDF's gemaakt (als ontspanning :)):
1. Bereken de einddatum-tijd vertrekkend van een startdatum-tijd, doorlooptijd, werktijdenkalender en feestdagen.
2. Bereken de startdatum-tijd om een bepaalde einddatum-tijd te halen met gegeven doorlooptijd, werktijdenkalender en feestdagen.
3. Bereken het aantal werkuren tussen de startdatum-tijd en einddatum-tijd voor een bepaalde werktijdenkalender en rekening houden met feestdagen.
Het vergt wat ingave van planning-technishe gegevens zoals te zien in werkbladen: Kalender, Feestdagen en FD_Indeks (hulpje om feestdagen te toetsen)
Afijn een lang verhaal voor een kort antwoord: 24 uur (zie sheet: TestData in cell: D4)
Zie bijlage....
 

Bijlagen

misschien zoek je een eenvoudig vba resttijd formuletje: hij kan eenvoudig uit gebreid worden met feestdagen als je dat wil.
zie grijze cellen het grote blok is je wekelijkse werktijden tabel .
 

Bijlagen

@sylvester-ponte,
Mooie oplossing en werkt snel.
Echter als de deadline niet ma 24-09-2018 00:00 is maar bijv. za 22-09-2018 10:00 dan is de uitkomst: 23:45
en dat zou dan 26:30 moeten zijn.
Het euvel is eenvoudig op te lossen door de For - Next loop 1 dag extra te geven.
Code: 
For D = Van To Tot [COLOR="#FF0000"]+ 1[/COLOR]

P.S. Deze code kan ik goed gebruiken voor mijn project "Planning in Excel" Dus een mooi gevalletje van Win-Win :)
 
Piet, bedankt voor je debugging je hebt helemaal gelijk dat het fout gaat. het moet zijn:
Code: 
For D = [COLOR="#FF0000"]Int[/COLOR](Van) To Tot

ps dit is niet de snelste oplossing, het kan qua rekentijd behoorlijk sneller.
als je dat wil, hoor ik het wel.
 

Bijlagen

Laatst bewerkt:
ok Sylvester,
We wachten even af wat de TS er van vind.
En als ik verder wil verdiepen, zal ik een nieuw topic starten.
Ik ben toch nu meteen benieuwd hoe je zo'n VBA functie nog sneller kan maken.
 
in de huidige function:
Code: 
Function testTijd(Van As Date, Tot As Date, Tabel As Range) As Date
    Dim Kolom, Rij, D, Van2 As Date, Tot2 As Date
With WorksheetFunction
    For D = Int(Van) To Tot
        Kolom = DatePart("w", D, vbMonday)
        For Rij = 1 To Tabel.Rows.Count Step 2
            Van2 = Tabel(Rij, Kolom) + Int(D)
            Tot2 = Tabel(Rij + 1, Kolom) + Int(D)
            testTijd = testTijd + .Max(0, .Min(Tot, Tot2) - .Max(Van, Van2))
        Next
    Next
End With
End Function
wordt voor elke dag een loop doorlopen.
terwijl voor alle hele dagen de waarde al in de tabel staat.
dus eigenlijk hoef je alleen voor de eerste dag en de laatste dag het loopje te doorlopen.
dat scheelt rekentijd, vooral als er over veel dagen gekeken wordt.
dan krijg je bvb zo iets:
Code: 
Function testTijd2(Van As Date, Tot As Date, Tabel As Range) As Date
    Dim Kolom, Rij, D, Van2 As Date, Tot2 As Date
    D = Van: GoSub bereken_dag 'eerste dag
    D = Tot: GoSub bereken_dag 'laatste dag
    For D = Int(Van) + 1 To Int(Tot) - 1 'de hele tussen dagen direct uit tabel halen
        Kolom = DatePart("w", D, vbMonday)
        testTijd2 = testTijd2 + Tabel(0, Kolom) 'hier staat in het voorbeeldbestand de totale dag_tijd
    Next
Exit Function
bereken_dag:
With WorksheetFunction
    Kolom = DatePart("w", D, vbMonday)
    For Rij = 1 To Tabel.Rows.Count Step 2
        Van2 = Tabel(Rij, Kolom) + Int(D)
        Tot2 = Tabel(Rij + 1, Kolom) + Int(D)
        testTijd2 = testTijd2 + .Max(0, .Min(Tot, Tot2) - .Max(Van, Van2))
    Next
End With
Return
End Function
 
Laatst bewerkt:
Dat klinkt logisch, Sylvester.
Ik heb nog wat getest en Excel lijkt soms iets te onnauwkeurig met datumberekeningen.
Soms volgt uit een berekening dat de uitkomst minder dan een duizendste seconde lager uitvalt.
Op zich zeg je dan: nou en, maar als die waarde op ));)) uur uitkomt en je berekent de integer, dan valt de uitkomst 1 dag lager uit. En dat is dus wel VEEL.
Ik heb jou functie de: as Date verandert in: as Double en dan gaat het een stuk beter.
ook kun je dan beter FIX nemen om af te ronden i.p.v. INT
 
sylvester-ponte zei:
in de huidige function:
Code: 
Function testTijd2(Van As Date, Tot As Date, Tabel As Range) As Date
    Dim Kolom, Rij, D, Van2 As Date, Tot2 As Date
    D = Van: GoSub bereken_dag 'eerste dag
    D = Tot: GoSub bereken_dag 'laatste dag
Klik om te vergroten...

Deze code maakt zeker minder loops.
Als de Van en Tot datums op 1 dag vallen dan moet 1 van de 2 niet berekend worden.
Verder doet de code het goed.

Ik ga nu eens nadenken over het rekening houden met feestdagen.
En later ook nog over de mogelijkheid van mensen die op even weken een ander patroon hebben dan op oneven weken.
(bijv. Op even weken werkt een persoon niet op maandagochtend en op oneven weken is die vrijdagmiddagen vrij)
Daarvoor zal ik een nieuw topic starten.
 
piet , je hebt weer prima debug werk gedaan :thumb: het moet zijn:
Code: 
Function testTijd2(Van As Date, Tot As Date, Tabel As Range) As Date
    Dim Kolom, Rij, D, Van2 As Date, Tot2 As Date
    D = Van: GoSub bereken_dag 'eerste dag
    If int(van)<>int(tot) then D = Tot: GoSub bereken_dag 'laatste dag
    For D = Int(Van) + 1 To Int(Tot) - 1 'de hele tussen dagen direct uit tabel halen
        Kolom = DatePart("w", D, vbMonday)
        testTijd2 = testTijd2 + Tabel(0, Kolom) 'hier staat in het voorbeeldbestand de totale dag_tijd
    Next
Exit Function
bereken_dag:
With WorksheetFunction
    Kolom = DatePart("w", D, vbMonday)
    For Rij = 1 To Tabel.Rows.Count Step 2
        Van2 = Tabel(Rij, Kolom) + Int(D)
        Tot2 = Tabel(Rij + 1, Kolom) + Int(D)
        testTijd2 = testTijd2 + .Max(0, .Min(Tot, Tot2) - .Max(Van, Van2))
    Next
End With
Return
End Function
 
Ok Sylvester,
Ik ben helemaal blij met jou oplossing (en ga het zeker gebruiken)
Ik hoop dat de TS (hospelino) er ook zo in staat..
Hij is nog maar pas op deze site en krijgt misschien geen alerts ??
Of hebben wij (de helpers) zijn topic gekaapt ? :(
 
Er is inderdaad een hoop gebeurd hier de afgelopen dagen. Ben een paar dagen weg geweest.
In ieder geval toch bedankt voor de info, als moet ik zeggen dat het voor mij toch allemaal best moeilijk is
aangezien ik op dat gebied niet veel weet over al die formules etc..

Met die tijden wilde ik proberen om in mijn planning er iets in te kunnen verwerken. Het gaat me op deze manier alleen niet lukken denk ik...

Bekijk bijlage PLANNING rev.3.1.xlsm Dit is het documentje waar het om gaat.


Hierbij worden de productie ingevuld in minuten en men kan deze gereedmelden door een "v"tje te typen.
De waarde in cel productie wordt dan overgenomen.

Zoals je ziet blijft boven aan een waarde over aan openstaande uren.

Nu was ik benieuwd of aan de hand van deze openstaande uren kan weergegeven worden wat er nog aan resturen over is voor die week als het bijvoorbeeld midden van de week is?

Nogmaals als dit allemaal te moeilijk is, dan niet te veel aandacht aan schenken, het is puur voor me zelf.
Anders is het gewoon logisch het hoofd er bij houden, maar ik dacht zal eens vragen...

In ieder geval allen bedankt voor de info.
 
Ik heb in de linkerbovenhoek de resterende tijd vanaf het komende hele uur t/m het eind van de betreffende week geplaatst.
Hiervoor heb ik wat werkbladen toegevoegd: Kalenders, Feestdagen, FD_Index
Verder heb ik de VBA-functie van Sylvester (reactie #11) toegepast als UDF: testTijd2()
Ik heb er zelf wat code aan toegevoegd zodat er rekening gehouden word met Feestdagen.
Ik hoop dat dit antwoord geeft op jou vraag.
 

Bijlagen

Kalenders_afbeelding.jpg

Piet bedankt, dat ziet er netjes uit :)

Wat betekend Odd wks en Even wks
Kan ik hier verschillende dienstroosters in zetten door de tijden aan te passen?
 
Ja, in de praktijk kom je weleens tegen dat sommige mensen volgens afwisselende weekpatronen werken.
Ik had een collega die graag ging zeilen in het weekend, maar 2 dagen was veel te kort.
Daarom nam hij de ene week vrijdags vrij en de week daarop op maandag, zo kon hij 4 dagen gaan zeilen om de andere week.
Dus zijn werkpatroon was op even weken anders dan op oneven weken.
Het komt echter niet heel vaak voor, en in gevallen dat er geen verschil is kopieer je het weekpatroon van de oneven weken naar de even weken.
Zo ben je wel voorbereid op de toekomst.
En het is beter om dat meteen mee te nemen in je tool, want later aanpassen is veel lastiger.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan