[Excel] Uren optellen

[KloyenZ]

Hallo,

Ik heb een kleine uitdaging gekregen en heb er al uren op gezocht maar krijg het maar niet voor mekaar.
Men geeft altijd een start uur en een eind uur in en die kunnen mekaar overlappen.
Per regel moeten er de extra uren getoond worden. (die in de rijen erboven nog niet geteld zijn)
Ook wordt niet alles perse chronologisch ingegeven worden.
Ik geef hieronder een voorbeeld

Start uur	Eind uur	Uren
09:00	11:00	2:00
08:00	10:00	1:00
08:00	12:30	1:30

Zoals je kan zien mogen enkel de extra uren (die nog niet geteld geweest zijn in de bovenstaande rijen) er achteraan bijgezet worden.

Je zou dit kunnen oplossen met MIN en MAX maar er kunnen ook pauze's tussen in zijn dus dat er bv. van 12:30 tot 13:30 niets ingegeven is en dus moet die tijd ook niet geteld worden.

Ik heb al met verschillende ALS gewerkt maar ik geraak er niet uit.
Kan er mij iemand op weg zetten ?

Mvg,

KloyenZ

[Bolukan]

Pfff, dat is volgens mij niet in 1 formule te doen. Wel een leuke uitdaging!!

Het kan wel zo:
Cellen A5:B7 De start en eindtijden conform jouw overzicht.
Cellen D1:G2 Alle tijden die mogelijk zijn in volgorde zetten: D1: 8; D2: 9, etc met 9-10, 10-11, 11-12.30
Cel D5:G7 een formule (voorbeeld is cel D5:)

Visual Basic:

=ALS(EN($A5<=D$1;$B5>=D$2);(D$2-D$1)-SOM(D$4:D4);0)

En dan tenslotte in cel C5:C7 alles optellen (voorbeeld is cel C5:)

Visual Basic:

=SOM(D5:G5)

Je zou eventueel een macro kunnen maken die de cellen D1:G2 aanmaakt.

[Bolukan]

Zo ziet het er uit:

	A	B	C	D	E	F	G
1				8:00	9:00	10:00	11:00
2				9:00	10:00	11:00	12:30
3	Start	Eind	Uren
4				0	0	0	0
5	9:00	11:00	2:00	0:00	1:00	1:00	0:00
6	8:00	10:00	1:00	1:00	0:00	0:00	0:00
7	8:00	12:30	1:30	0:00	0:00	0:00	1:30

edit: typo

[ Voor 9% gewijzigd door Bolukan op 20-10-2007 20:26 ]

[KloyenZ]

Hallo,

Is er geen andere methode want dit vind ik nogal ingewikkeld.

Thanks,

Kevin

[Witte]

Ja, je zet alle switch-tijden op volgorde, reken de tijden uit tussen de switch-tijden, en tel ze op als er gewerkt is.

[TXC]

Ik heb nog een andere oplossing voor je:

Start uur	Eind uur	Uren	Vroegste begintijd	Laatste eindtijd
09:00	11:00	2:00	9:00	11:00
08:00	10:00	1:00	8:00	11:00
08:00	12:30	1:30	8:00	12:30

Formules voor cellen C2, D2, en E2:

code:

C2:=B2-A2
D2:=A2
E2:=B2

Formules voor cellen C3, D3 en E3 (en dan doortrekken naar onder:

code:

C3:=ALS(A3<D2; D2-A3; 0) + ALS(B3>E2; B3-E2; 0)
D3=ALS(A3<D2; A3; D2)
E3:=ALS(B3>E2; B3; E2)

De extra kolommen kun je eventueel verbergen.

edit:
Je kunt natuurlijk ook gewoon min en max gebruiken zoals je zelf al zegt

[ Voor 5% gewijzigd door TXC op 25-10-2007 14:07 ]

[KloyenZ]

en wat als er dan tussenin een uur niet gewerkt is ?

[TXC]

KloyenZ schreef op donderdag 25 oktober 2007 @ 21:20:
en wat als er dan tussenin een uur niet gewerkt is ?

Die neemt hij in mijn voorbeeld toch niet mee? Hij pakt alleen per ingegeven tijd het stuk van zijn begintijd tot de begintijd die al eerder is opgegeven en het stuk van de eindtijd die al is opgegeven tot zijn eindtijd (als de eindtijd later is dan eerder al is opgegeven). Het tussenurenprobleem heb je zo dus volgens mij niet.

[BoutWout]

Er is ook een dagen functie =DAGEN360 waarmee je uren kunt optellen, moet je wel data specificeren. Hiermee moet het ook kunnen.

Een urenbriefje dat ik wel eens gebruik =ALS(G14>0;24*(H14-G14-1/48);"") maakt van een begin uur en een vertrek uur een totaal aantal uren.

[KloyenZ]

TXC,

Je neemt toch de vroegste tijd en de laatste tijd ?
En daar tel je de uren van ?
maar wat als iemand werkt van:

8 - 10
9 - 11
11 - 12
12:30 - 13

Dan tel jij in je voorbeeld toch 5 uren terwijl het er maar 4,5 zijn. Of redeneer ik niet goed ?

Mvg,

Kevin

[TXC]

Nee daar heb jij gelijk in zo had ik er nog niet na gekeken. Zal er nog eens verder over nadenken

[KloyenZ]

TXC,

en nog een moeilijkheid.
De uren staan niet in volgorde.
kan dus ook zijn:

11 - 12
12:30 - 13
8 - 10
9 - 11

Hij moet dus in de hele kolom kijken

[Lustucru]

offtopic:
Probeer zelf ook eens wat input te geven. Dit gaat teveel de kant op van 'willen jullie het voor me oplossen' ipv 'willen jullie meedenken

Anyway, tussenuren zijn te herkennen aan het feit dat de begintijd later ligt dan de grootste eindtijd die hoort bij een kleinere begintijd. Omdat hij de hele kolom moet bekijken heb je een matrixformule nodig; de uitkomsten daarvan zet je in een hulpkolom (bv D).

code:

{=MAX(0;(A1-(MAX(ALS($A$1:$A$10<A1;$B$1:$B$10;0)))))}

Het totaal gewerkte uren is nu simpel de grootste einddtijd minus de som van het aantal tussenuren:

code:

=MAX(B1:B10)-SOM(D1:d10)