Complexe formule naar VBA

[HSV]

@snb, die formule kon je niet verkrijgen op Helpmij.nl?

 

[snb]

@HSV

Ik was het wachten op jouw bijdrage moe
Maar ook elders heb ik slechts een zetje in de goede richting gekregen: workdays & trunc

 

[HSV]

Het spijt me dat ik dit onderwerp niet alsmaar volg en zo de vraag naar je formule hebt gemist.
Ik zag de vraag naar de formule op die andere site, misschien had je er hier op Helpmij een aparte vraag van kunnen maken.

Maar zo is het ook prima.

 

[Piet Bom]

@snb,
Deze formules komen wel heel dicht in de buurt van PERFECTIE (of moet het met een K ?)
Ik heb ze even in het testbestand gegooid, en pas in Dec-2019 vind ik 1 verschilletje
Ik heb jouw formule in stukjes geknipt en zie dan wel een merkwaardig verschil, maar snap niet waarom hij alleen daar een verschil maakt.
Het testbestand toegevoegd...……..

 

[snb]

@Pet,

Ervan uitgaande dat de begindatum in kolom A staat en de einddatum in kolom C;
de formule voor de doorlooptijd:

=(NETWORKDAYS(A3;C3;Feestdagen!$A$2:$A$154)-2)*STime+(MOD(C3;1)+1/3-MOD(A3;1))

 

[Piet Bom]

de formule voor de doorlooptijd:

=(NETWORKDAYS(A305;C305;Feestdagen!$A$2:$A$154)-2)*STime+(MOD(C305;1)+1/3-MOD(A305;1))

@snb,
Nu werken ze alle drie 100%
Geweldig !
Bedankt voor de inspanning.
De formules zijn heel compact en werken snel.
Ik ga nu deze formules in een UDF versleutelen, zodat ik ze beter kan toepassen in een planning.
Als het zover is kom ik hier terug met het resultaat.

 

[Piet Bom]

@snb,
Misschien ben ik wel een lastige TS
Maar nu de formules zo compact zijn geworden zijn we de variabelen Werkdagstarttijd en Werkdageindtijd uit het oog verloren.
Het is nu o.a. die 1/3 geworden die de startijd van 08:00u zou kunnen zijn (8/24 =1/3), maar het kan ook de werkdaguren zijn:
Ook 1/3 zijnde 16:00-08:00 = 08:00u (ook 8/24)
Nu weet ik niet hoe we die variabelen weer terug moeten krijgen.
In de bijlage heb ik de testdata aangepast met starttijd 07:00u en eindtijd 17:00u dus 10 uur per dag.
Ik ga zelf nog wel even puzzelen, want als ik de formule begrijp, moet ik hem ook kunnen aanpassen.

 

[snb]

@Piet

Je kunt gedachten lezen Waar kun je dat leren ?

Ik ga naar je bestand kijken.

 

[snb]

Alvast de Excelformules:

Zie kolommen W, Z en AC

F2: start (begin werkdag)
F3: einde (einde werkdag)
F4: doorloop (arbeidsuren per werkdag)

 

[Piet Bom]

@snb,
In 1 woord: Geweldig
Ik geloof nog steeds in mijn eigen complexe formule.
Die is echter zo complex dat ik hem nu zelf niet meer snap
Jij heb hem zo ver ingekort dat ik die weer begin te snappen.
Ik denk dat jouw formules vrij gemakkelijk in VBA (UDF) zijn te gieten.
Ik ga dat in ieder geval proberen.
Er is nog 1 mini wensje: De eerste formule is op zoek naar een Startdatum.
Als er twee antwoorden mogelijk zijn bijv. Maandag 07:15 en Vrijdag 15:45 (daar zit nog geen werk-seconde tussen)
In dat geval is maandag 07:15 de gewenste uitkomst. (die ligt aan het begin van de dag)
En bij de tweede formule zoeken we een Einddatum, dus daar is het verhaal precies andersom.
Mijn workaround daarvoor zou zijn:
Bij formule 1: Tel eerst bij de inputdatum 1 seconde op en trek die seconde van het resultaat af.
Bij formule 2: Trek van de inputdatum 1 seconde af en tel die er bij het resultaat weer op.
Ik heb het uitgeprobeerd en dat werkt goed.
De formules zouden weer een stuk minder compact zijn, maar bij VBA heeft dat minder effect.
In ieder geval hartelijk bedankt voor heb vereenvoudigen van de formules.
Ik ben echt geholpen en ga mijn project: "Planning in Excel" afmaken

 

