Onkosten berekening excel

[Anoniem: 1020027]

Ik heb een formule nodig om onkostenvergoeding voor mijn werknemers te bereken wie kan mij daarbij helpen

Voorbeeld

Colommen:
A: begin tijd
B: eind tijd
C: totale werk tijd per dag
D: Onkostenvergoeding

Er word onkosten berekend als de werktijd langer is dan 4uur.
De onkosten die daarbij horen zijn €0,61 per uur
Als er gewerkt word tussen 18.00 en 24.00uur word er een vergoeding betaald van €2,78 per uur i.p.v. €0,61

Welke formule kan ik gebruiken om in colom D de onkostenvergoeding te berekenen.

[MAX3400]

En hoe zou de formule op papier eruit zien als ik werk van 16:30 tot 23:15? Want dan moet je "detecteren" dat er een cutover plaatsvindt en dat de vergoeding de eerste anderhalf uur anders is dan de volgende 5 uur en een kwartier.

Misschien heb je al een formule staan maar die geeft een verkeerde uitkomst; als je die dan even met ons wil delen, is er vast een mogelijkheid dat we de fout kunnen corrigeren met je.

[Anoniem: 1020027]

Wat je zegt klopt inderdaad.

Op papier zou het er zo uitzien:
1,5uur x 0,61
5,25 x 2,78

Ik heb nog geen berekening want ik kom er niet aan uit, mijn excel kennis is wat deze berekening aangaat niet zo uitgebreid haha.

[breew]

Anoniem: 1020027 schreef op vrijdag 29 december 2017 @ 15:44:
Wat je zegt klopt inderdaad.

Op papier zou het er zo uitzien:
1,5uur x 0,61
5,25 x 2,78

Ik heb nog geen berekening want ik kom er niet aan uit, mijn excel kennis is wat deze berekening aangaat niet zo uitgebreid haha.

Als je niet zo sterk bent met excel, waarom zou je dan een dergelijk belangrijk (gaat blijkbaar om geld van werknemers) er mee willen maken? Je zult het bestand zelf moeten kunnen beheren...

Als ik jou was, zou ik het lekker op papier/handmatig doen.

Is dat geen optie, of wil je jezelf er in verdiepen, dan zou ik met vba aan de slag gaan, en een macro of custom functie bouwen die kan wat jij wilt.

Hier alvast een opzetje:
NB: Niet gebruiken als je niet weet/snapt wat het doet, anders gaat het geheid mis namelijk. Sowieso houdt deze code bijvoorbeeld al geen rekening met diensten die over een datumwissel heengaan... En er zal vast nog meer niet aan kloppen voor jouw specifieke use case...

Visual Basic:

Option Explicit

Sub onkosten()

  Dim c As Range         'celbereik
  Dim dblStart As Double 'starttijd
  Dim dblEind As Double  'eindtijd
  
  Const cdblLaag As Double = 0.61    'onkosten voor 18 uur
  Const cdblHoog As Double = 2.78    'onkosten na 18 uur
  Const cdblLaat As Double = 18 / 24 'numerieke waarde van 18:00 uur
  
  'met het huidige werkblad
  With ActiveSheet
    'loop door de geselecteerde cellen / rijen
    For Each c In Selection
      'neem de starttijd van de huiduige rij uit kolom A
      dblStart = TimeValue(.Cells(c.Row, 1).Value)
      'neem de eindtijd van de huiduige rij uit kolom B
      dblEind = TimeValue(.Cells(c.Row, 2).Value)
      'ligt de begintijd voor 18u en de eindtijd ook?
      If dblStart <= cdblLaat And dblEind <= cdblLaat Then
        'bepaal de onkosten
        .Cells(c.Row, 4).Value = (dblEind - dblStart) * cdblLaag * 24
      End If
      'ligt de begintijd voor 18u, en de eindtijd niet?
      If dblStart <= cdblLaat And dblEind > cdblLaat Then
        .Cells(c.Row, 4).Value = (18 / 24 - dblStart) * cdblLaag * 24 + (dblEind - 18 / 24) * cdblHoog * 24
      End If
    Next c
  End With
  
End Sub

[F_J_K]

@Anoniem: 1020027 welkom op GoT

Dat is niet hoe je het op papier doet, je slaat diverse stappen over. Als langer dan vier uur, dan: bereken het aantal uur tot 18u vermenigvuldigd met 0,61; tel daarbij op het aantal uur na 18u vermenigvuldigd met 2,78. En dat is precies hoe het in Excel kan.

