Excel tijd berekenen tussen twee tijden

[arnold24]

Beste tweakers,

Wij vullen uren in op een urenbrief in Excel.
Werkdagen/nachten van bijvoorbeeld 19:00 's avonds tot 07:00 in de ochtend. (Dus 12 uur)

Wat ik nu wil weten is het aantal uren dat gewerkt is tussen 21:00 en 05:00 (Als er al gewerkt is).

Voorbeeld:
Er is gewerkt tussen 13:00 en 23:00 - hoeveel uur tussen 21:00 en 05:00
Er is gewerkt tussen 22:00 en 06:00 - hoeveel uur tussen 21:00 en 05:00

Hopelijk hebben jullie een idee

Groet
Arnold

[Lelletje]

https://exceljet.net/form...f-hours-between-two-times

Hier al naar gekeken?

[de Peer]

Even op google intoetsen inderdaad en kijken wat er uit komt rollen, had je dat al gedaan?

[arnold24]

Ik heb dergelijke formules inderdaad wel gezien. Ook heb ik google geprobeerd. Het ei heb ik helaas nog niet gevonden.

Ik wil dus tussen de werktijden weten hoeveel uur van de werkdag gemaakt is tussen 21:00 en 05:00 ivm CAO.

Gewerkt tussen 19:00 en 08:00 = 13 uur.
Tussen 21:00 en 05:00 = 9 uur.

Ideeën?

[Chris03]

Als je de formule gebruikt die in het voorbeeld hierboven staat, heb je volgens mij precies wat je nodig hebt:

=IF(end>start, end-start, 1-start+end)

Volgens mij heb je geen ei meer nodig, alleen deze formule.

[erwn]

Gewerkt tussen 21:00 en 5:00 is dan toch 8 uur?

Kun je niet afzonderlijk de tijd tussen starttijd en 0:00 enerzijds en 5:00 en eindtijd anderzijds berekenen?

Som van:

• Voor 0:00
  • Als begintijd op of voor 21:00 dan 3 uur, of
  • Als begintijd na 21:00 dan verschil tussen 0:00 en begintijd
• Na 0:00
  • Als eindtijd op of na 5:00 dan 5 uur, of
  • Als eindtijd voor 5:00 dan verschil tussen 0:00 en eindtijd

[F_J_K]

arnold24 schreef op maandag 10 januari 2022 @ 13:12:
Ik heb dergelijke formules inderdaad wel gezien. Ook heb ik google geprobeerd. Het ei heb ik helaas nog niet gevonden.

Ik wil dus tussen de werktijden weten hoeveel uur van de werkdag gemaakt is tussen 21:00 en 05:00 ivm CAO.

Gewerkt tussen 19:00 en 08:00 = 13 uur.
Tussen 21:00 en 05:00 = 9 uur.

Ideeën?

Geef dan even concreet aan welke zijn geprobeerd maar niet werken. Dan hoeven we niet te herhalen

[dragonhaertt]

Kan je er niet gewoon een IF statement van maken, waar je Cel2 - Cel1 doet, en als dat getal negatief is voeg je 24 uur toe?
24 uur is decimaal 1.0 in tijdsformaat

=IF(CEL2-CEL1>0;CEL2-CEL1;CEL2-CEL1+1)

Bovenstaand werkt bij mij prima. 1904 datum systeem, alle cellen opgemaakt in "Time" format en RC notatie: =IF(RC[-2]-RC[-3]>0;RC[-2]-RC[-3];RC[-2]-RC[-3]+1)

[Voor 43% gewijzigd door dragonhaertt op 10-01-2022 13:37]

[goldcard]

dragonhaertt schreef op maandag 10 januari 2022 @ 13:20:
Kan je er niet gewoon een IF statement van maken, waar je Cel2 - Cel1 doet, en als dat getal negatief is voeg je 24 uur toe?
24 uur is decimaal 1.0 in tijdsformaat

=IF(CEL2-CEL1>0;CEL2-CEL1;CEL2-CEL1+1)

