[Excel] netto werktijden berekenen

[Anoniem: 1064029]

Goedemiddag,

Ik heb een soortgelijke vraag...

Op dit moment ben ik bezig met een planning binnen een productie-afdeling. Ik wil op basis van de starttijd (datum/tijd) en productietijd een eindtijd berekenen. Dit klinkt simpel, maar hierin wil ik ook de pauzes en einde werkdag meenemen. Er wordt normaliter gewerkt tussen 7:30 en 16:30 uur. Op de volgende tijdstippen wordt niet gewerkt:

- 9:45 - 10:00 uur (pauze 1)
- 12:30 - 13:00 uur (pauze 2)
- 14:45 - 15:00 uur (pauze 3)
- 16:30 - 7:30 uur (volgende dag, einde werkdag, etc.)

Sommige productie-opdrachten duren meerdere uren en overschrijden meerdere pauzes. Daarnaast kan het voorkomen dat een opdracht om 16:00 uur start en doorgaat op de volgende dag, en ook pauzes overschrijdt. Ik wil naar een formule toe die dit automatisch meewerkt. Met de ALS-functie loop ik al gauw vast, omdat er sprake is van heel veel voorwaarden.

De datum en tijdstip van de start zijn bepalend. Bijvoorbeeld...

Ik heb een productie-opdracht van 2 uur. Als ik die plan op 18-4-2018 7:30 uur, dan moet er probleemloos 18-4-2018 9:30 uur uitkomen. Als ik deze plan op 18-4-2018 12:15 uur, dan moet er 18-4-2018 14:45 uur uitkomen. En als ik een opdracht van 4 uur plan op 18-4-2018 15:30 uur, dan moet er 19-4-2018 10:45 uur uitkomen.

Ik hoor graag van jullie of dit mogelijk is. Veel werk volgens, maar alle hulp is welkom. Bij voorkeur ontvang ik graag een document met uitleg (als het mogelijk is).

Alvast bedankt!

[MAX3400]

Anoniem: 1064029 schreef op woensdag 18 april 2018 @ 12:15:
Goedemiddag,

Ik heb een soortgelijke vraag...

En na 13 jaar heb je niets in Google kunnen vinden qua oplossing Zo oud is dit topic al.

Maak even een nieuw topic en misschien dat je allicht je IF-function ook kan toelichten? Je kan met een nested IF erg ver komen maar gezien je vraagstelling, denk ik dat je 3 of 4 functies moet gaan combineren of overlappende dagen/opdrachten in andere sheets gaan "tussen berekenen".

[F_J_K]

Welkom Voor de volledigheid: ik neem aan dat je Het algemeen beleid #topicplaatsen hebt gezien.

Zoals ik in F_J_K in "Excel, dag van 8 uur ipv 24 uur" al aangaf, heb ik je vraag verplaatst naar een eigen topic.

Kan je inderdaad de ALS() geven? Ik zie 'slechts' vier pauzes, dat moet passen?

Je kunt geen kant en klaar document verwachten, of zelfs kant en klare code. Dan kan je beter iemand inhuren om het te doen. We denken graag mee, maar de grens ligt bij meedenken

[breew]

Excel zet tijden om naar een getal tussen 0 en 1.
0 = 00:00 uur
1 = 00:00 uur de volgende dag

Als je met tijden gaat rekenen, is het handig om die altijd even om te zetten naar numerieke waarden...
Dit kan (eenvoudig) met de functie NUMERIEKE.WAARDE()

Zo is je pauze1 (9:45 - 10:00) dus van 0,40625 tot 0,41667, en heeft een duur van: 0,010416667

Als de begintijd van een activiteit voor 0,40625 ligt, en de eindtijd na 0,41667, dan mag je dus een totaal van 0,01416667 van de duur van die activiteit aftrekken om duur van 'pauze 1' te verrekenen...

Giet dat in een aantal ALS() functies (het maximaal aantal geneste ALS ligt geloof ik, inmiddels, op 64) en presto...

inspiratie / achtergrond:
https://www.exhelp.be/for...nen-met-datums-en-tijden/

[ Voor 6% gewijzigd door breew op 18-04-2018 12:56 ]

[Paul1987]