We vragen van alle bezoekers die een vraag stellen, dat ze laten zien dat ze ook zelf tot een oplossing proberen te komen. Ik neem aan dat je de basics van Excel kent (zo niet: dat wil je eerst leren, nooit zomaar 'code' van een ander overnemen zonder het te snappen). Of iemand inhuren. al zal dat niet lukken voor 2,78 per uur

Doe dus eest zelf een poging. Je zou met functies als ALS() en optellen, vermenigvuldigen een eind moeten komen. Let wel op hoe Excel met tijd rekent. Iets als https://support.office.co...6e-49c1-8e2c-3398a7cad6ad kan dan helpen.

VBA lijkt me erg niet de bedoeling als beginner. Althans, als je niet weet wat de code hierboven doet.

[eric.1]

Zoals FJK al aangeeft, probeer uit te schrijven wat er moet gebeuren. Daar hief je geen excel-expert voor te zijn.

Er word onkosten berekend als de werktijd langer is dan 4uur.

ALS eindtijd-begintijd = groter dan 4 uur, dan;

De onkosten die daarbij horen zijn €0,61 per uur

Dan dus aantal uren (voor 1800 en na 00.00; zie onderstaand) * 0.61

Als er gewerkt word tussen 18.00 en 24.00uur word er een vergoeding betaald van €2,78 per uur i.p.v. €0,61

Dan zal je dus iets moeten maken dat controleert of de eindtijd en/of begintijd tussen die tijden valt en die uren * 2.78. Tel dit op bij de 0.61 per uur uren.

[breew]

Een eerste opzet voor een logische gedachtengang (die gevolgd kan worden door een computer):

a = starttijd
b = eindtijd
c = 18:00 uur
x = vergoeding t/m 18:00uur
y = vergoeding na 18:00 uur (tot 24:00)

er zijn nu drie mogelijkheden (controleer dit!!)
1. a en b kleiner dan c          -->    onkosten: berekening 1
2. a en b groter dan c           -->    onkosten: berekening 2
3. a kleiner, en b groter dan c  -->    onkosten: berekening 3

Hier kan je nu een Excel-formule mee bouwen:

=ALS(EN(1);berekening 1;ALS(EN(2);berekening 2;berekening 3))

Hier zit wel een (belangrijke!) aanname in, namelijk dat wanneer 'niet situatie a' en 'niet situatie 2', dan geldt situatie 3. Ga na of dat in jouw situatie ook daadwerkelijk zo is, en of er niet meer situaties te bedenken zijn.

[ Voor 11% gewijzigd door breew op 29-12-2017 19:25 ]

[Lustucru]

In plaats van de volledige berekening te herhalen voor alle situaties zou ik er de voorkeur aan geven om in de berekening de condities mee te nemen. Je krijgt dan in @breew 's schema:

a = starttijd
b = eindtijd
c = 18:00 uur
x = vergoeding t/m 18:00uur
y = vergoeding na 18:00 uur (tot 24:00)

ALS(b-a>4)
{
    de basistoeslag  -->   (b-a)*x
    plus
    
    ALS(b>18:00) 
    {
        de extra toeslag ->  trek de grootste van A en C af van B en vermenigvuldig dat met (y-x)
     }
}

Dan moet je ook nog weten dat tijden in Excel getallen zijn tussen 0 en 1, waarbij 1/24 gelijk is aan één uur. Stel A1= starttijd, B1 is eindtijd dan zou je dus iets krijgen als:

code:

=ALS((B1-A1)>(1/6);(B1-A1)*0,61*24 + ALS(B1>0,75;(B1-MAX(0,75;A1))*2,17*24;0);0)

In plaats van als(b>c;berekening;0) kun je ook ((b>c)*berekening) nemen, omdat Excel onwaarheden als 0 berekent. Omdat je de werktijd al in C1 hebt staan kun je het inkorten tot

code:

=(C1>(1/6))*24*(C1*0,61+(B1>0,75)*(B1-MAX(0,75;A1))*2,17)

offtopic:
Toch nog uitgewerkt, maar neem ajb bovenstaande opmerkingen van harte: neem nooit iets zomaar over van het internet. Zelfs niet van tweakers.

[ Voor 14% gewijzigd door Lustucru op 29-12-2017 23:29 ]

[Anoniem: 1020027]

Lustucru schreef op vrijdag 29 december 2017 @ 22:10:
In plaats van de volledige berekening te herhalen voor alle situaties zou ik er de voorkeur aan geven om in de berekening de condities mee te nemen. Je krijgt dan in @breew 's schema:

a = starttijd
b = eindtijd
c = 18:00 uur
x = vergoeding t/m 18:00uur
y = vergoeding na 18:00 uur (tot 24:00)

