Uren automatisch berekenen in excel

[pipo5874]

Ik denk wel dat er een fout zit in de formule van de daguren. Als ik daar iets invul verandert er niks.

Bekijk bijlage uren in tijdsvakken.xlsx

 

[popipipo]

Let even goed op wat je in D4 zet, uren na 24:00 uur moet je doortellen dus 2:00 uur tik je in als 26:00

Alleen in cel D4 moet je de uren door tellen.
Niet in cellen B5 en C3

 

[plongske]

Kan je deze even testen?

 

[popipipo]

Van 21:00 tot 9:00 uur gaat het niet goed

 

[cow18]

zie bijlage kolommen Q:T

 

[plongske]

@ Willem, bedankt voor de opmerking.
bij deze aangepast.

 

[plongske]

Oeps nog een foutje gevonden en aangepast.

 

[pipo5874]

Dank u allemaal, ik wordt hier overstelpt met oplossingen. dat vindt ik super. Qua ontwerp vindt ik wel dat die van Popi het best overeenkomt met wat ik meestal gebruikte. Maar daar weet ik echter niet welke formule ik moet plakken bij zondag dag en zondag nacht. Want als ik daar een gewonen weekdag noteer zal ik die formule waarschijnlijk niet op die lijn mogen plaatsen want dan zal excel deze lijn ook aanzien als een zaterdag of zondag?

Bekijk bijlage uren splitsen over dag nacht avond opzetje.xlsx

 

[cow18]

zie bijlage
je moet enkel de kolommen B aanvullen de rest wordt zelf toegevoegd omdat het een tabel is.
einduur mag ook het gewone uur zijn, dus zonder 24 uur extra toe te voegen, zie laatste rij, die voorwaardelijke opmaak verbeter ik straks zodat die niet oranje wordt omdat er 24 uur verschil op zit.

 

[pipo5874]

Het werkt niet bij mij. ik krijg steeds de volgende melding: automatisatiefout. en dan geeft hij deze foutcode op:

Option Explicit
Option Compare Text

Sub xxx()
MsgBox Time(1.25)
End Sub


Function Uren(Datum, Begin, Einde, Wat)
Dim u(1 To 4), d1 As Date, d2 As Date, dVan As Double, dTot As Double, Dag, Weekdag%, b, e
'b = TimeValue(Replace(Begin, ".", ":")): e = TimeValue(Replace(Einde, ".", ":"))
b = Begin: e = Einde
d1 = Datum + b
d2 = Datum + e - (b >= e)
Do
dVan = d1 - Int(d1): dTot = IIf(Int(d1) = Int(d2), d2 - Int(d2), 1): Weekdag = Weekday(d1, 2)
Select Case Wat
Case "MaNacht": If Weekdag = 1 Then Uren = Uren + Overlap(dVan, dTot, 0, TimeSerial(6, 0, 0))
Case "DDW dag": If WorksheetFunction.Median(1, 5, Weekdag) = Weekdag Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(6, 0, 0), TimeSerial(22, 0, 0))
Case "DDW Nacht"
If WorksheetFunction.Median(1, 5, Weekdag) = Weekdag Then
If Weekdag <> 1 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(0, 0, 0), TimeSerial(6, 0, 0))
If Weekdag <> 5 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(22, 0, 0), 1)
End If
Case "VrNacht"
If WorksheetFunction.Median(5, 6, Weekdag) = Weekdag Then
If Weekdag = 5 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(22, 0, 0), 1)
If Weekdag = 6 Then Uren = Uren + Overlap(dVan, dTot, 0, TimeSerial(6, 0, 0))
End If
Case "Zaterdagnacht"
If WorksheetFunction.Median(7, 6, Weekdag) = Weekdag Then
If Weekdag = 6 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(22, 0, 0), 1)
If Weekdag = 7 Then Uren = Uren + Overlap(dVan, dTot, 0, TimeSerial(6, 0, 0))
End If
Case "Zaterdag dag": If Weekdag = 6 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(6, 0, 0), TimeSerial(22, 0, 0))
Case "Zondag dag": If Weekdag = 7 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(6, 0, 0), TimeSerial(22, 0, 0))
Case "Zondagnacht": If Weekdag = 7 Then Uren = Uren + Overlap(dVan, dTot, TimeSerial(22, 0, 0), 1)
End Select
d1 = Int(d1 + 1)
Loop While d1 <= d2
Uren = Uren * 24
End Function