Ik heb ooit een stuk VB geschreven om het aantal netto werkdagen tussen twee data te berekenen. Ik vermoed dat deze prima aangepast moet kunnen worden om ook aan jouw eisen te voldoen.

door middel van een aantal 'IF then' statements kun je makkelijk 'tijd' toevoegen aan het resultaat dat uit deze berekening komt.

Ik zou zeggen, kijk eens of je hier iets van begrijpt en of je dit kunt aanpassen voor jouw doeleinde.

Visual Basic .NET: Functie

Public Function XLMod(a, b)
    ' This replicates the Excel MOD function
    XLMod = a - b * Int(a / b)
End Function

Visual Basic .NET: Module1

Sub work_days()

Dim wb As Workbook
Dim ws As Worksheet
Dim st As Range

Dim Start_Dag As Date
Dim Eind_Dag As Date
Dim Start_Tijd As Double
Dim Eind_Tijd As Double
Dim Tijd As Double
Dim Tijd2 As Double
Dim Tijd3 As Double
Dim Tijd4 As Double
Dim wh As Double

Dim Last_R

Set wb = Workbooks("[b]werkboek[/b].xlsm")
Set ws = wb.Sheets("[b]sheet[/b]")

With ws
Last_R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim r

For r = 2 To Last_R
If ws.Cells(r, 2).Value <> "" Then

Start_Dag = ws.Cells(r, 2).Value
If ws.Cells(r, 10).Value <> "" Then
Eind_Dag = ws.Cells(r, 10).Value
Else
Eind_Dag = 0
End If

If Not Eind_Dag <> 0 Then
Eind_Dag = Now()
End If

Start_Tijd = TimeValue("08:00:00")
Eind_Tijd = TimeValue("17:00:00")

If Application.WorksheetFunction.Or(Eind_Tijd < Start_Tijd, Eind_Dag < Start_Dag) Then
    Tijd = 0
    Else
    Tijd = Application.WorksheetFunction.NetworkDays(Start_Dag, Eind_Dag, wb.Sheets("Rekenblad").Range("A31:A40")) '<<rekenblad verwijst naar een lijst met feestdagen
    
        
        If (Start_Dag Mod 1) > Eind_Tijd Then
            Tijd2 = 1
            Else
                
            Tijd2 = (Application.WorksheetFunction.max(Start_Tijd, XLMod(Start_Dag, 1)) - Start_Tijd)
            Tijd2 = Tijd2 / (Eind_Tijd - Start_Tijd)
            Tijd2 = Tijd - (Tijd2 * Application.WorksheetFunction.NetworkDays(Start_Dag, Start_Dag, wb.Sheets("Rekenblad").Range("A31:A40")))
                
                If (XLMod(Eind_Dag, 1)) < Start_Tijd Then
                    Tijd3 = 1
                    Tijd3 = Tijd2 - Tijd3
                    Else
                    Tijd3 = Application.WorksheetFunction.NetworkDays(Eind_Dag, Eind_Dag, wb.Sheets("Rekenblad").Range("A31:A40"))
                    Tijd3 = Eind_Tijd - Application.WorksheetFunction.Min(Eind_Tijd, XLMod(Eind_Dag, 1))
                    Tijd3 = Tijd3 / (Eind_Tijd - Start_Tijd)
                    Tijd3 = Tijd2 - Tijd3
                End If
        End If
End If

wh = Eind_Tijd - Start_Tijd
ws.Cells(r, 14).Value = ((Tijd3 * (Eind_Tijd - Start_Tijd) * 24))
ws.Cells(r, 12).Value = Format(Cells(r, 2).Value, "MM-YYYY")



End If
Next r
End Sub

[breew]

Paul1987 schreef op donderdag 19 april 2018 @ 10:56:
Ik heb ooit een stuk VB geschreven om het aantal netto werkdagen tussen twee data te berekenen. Ik vermoed dat deze prima aangepast moet kunnen worden om ook aan jouw eisen te voldoen.

door middel van een aantal 'IF then' statements kun je makkelijk 'tijd' toevoegen aan het resultaat dat uit deze berekening komt.

Ik zou zeggen, kijk eens of je hier iets van begrijpt en of je dit kunt aanpassen voor jouw doeleinde.

Visual Basic .NET: Functie