ALS(b-a>4)
{
    de basistoeslag  -->   (b-a)*x
    plus
    
    ALS(b>18:00) 
    {
        de extra toeslag ->  trek de grootste van A en C af van B en vermenigvuldig dat met (y-x)
     }
}

Dan moet je ook nog weten dat tijden in Excel getallen zijn tussen 0 en 1, waarbij 1/24 gelijk is aan één uur. Stel A1= starttijd, B1 is eindtijd dan zou je dus iets krijgen als:

code:

=ALS((B1-A1)>(1/6);(B1-A1)*0,61*24 + ALS(B1>0,75;(B1-MAX(0,75;A1))*2,17*24;0);0)

In plaats van als(b>c;berekening;0) kun je ook ((b>c)*berekening) nemen, omdat Excel onwaarheden als 0 berekent. Omdat je de werktijd al in C1 hebt staan kun je het inkorten tot

code:

=(C1>(1/6))*24*(C1*0,61+(B1>0,75)*(B1-MAX(0,75;A1))*2,17)

offtopic:
Toch nog uitgewerkt, maar neem ajb bovenstaande opmerkingen van harte: neem nooit iets zomaar over van het internet. Zelfs niet van tweakers.

Bedankt voor je antwoord. Berekening klopt precies!

[Anoniem: 1020027]

Bedankt iedereen voor de hulp ik zou echter nog een berekening nodig hebben kunnen jullie mij hier ook aan helpen.

Per diensttijd word er gewerkt met een pauzestaffel deze is als volgt
30 minuten pauze bij een diensttijd van 4,5 tot 7,5uur
60 minuten pauze bij een diensttijd van 7,5 tot 10,5uur
90 minuten pauze bij een diensttijd van 10,5 tot 13,5 uur
120 minuten pauze bij een diensttijd van 13,5 tot 16,5uur
150 minuten pauze bij een diensttijd van 16,5uur of meer

Stel ik heb in kolom A de totale diensttijd staan en ik wil in kolom B de pauze berekend hebben die bij die diensttijd hoort welke formule zou ik dan kunnen gebruiken?

Ik ben zelf al heel veel met de "als" functie aan het proberen geweest en heel veel gelezen maar het gaat mijn excel kennis helaas te boven.

[F_J_K]

Verticaal zoeken. Met die functie zou je de juiste staffel moeten kunnen vinden. Check wat voorbeeldgebruik onder de Excel hulp, of hier op het forum.

[loeberce]