Bovenstaand werkt bij mij prima. 1904 datum systeem, alle cellen opgemaakt in "Time" format en RC notatie: =IF(RC[-2]-RC[-3]>0;RC[-2]-RC[-3];RC[-2]-RC[-3]+1)

Ja, en hoe vang je dan alleen de uren tussen 21:00 en 05:00??

[dragonhaertt]

goldcard schreef op maandag 10 januari 2022 @ 13:51:
[...]


Ja, en hoe vang je dan alleen de uren tussen 21:00 en 05:00??

gewoon een IF statement toevoegen.
IF Tijd1 < 21:00, gebruik 21:00
IF Tijd2 > 05:00, gebruik 05:00

[dragonhaertt]

=IF(B-A>0;B-A;B-A+1)
Dan B vervangen met: IF(Tijd2>05:00;05:00;Tijd2)
en A vervangen met: IF(Tijd1<21:00;21:00;Tijd1)

=IF(IF(Tijd2>05:00;05:00;Tijd2)-IF(Tijd1<21:00;21:00;Tijd1) >0;IF(Tijd2>05:00;05:00;Tijd2)-IF(Tijd1<21:00;21:00;Tijd1) ;IF(Tijd2>05:00;05:00;Tijd2)-IF(Tijd1<21:00;21:00;Tijd1) +1)

[goldcard]

Oke, en als tijd1 = 22:00 en tijd2 = 23:00... dan is de gewenste uitkomst 1, maar jouw uitkomst 7, omdat Tijd2 (23:00) groter is dan 05:00 en de formule dus met 05:00 gaat rekenen.

Het zou voor TS makkelijker zijn wanneer het veld een dateTime veld is. Daarmee kan excel veel simpeler rekenen.

[Orion84]

Dus je wilt van het aantal gewerkte uren weten welke daarvan "gedurende de nacht (21:00-05:00)" waren.

Dan heb je dus een aantal gevallen:
start vóór 21:00, eind na 05:00 -> 8u
start vóór 21:00, eind vóór 05:00 -> 8 - (aantal uur tussen eind en 05:00)
start na 21:00, eind na 5:00 -> 8 - (aantal uur tussen 21:00 en start)
start na 21:00, eind vóór 5:00 -> aantal uur tussen start en eind
start na 05:00, eind vóór 21:00 -> 0

Dat is even aannemende dat er geen shifts zijn die van de ene nachtdienst tot in de volgende nachtdienst lopen.

Die 5 gevallen kan je met IF constructies uitsplitsen en ieder apart berekenen. Of je kan kijken of je wat slims kan doen als:
MIN(8,(MIN(eind,05:00)-MAX(start,21:00)))
Dat werkt niet direct zo, zoals het daar staat, maar wel als je 21:00 en 05:00 vervangt door verwijzingen naar cellen met daarin respectievelijk de date-time stamp van die dag 21:00 en de volgende dag 05:00 en zowel start als eind ook als volledige date-time zijn opgeslagen (dus niet enkel een tijdstip).

Sowieso opletten met vergelijkingen: 23:00 is sec gezien groter dan 05:00, maar ligt dus op de klok wel tussen 21:00 en 05:00 in. Dat gaat allemaal goed zo lang je volledige date-time stamps gebruikt, maar niet als je enkel tijdstippen gebruikt.

[Djordjo]

Orion84 schreef op maandag 10 januari 2022 @ 14:02:
Dat is even aannemende dat er geen shifts zijn die van de ene nachtdienst tot in de volgende nachtdienst lopen.

Dat is inderdaad een belangrijke aanname, anders wordt het een stuk complexer.

[Voor 24% gewijzigd door Djordjo op 10-01-2022 14:16]

[dragonhaertt]

goldcard schreef op maandag 10 januari 2022 @ 14:02:
Oke, en als tijd1 = 22:00 en tijd2 = 23:00... dan is de gewenste uitkomst 1, maar jouw uitkomst 7, omdat Tijd2 (23:00) groter is dan 05:00 en de formule dus met 05:00 gaat rekenen.