Public Function XLMod(a, b)
    ' This replicates the Excel MOD function
    XLMod = a - b * Int(a / b)
End Function

Visual Basic .NET: Module1

Sub work_days()

Dim wb As Workbook
Dim ws As Worksheet
Dim st As Range

Dim Start_Dag As Date
Dim Eind_Dag As Date
Dim Start_Tijd As Double
Dim Eind_Tijd As Double
Dim Tijd As Double
Dim Tijd2 As Double
Dim Tijd3 As Double
Dim Tijd4 As Double
Dim wh As Double

Dim Last_R

Set wb = Workbooks("[b]werkboek[/b].xlsm")
Set ws = wb.Sheets("[b]sheet[/b]")

With ws
Last_R = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Dim r

For r = 2 To Last_R
If ws.Cells(r, 2).Value <> "" Then

Start_Dag = ws.Cells(r, 2).Value
If ws.Cells(r, 10).Value <> "" Then
Eind_Dag = ws.Cells(r, 10).Value
Else
Eind_Dag = 0
End If

If Not Eind_Dag <> 0 Then
Eind_Dag = Now()
End If

Start_Tijd = TimeValue("08:00:00")
Eind_Tijd = TimeValue("17:00:00")

If Application.WorksheetFunction.Or(Eind_Tijd < Start_Tijd, Eind_Dag < Start_Dag) Then
    Tijd = 0
    Else
    Tijd = Application.WorksheetFunction.NetworkDays(Start_Dag, Eind_Dag, wb.Sheets("Rekenblad").Range("A31:A40")) '<<rekenblad verwijst naar een lijst met feestdagen
    
        
        If (Start_Dag Mod 1) > Eind_Tijd Then
            Tijd2 = 1
            Else
                
            Tijd2 = (Application.WorksheetFunction.max(Start_Tijd, XLMod(Start_Dag, 1)) - Start_Tijd)
            Tijd2 = Tijd2 / (Eind_Tijd - Start_Tijd)
            Tijd2 = Tijd - (Tijd2 * Application.WorksheetFunction.NetworkDays(Start_Dag, Start_Dag, wb.Sheets("Rekenblad").Range("A31:A40")))
                
                If (XLMod(Eind_Dag, 1)) < Start_Tijd Then
                    Tijd3 = 1
                    Tijd3 = Tijd2 - Tijd3
                    Else
                    Tijd3 = Application.WorksheetFunction.NetworkDays(Eind_Dag, Eind_Dag, wb.Sheets("Rekenblad").Range("A31:A40"))
                    Tijd3 = Eind_Tijd - Application.WorksheetFunction.Min(Eind_Tijd, XLMod(Eind_Dag, 1))
                    Tijd3 = Tijd3 / (Eind_Tijd - Start_Tijd)
                    Tijd3 = Tijd2 - Tijd3
                End If
        End If
End If

wh = Eind_Tijd - Start_Tijd
ws.Cells(r, 14).Value = ((Tijd3 * (Eind_Tijd - Start_Tijd) * 24))
ws.Cells(r, 12).Value = Format(Cells(r, 2).Value, "MM-YYYY")



End If
Next r
End Sub

Bedoel je dat je de Excel-functie NETTO.WERKDAGEN() hebt nagebouwd?

[Paul1987]

breew schreef op donderdag 19 april 2018 @ 12:10:
[...]


Bedoel je dat je de Excel-functie NETTO.WERKDAGEN() hebt nagebouwd?

Nettowerkdagen+. Houdt rekening met start en eindtijden van een werkdag (8 tot 5). Dus als je start op 1-1-2018 om 06:00 en eindigt op 3-1-2018 om 15:00 dan berekent hij:
1. Op 1-1 vanaf 08:00 (min 2 uur) t/m 17:00
2. op 2-1 de hele werkdag
3. op 3-1 van 08:00 t/m 15:00 (- 2 uur)
m.a.w. net.werkdagen voor deze tijd zou worden 3.
Mijn berekening zegt 2,77

Dit lijkt weinig verschil te maken maar wanneer je rapporteert over doorlooptijden die gebonden zijn aan werkdagen en daar een KPI/Control op rust. Dan kan dit het verschil maken tussen een succes en een tik op je vingers