Excel tijd berekenen tussen twee tijden

Pagina: 1
Acties:

Vraag


Acties:
  • 0 Henk 'm!

  • arnold24
  • Registratie: Augustus 2000
  • Laatst online: 09-09-2024
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

Beste antwoord (via arnold24 op 10-01-2022 15:04)


  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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.
Afbeeldingslocatie: https://tweakers.net/i/0WerqPQR78MPtJaCVciW9Dojoiw=/full-fit-in/4000x4000/filters:no_upscale():fill(white):strip_exif()/f/image/5MX9H9GZjYnES8JneP27W7YC.png?f=user_large

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 ]

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.

Alle reacties


Acties:
  • 0 Henk 'm!

  • Lelletje
  • Registratie: Juli 2007
  • Laatst online: 09-05 15:09

Acties:
  • 0 Henk 'm!

  • de Peer
  • Registratie: Juli 2002
  • Nu online

de Peer

under peer review

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

20600 Wp, Atlantic Explorer V3, 3x Daikin airco, Opel Ampera-e, VW ID3, Gasloos sinds 2018


Acties:
  • 0 Henk 'm!

  • arnold24
  • Registratie: Augustus 2000
  • Laatst online: 09-09-2024
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?

Acties:
  • 0 Henk 'm!

  • Chris03
  • Registratie: December 2005
  • Laatst online: 23-04 15:16
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.

Acties:
  • 0 Henk 'm!

  • erwn
  • Registratie: November 2020
  • Niet online
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

Acties:
  • 0 Henk 'm!

  • F_J_K
  • Registratie: Juni 2001
  • Niet online

F_J_K

Moderator CSA/PB

Front verplichte underscores

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 :)

'Multiple exclamation marks,' he went on, shaking his head, 'are a sure sign of a diseased mind' (Terry Pratchett, Eric)


Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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 ]

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • goldcard
  • Registratie: Oktober 2003
  • Laatst online: 25-04 12:56
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??

Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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)

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • goldcard
  • Registratie: Oktober 2003
  • Laatst online: 25-04 12:56
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.

Acties:
  • 0 Henk 'm!

  • Orion84
  • Registratie: April 2002
  • Laatst online: 09-05 22:00

Orion84

Admin General Chat / Wonen & Mobiliteit

Fotogenie(k)?

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.

The problem with common sense is that it's not all that common. | LinkedIn | Flickr


Acties:
  • 0 Henk 'm!

  • Djordjo
  • Registratie: Mei 2007
  • Niet online
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 ]


Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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 ]

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • arnold24
  • Registratie: Augustus 2000
  • Laatst online: 09-09-2024
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....

Acties:
  • 0 Henk 'm!

  • Orion84
  • Registratie: April 2002
  • Laatst online: 09-05 22:00

Orion84

Admin General Chat / Wonen & Mobiliteit

Fotogenie(k)?

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).

The problem with common sense is that it's not all that common. | LinkedIn | Flickr


Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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 ]

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • arnold24
  • Registratie: Augustus 2000
  • Laatst online: 09-09-2024
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 ]


Acties:
  • Beste antwoord
  • +1 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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.
Afbeeldingslocatie: https://tweakers.net/i/0WerqPQR78MPtJaCVciW9Dojoiw=/full-fit-in/4000x4000/filters:no_upscale():fill(white):strip_exif()/f/image/5MX9H9GZjYnES8JneP27W7YC.png?f=user_large

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 ]

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • 0 Henk 'm!

  • dragonhaertt
  • Registratie: Februari 2011
  • Laatst online: 11:36

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. (y)

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

Truth is like a language with no native speakers left.
Its poetry is speechless and it can’t be caught in human being’s breath.


Acties:
  • +1 Henk 'm!

  • arnold24
  • Registratie: Augustus 2000
  • Laatst online: 09-09-2024
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)
toon volledige bericht
Ja! Dit werkt. Helemaal top.
20 jaar geleden heb ik nog wel eens geprogrammeerd echter je vergeet onbenullige zaken
als ALS/IF.
Superbedankt!

Acties:
  • +2 Henk 'm!

  • Lustucru
  • Registratie: Januari 2004
  • Niet online

Lustucru

26 03 2016

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 :X
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.

Afbeeldingslocatie: https://tweakers.net/i/Bs0zO0sQm-ACD0XZz_rfYDi4XvQ=/full-fit-in/4000x4000/filters:no_upscale():fill(white):strip_exif()/f/image/uWrN7jWYHKRdCiMayUvzHDmH.png?f=user_large

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

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland


Acties:
  • 0 Henk 'm!

  • Orion84
  • Registratie: April 2002
  • Laatst online: 09-05 22:00

Orion84

Admin General Chat / Wonen & Mobiliteit

Fotogenie(k)?

@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 ]

The problem with common sense is that it's not all that common. | LinkedIn | Flickr


Acties:
  • 0 Henk 'm!

  • Lustucru
  • Registratie: Januari 2004
  • Niet online

Lustucru

26 03 2016

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 :P

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland


Acties:
  • +1 Henk 'm!

  • Lustucru
  • Registratie: Januari 2004
  • Niet online

Lustucru

26 03 2016

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:
1
2
3
4
5
' 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:
1
2
'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:
1
2
'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.

Afbeeldingslocatie: https://tweakers.net/i/yxrGIGoIubwkwGBRPuuBJfm9KWA=/full-fit-in/4000x4000/filters:no_upscale():fill(white):strip_exif()/f/image/x95ryNC7ks4dK3TXc5OTqD4E.png?f=user_large

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland

Pagina: 1