Het zou voor TS makkelijker zijn wanneer het veld een dateTime veld is. Daarmee kan excel veel simpeler rekenen.

Als we ervan uitgaan dat een dienst nooit van de ene nachtdienst tot in de volgende nachtdienst lopen., dan kan je dat makkelijk afvangen door IF Tijd1 < 21:00 aan te passen naar IF 05:00 < Tijd1 < 21:00

Ja, dateTime zou makkelijker zijn maar IF statements werken prima.
Nieuwe versie:
=IF(B-A>0;B-A;B-A+1)

Dan B vervangen met: IF(21:00>Tijd2>05:00;05:00;Tijd2):
=IF(OR(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)

en A vervangen met: IF(05:00<Tijd1<21:00;21:00;Tijd1):
=IF(OR(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)

Totaalformule:
=IF(IF(OR(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-IF(OR(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)>0;IF(OR(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-IF(OR(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21);IF(OR(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-IF(OR(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)+1)


En in standaardopmaak:
=IF(IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21)>0;IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21);IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21)+1)
Met de tijden in cel D4 en E4

[Voor 10% gewijzigd door dragonhaertt op 10-01-2022 14:32]

[arnold24]

Orion84 schreef op maandag 10 januari 2022 @ 14:02:
Dus je wilt van het aantal gewerkte uren weten welke daarvan "gedurende de nacht (21:00-05:00)" waren.

Dan heb je dus een aantal gevallen:
start vóór 21:00, eind na 05:00 -> 8u
start vóór 21:00, eind vóór 05:00 -> 8 - (aantal uur tussen eind en 05:00)
start na 21:00, eind na 5:00 -> 8 - (aantal uur tussen 21:00 en start)
start na 21:00, eind vóór 5:00 -> aantal uur tussen start en eind
start na 05:00, eind vóór 21:00 -> 0

Deze aannames kloppen. Ik heb het geprobeerd met jouw formule echter nog geen succes.
Ik heb in cel A1 de begintijd ( 9-1-2022 17:00 ) en in cel B1 de eindtijd op ( 10-1-2022 - 06:00 )
Hoe gaat ik het dan noteren in Excel?

Dank voor alle reacties. Ik werk regelmatig in excel maar deze vindt ik toch wel lastig....

[Orion84]

arnold24 schreef op maandag 10 januari 2022 @ 14:30:
[...]


Deze aannames kloppen. Ik heb het geprobeerd met jouw formule echter nog geen succes.

Wat voor uitkomst / foutmelding krijg je. Oftewel: wat lukt er precies niet?

Ik heb in cel A1 de begintijd ( 9-1-2022 17:00 ) en in cel B1 de eindtijd op ( 10-1-2022 - 06:00 )
Hoe gaat ik het dan noteren in Excel?

Staan die waarden daarin als platte tekst, of als datumveld? En hoe ziet de formule er precies uit die je hebt geprobeerd? Want zoals ik al zei: dat was pseudocode. Je kan daar niet plat 21:00 en 05:00 invullen, daar zal je verwijzingen moeten hebben naar cellen waarin de grenzen van de nacht-uren zijn vastgelegd voor de betreffende dag. Dus een cel met 9-1-2022 21:00 en een cel met 10-1-2022 - 05:00 (beide wederom als datumveld, niet als tekst).

[dragonhaertt]

arnold24 schreef op maandag 10 januari 2022 @ 14:30:
[...]


Deze aannames kloppen. Ik heb het geprobeerd met jouw formule echter nog geen succes.
Ik heb in cel A1 de begintijd ( 9-1-2022 17:00 ) en in cel B1 de eindtijd op ( 10-1-2022 - 06:00 )
Hoe gaat ik het dan noteren in Excel?

Dank voor alle reacties. Ik werk regelmatig in excel maar deze vindt ik toch wel lastig....

Als het goed is kan je de formule uit mijn laatste post gewoon kopiëren en gebruiken.
Die werkt voor alle tijden tussen 5:01 starttijd op dag 1, tot 4:59 starttijd op dag 2
tot
21:01 stoptijd op dag 1, tot 20:59 stoptijd op dag 2.

(even verversen, ik heb een standaard A1B2 notatie toegevoegd)

[Voor 4% gewijzigd door dragonhaertt op 10-01-2022 14:37]

[arnold24]

dragonhaertt schreef op maandag 10 januari 2022 @ 14:30:
En in standaardopmaak:
=IF(IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21)>0;IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21);IF(OR(1/24*21<E4;E4<1/24*5);E4;1/24*5)-IF(OR(1/24*5>D4;D4>1/24*21);D4;1/24*21)+1)
Met de tijden in cel D4 en E4

Top. Maar ik krijg #NAAM? te zien als ik jouw formule gebruik.
In CEL D4 en E4 heb ik de tijden. Heb meerdere opmaakmanieren gebruikt.
Bij celeigenschap heb ik staan "14-03-12 13:30" zoals excel het vermeld.

[Voor 6% gewijzigd door arnold24 op 10-01-2022 14:40]

[dragonhaertt]

arnold24 schreef op maandag 10 januari 2022 @ 14:38:
[...]


Top. Maar ik krijg #NAAM? te zien als ik jouw formule gebruik.
In CEL D4 en E4 heb ik de tijden. Heb meerdere opmaakmanieren gebruikt.
Bij celeigenschap heb ik staan "14-03-12 13:30" zoals excel het vermeld.

Als je #NAAM krijgt heb je waarschijnlijk de nederlandse Excel en zal je de formule-namen moeten aanpassen.
IF -> ALS
OR -> OF

Mogelijk moet je ook komma's gebruiken in plaats van puntkomma's, ik weet niet zeker of dat nog zo is in de NL versie

=ALS(ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)>0;ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21);ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)+1)

=ALS(ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)>0;ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21);ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)+1)