[Piet Bom]

Heb de formule van snb omgezet naar een VBA UDF met enkelvoudige parameters.
Het resultaat is hetzelfde, maar de formules rekenen sneller dan de VBA funkties.
Misschien kan de code efficienter ?

1. Om van Startdatum naar Einddatum te komen:

Function snb_2a(sta_dat As Double, duur As Double, holidees As Range, Week_Patroon, STime, FTime, SFflag) As Double
    Dim sec_1 As Double, dt2 As Double
    sec_1 = 1 / 24 / 60 / 60
    If Week_Patroon = "" Then Week_Patroon = "0000011"
    If SFflag <> "S" And SFflag <> "F" Then SFflag = "F" 'Default Finish date for snb_2a
    sfflg = 1: If SFflag = "S" Then sfflg = -1           'Start of Finish datum berekenen
    sta_dat = sta_dat - sfflg * sec_1                    'truuk van 1 seconde optellen / aftrekken om Start c.q. Finish datum te berekenen
    duur2 = Fix((duur + sta_dat - Fix(sta_dat) - STime) / (FTime - STime))
    duur3 = (sta_dat - Fix(sta_dat) - STime + duur)
    duur4 = mod_pcb(duur3, (FTime - STime))
    dt2 = WorksheetFunction.WorkDay_Intl(sta_dat, duur2, Week_Patroon, holidees) + STime + duur4 + sfflg * sec_1
    snb_2a = Int(dt2 / sec_1 + 0.5) * sec_1              'afronden op hele seconden
End Function

2. Om van Einddatum naar Startdatum te komen:

Function snb_1a(fin_dat As Double, duur As Double, holidees As Range, Week_Patroon, STime, FTime, SFflag) As Double
    Dim sec_1 As Double, dt2 As Double
    sec_1 = 1 / 24 / 60 / 60
    If Week_Patroon = "" Then Week_Patroon = "0000011"   'Default week is with weekend on Sat & Sun
    If SFflag <> "S" And SFflag <> "F" Then SFflag = "S" 'Default Startdate for snb_1a
    sfflg = 1: If SFflag = "S" Then sfflg = -1           'Start of Finish datum berekenen
    fin_dat = fin_dat - sfflg * sec_1                    'truuk van 1 seconde optellen / aftrekken om Start c.q. Finish datum te berekenen
    duur2 = (Fix((fin_dat - Fix(fin_dat) - FTime - duur) / (FTime - STime))) + sec_1 / 1000 ' 1/1000ste sec nodig voor afrondingsfout in VBA ?
    duur3 = (fin_dat - Int(fin_dat) - STime - duur)
    duur4 = mod_pcb(duur3, (FTime - STime))
    dt2 = WorksheetFunction.WorkDay_Intl(fin_dat, duur2, Week_Patroon, holidees) + STime + duur4 + sfflg * sec_1
    snb_1a = Int(dt2 / sec_1 + 0.5) * sec_1              'afronden op hele seconden
End Function

3. Verder gebruik ik een eigen funktie voor de MOD berekening (omdat VBA die anders doet dan de gewone excel-formule MOD()

Function mod_pcb(getal, deler)
  mod_pcb = ((getal / deler) - Int(getal / deler)) * deler
End Function