Dat is een geneste IF-formule:
=IF(AND(A1>=4.5;A1<7.5);30;IF(AND(A1>=7.5;A1<10.5);60;IF(AND(........)))

Als je de formule doorloopt komt je eerst de als-functie tegen; hierin noem je dat a1 groter/gelijk moet zijn dan 4.5 én kleiner dan 7.5. Als dit waar is dan is de uitkomst 30 (minuten). Bij onwaar gaat de volgende IF-formule lopen.
Let aan het eind even op het totaal aantal haakjes sluiten.

F_J_K schreef op woensdag 3 januari 2018 @ 10:17:
Verticaal zoeken. Met die functie zou je de juiste staffel moeten kunnen vinden. Check wat voorbeeldgebruik onder de Excel hulp, of hier op het forum.

Dat kan ook maar dan moet je eerst elders de staffel definiëren. En met een bereik van 4.5-7.5 uur is dat niet eens zo makkelijk.

[ Voor 33% gewijzigd door loeberce op 03-01-2018 10:19 ]

[Anoniem: 1020027]

loeberce schreef op woensdag 3 januari 2018 @ 10:17:
Dat is een geneste IF-formule:
=IF(AND(A1>=4.5;A1<7.5);30;IF(AND(A1>=7.5;A1<10.5);60;IF(AND(........)))

Als je de formule doorloopt komt je eerst de als-functie tegen; hierin noem je dat a1 groter/gelijk moet zijn dan 4.5 én kleiner dan 7.5. Als dit waar is dan is de uitkomst 30 (minuten). Bij onwaar gaat de volgende IF-formule lopen.
Let aan het eind even op het totaal aantal haakjes sluiten.


[...]

Dat kan ook maar dan moet je eerst elders de staffel definiëren. En met een bereik van 4.5-7.5 uur is dat niet eens zo makkelijk.

Ik ben aan het proberen met deze som maar werkt dit ook met een tijds berekening? Want ik krijg iedere keer de melding dat de som fout is

[loeberce]

Anoniem: 1020027 schreef op woensdag 3 januari 2018 @ 11:08:
[...]


Ik ben aan het proberen met deze som maar werkt dit ook met een tijds berekening? Want ik krijg iedere keer de melding dat de som fout is

Wat heb je nu in de formule staan dan? En gebruik je de evalueer-functie van Excel? Die is er goed in het opsporen van foutjes. Afhankelijk van NL/US instellingen van Excel moet je ook , of ; gebruiken.

[Anoniem: 1020027]

loeberce schreef op woensdag 3 januari 2018 @ 11:10:
[...]

Wat heb je nu in de formule staan dan? En gebruik je de evalueer-functie van Excel? Die is er goed in het opsporen van foutjes. Afhankelijk van NL/US instellingen van Excel moet je ook , of ; gebruiken.

Deze formule heb ik nu:

=ALS(EN(A1>=4.5;A1<7.5);30;ALS(EN(A1>=7.5;A1<10.5);60;ALS(EN(A1>=10.5;A1<13.5);90;ALS(EN(A1>=13.5;A1<16.5);120)))

[loeberce]

Er mist een haakje sluiten aan het eind en een ONWAAR-statement in de laatste ALS-formule. Je kan deze 150 maken, maar dan loop je het risico dat waarden < 4.5 ook 150 als pauze krijgen toegewezen.

code:

=ALS(EN(A1>=4.5;A1<7.5);30;ALS(EN(A1>=7.5;A1<10.5);60;ALS(EN(A1>=10.5;A1<13.5);90;ALS(EN(A1>=13.5;A1<16.5);120;ALS(A1>=16.5;150;0)))))

[Anoniem: 1020027]

In A1 staat wel een tijdsnotitie houd de som hier rekening mee? Want als ik hem overneem werkt hij niet

[loeberce]

Anoniem: 1020027 schreef op woensdag 3 januari 2018 @ 11:31:
In A1 staat wel een tijdsnotitie houd de som hier rekening mee? Want als ik hem overneem werkt hij niet

Het helpt als je wat duidelijker bent. Wat is een tijdsnotitie? Dit is vaak een weergave van een getal. Wat zie je als je de getalnotatie aanzet? En hoe merk je dat het niet werkt?
Waarschijnlijk moet je alle tijdsduren door 24 delen. Dus 4.5 wordt .1875. Enzovoort.

[ Voor 9% gewijzigd door loeberce op 03-01-2018 11:38 ]

[Anoniem: 1020027]

Als ik het door 24 deel klopt de formule wel top. Loop nu alleen tegen een ander probleem aan haha..

De tijd die in A1 staat is berekend door de begin en eindtijd van elkaar af te trekken zodat je de totale werktijd in kolom A1 krijgt. Hij geeft nu alleen aan
"dat er een kringverwijzing is aangetroffen waarbij een formule direct of indirect naar de eigen cel verwijst" Kan ik dit nog aanpassen?

Wat er gebeurd in mijn sheet is dat de begintijd in B1 staat de eindtijd in C1 de totale werktijd komt in E1 dit word eerst berekend. Dan rekent de pauzestaffel formule de pauze uit maar deze moet weer van E1 afgetrokken worden. Dus eigenlijk maak ik 2 berekeningen op een vak in dit geval E1.

Hier had ik nog niet over nagedacht dat ik 2x op een vak een formule maak. Weet ook niet of dat kan in excel eigenlijk haha

[loeberce]

Anoniem: 1020027 schreef op woensdag 3 januari 2018 @ 12:02:
Als ik het door 24 deel klopt de formule wel top. Loop nu alleen tegen een ander probleem aan haha..

De tijd die in A1 staat is berekend door de begin en eindtijd van elkaar af te trekken zodat je de totale werktijd in kolom A1 krijgt. Hij geeft nu alleen aan
"dat er een kringverwijzing is aangetroffen waarbij een formule direct of indirect naar de eigen cel verwijst" Kan ik dit nog aanpassen?

Wat er gebeurd in mijn sheet is dat de begintijd in B1 staat de eindtijd in C1 de totale werktijd komt in E1 dit word eerst berekend. Dan rekent de pauzestaffel formule de pauze uit maar deze moet weer van E1 afgetrokken worden. Dus eigenlijk maak ik 2 berekeningen op een vak in dit geval E1.

Hier had ik nog niet over nagedacht dat ik 2x op een vak een formule maak. Weet ook niet of dat kan in excel eigenlijk haha

Ik zou in dit geval twee kolommen maken met bruto en netto werktijd. Bruto = eindtijd-begintijd. Netto is eindtijd-begintijd-pauze.

[Anoniem: 1020027]

loeberce schreef op woensdag 3 januari 2018 @ 12:04:
[...]

Ik zou in dit geval twee kolommen maken met bruto en netto werktijd. Bruto = eindtijd-begintijd. Netto is eindtijd-begintijd-pauze.

Heb het zo gedaan werkt super! Bedankt voor je hulp