Function Overlap(Van1, Tot1, Van2, Tot2)
Dim W1 As Boolean, W2 As Boolean, W3 As Boolean, W4 As Boolean, Temp
If Van1 > Tot1 Then Temp = Van1: Van1 = Tot1: Tot1 = Temp
If Van2 > Tot2 Then Temp = Van2: Van2 = Tot2: Tot2 = Temp
W1 = Van1 < Van2
W2 = (Van1 < Tot2) And Not W1
W3 = Tot1 < Van2
W4 = (Tot1 < Tot2) And Not W3
Overlap = (Tot2 - Van2) * (W3 - W1) - (Tot2 - Van1) * W2 + (Tot2 - Tot1) * W4
End Function

 

[plongske]

De uren zijn bij ons als volgt ingedeeld:

dag: 06:00 tot 22:00
nacht: 22:00 tot 06:00
zaterdag: 00:00 tot 23:59
zondag: 00:00 tot 23:59

Maar dan zit ik nog met het volgende probleem;

Als een werknemer werkt van bijvoorbeeld 20:00 tot 04:00 op een vrijdag, dan zitten hier 2 daguren in, 2 nachturen, 4 zaterdaguren en 4 zaterdag nachturen. Onze prijzen zijn zo opgemaakt in onze CAO.

Hier kan ik niet meer volgen? De zaterdag zijn er toch geen dag en nachturen volgens uw opgegeven schema?

 

[pipo5874]

nee dat klopt, dat is een foutje van mij sorry

 

[cow18]

bovenstaande vereenvoudiging van het aantal kolommen is later gemakkelijk door te voeren.
De automatiseringsfout waarvan sprake kan ik moeilijk plaatsen, je toont de ganse code, is er geen rij geel (of een andere kleur) gekleurd ? Dat zou me een eind verder helpen. Ik heb wat extra commentaar toegevoegd aan de macro.
De C en D-kolom zijn in de opmaak [uu]:mm, dus kan je daar ook uren >=24:00 ingeven
Als C-kolom kleiner is dan de D-kolom, dan wordt er verondersteld dat het einduur de volgende dag ligt.
bv; rij 7, de uren over 7 dagen (=1 week), dus van 00:00 tot 168:00 (tik daar gewoon 7 in, excel vertaalt die wel naar 168:00) levert zoveel categorien aan uren op.

 

[pipo5874]

Function Uren(Datum, Begin, Einde, Wat)


Deze rij was geel

 

[pipo5874]

ik heb nu wel pas ontdekt dat op excel 2010 geen foutcode te zien is. Maar op de excel van mijn mac wel

 

[pipo5874]

De layout is wel perfect Cow, precies wat ik zocht.

 

[plongske]

Hoe zit het dan wel voor de zaterdag en Zondag?

 

[pipo5874]

De prijzen voor zaterdag en zondag worden in ons facturatieprogramma als volgt berekend: Zaterdagen 06:00 tot 22:00 en van 22:00 tot 06:00. Maar als we zaterdag van 22:00 tot 06:00 nemen, wordt er eigenlijk gerekend vanaf 00:00 tot 06:00 en ook van 22:00 tot 23:59. Zo heb je 8 nachturen op zaterdag maar die liggen tussen 00:00 en 23:59 en de uren daartussen zijn de daguren die berekend worden van 06:00 tot 22:00

 

[pipo5874]

Dat is eigenlijk heel ingewikkeld

 

[plongske]

Kan je dit even uitleggen aan de hand van een tabelletje? plaats eens enkele uren vb van vrijdagavond tot zaterdag gewerkt.
enkel zaterdag gewekt , enkel zaterdag nacht , zaterdag en zondag gewerkt.
plaats gewoon hoe het zou moeten zijn.want nu kan ik nog altijd niet volgen hoe de uren in het weekend moeten juist ingevuld worden.