[Voor 35% gewijzigd door dragonhaertt op 10-01-2022 15:40]

[dragonhaertt]

Nieuwe versie:
=IF(B-A>0;B-A;B-A+1)

Dan B vervangen met: IF(21:00>Tijd2>05:00; 05:00; Tijd2):
=IF(OR(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)

en A vervangen met: IF(05:00<Tijd1<21:00; 21:00; Tijd1):
=IF(OR(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)

Maar hopelijk kan je met de bovenstaande uitleg hem ook zelf schrijven.

[Voor 65% gewijzigd door dragonhaertt op 10-01-2022 14:48]

[arnold24]

dragonhaertt schreef op maandag 10 januari 2022 @ 14:42:
[...]

[Afbeelding]

Als je #NAAM krijgt heb je waarschijnlijk de nederlandse Excel en zal je de formule-namen moete naanpassen.
IF -> ALS
OR -> OF

Mogelijk moet je ook komma's gebruiken in plaats van puntkomma's, ik weet niet zeker of dat nog zo is in de NL versie

=ALS(ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)>0;ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21);ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)+1)

=ALS(ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)>0;ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21);ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)+1)

Ja! Dit werkt. Helemaal top.
20 jaar geleden heb ik nog wel eens geprogrammeerd echter je vergeet onbenullige zaken
als ALS/IF.
Superbedankt!

[Lustucru]

dragonhaertt schreef op maandag 10 januari 2022 @ 14:42:
[...]

=ALS(ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)>0;ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21);ALS(OF(1/24*21<RC[-2];RC[-2]<1/24*5);RC[-2];1/24*5)-ALS(OF(1/24*5>RC[-3];RC[-3]>1/24*21);RC[-3];1/24*21)+1)

=ALS(ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)>0;ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21);ALS(OF(1/24*21<E4;E4<1/24*5);E4;1/24*5)-ALS(OF(1/24*5>D4;D4>1/24*21);D4;1/24*21)+1)

oeff... wat een ding zeg

Orion84 schreef op maandag 10 januari 2022 @ 14:02:
Dus je wilt van het aantal gewerkte uren weten welke daarvan "gedurende de nacht (21:00-05:00)" waren.

Dan heb je dus een aantal gevallen:
start vóór 21:00, eind na 05:00 -> 8u
start vóór 21:00, eind vóór 05:00 -> 8 - (aantal uur tussen eind en 05:00)
start na 21:00, eind na 5:00 -> 8 - (aantal uur tussen 21:00 en start)
start na 21:00, eind vóór 5:00 -> aantal uur tussen start en eind
start na 05:00, eind vóór 21:00 -> 0

Dat is even aannemende dat er geen shifts zijn die van de ene nachtdienst tot in de volgende nachtdienst lopen.

Als dit idd alle mogelijkheden zijn, én zoals de @arnold24 schrijft dat de tijdvelden inclusief datum zijn dan kan het korter:

Neem de kleinste van 5 uur s'-ochtends de volgende dag en de eindtijd. Om de eindtijd zuiver te krijgen neem je de waarde en trek je daar het aantal dagen van de begindatum-tijd van af. Verminder dat met de grootste van begintijd en 21:00 's avonds. Als dat een negatief resultaat oplevert rond je het af op nul.

[Voor 3% gewijzigd door Lustucru op 10-01-2022 18:31]

[Orion84]

@Lustucru Dat is min of meer hetzelfde als wat ik bedoelde met mijn: MIN(8,(MIN(eind,05:00)-MAX(start,21:00)))

Alleen werkt dat wat ik zei, nu ik er nog eens naar kijk, alleen voor de gevallen dat er nachturen zijn, niet wanneer er 0 overlap is met die nachtperiode. Maar het was dan ook meer als voorzetje bedoeld dan complete oplossing.

[Voor 50% gewijzigd door Orion84 op 10-01-2022 18:41]

[Lustucru]

Orion84 schreef op maandag 10 januari 2022 @ 18:38:
@Lustucru Dat is min of meer hetzelfde als wat ik bedoelde met mijn: MIN(8,(MIN(eind,05:00)-MAX(start,21:00)))

Inderdaad: min of meer

[Lustucru]

Omdat de vraag vaker terugkomt en nooit helemaal compleet is beantwoord hier een voorzetje voor een makkelijk te reproduceren oplossing voor het bepalen van overlappen tussen een periode en nachtelijke uren. Onderstaande is gebaseerd op de volgende uitgangspunten:

- begin en eindtijden zijn genoteerd inclusief datum;
- er is per 24 uur één aaneengesloten tijdvak dat beoordeeld moet worden;
- starttijd is geheel vrij
- eindtijd mag doorlopen in de nacht van de volgende dag.

Voor de leesbaarheid is de formule opgedeeld in drie delen:

I: de begintijd ligt voor het einde van de nacht;

Visual Basic:

' bereken het verschil tussen de kleinste van eindtijd en einde van de nacht en de begintijd. 
' Omdat begin en eindtijd datumafhankelijk zijn wordt het tijdvak zelf naar de dag zelf toegerekend:
' als het tijdvak iedere dag om 5:00 u eindigt dan wordt dat dus geheel(starttijd)+5/24

=ALS(A1<GEHEEL(A1)+5/24;MIN(GEHEEL(A1)+5/24;B1)-A1;0)

II: de eindtijd ligt in of na de nacht

Visual Basic:

'Bereken het verschil tussen de kleinste van eindtijd en einde van de nacht en de grootste van starttijd en het begin van de nacht.
=ALS(B1>GEHEEL(A1)+21/24;MIN(GEHEEL(A1)+29/24;B1)-MAX(A1;GEHEEL(A1)+21/24);0)

III: de eindtijd ligt in of na de volgende nacht

Visual Basic:

'Bereken het verschil tussen de kleinste van einde van de nacht en de eindttijd en het begint van de nacht.
=ALS(B1>GEHEEL(A1)+45/24;MIN(GEHEEL(A1)+53/24;B1)-(GEHEEL(A1)+45/24);0)

IV: Bij nog meer dagen bereken je het dagverschil * nachturen. De totaalformule is niets meer dan een of meer van deze delen bij elkaar optellen.