[excel] Tijd laten berekenen a.d.h.v. 2 waardes uit 1 cel

[Wannial]

Ik ben bezig in excel om wat tijden te laten berekenen, maar ik kom er niet helemaal uit, en met google vind ik de oplossing niet (wellicht gebruik ik de verkeerde zoekterm).

Ik heb in 1 cel een tijdspanne staan, bijvoorbeeld 12:00-18:00 (dit betekent dat de activiteit van 12:00 tot 18:00 is)

Nu wil ik in de cel daaronder automatisch laten berekenen hoeveel uur dit is. Dus de uitkomst zoals in het voorbeeld:

12:00-18:00
6

Normaal als 12:00 en 18:00 in 2 losse cellen zou staan is dit natuurlijk geen probleem, dan is het een simpel sommetje. Maar omdat de 2 tijden in 1 cel staan is dit toch wat lastiger dan gedacht.

Weet iemand hoe ik dit oplos, of kan mij in de goede richting duwen?
Zoals aangegeven heb ik op google gezocht maar kan ik geen antwoord vinden, noch een richting waar ik moet zoeken.

[ Voor 21% gewijzigd door Wannial op 25-11-2015 10:29 ]

[migchiell]

Is het niet veel makkelijker om begintijd en starttijd als twee aparte waarden (en dus twee aparte cellen) te gebruiken?

Eerlijk gezegd zou ik denken dat wat jij wilt niet kan omdat Excel de inhoud van een cel ziet als één waarde, niet als meerdere losse waarden die je met elkaar kunt vergelijken.

[ Voor 42% gewijzigd door migchiell op 25-11-2015 10:27 ]

[Orion84]

Oplossing lijkt me dan simpel: splits de data, zodat start en eind in aparte cellen staat.

Alternatief is om een ingewikkelde formule te bouwen die de twee losse delen extraheert uit de complete string en ze daarna van elkaar aftrekt.

[ Voor 5% gewijzigd door Orion84 op 25-11-2015 10:25 ]

[Arjan90]

Oplossing van Orion84 lijkt me ook het beste, zeker als je deze vraag stelt. Ik zou dan niet te moeilijk doen met één formule maken.

Eventueel kun je eerst de twee waarden in twee aparte cellen laten zetten met een formule, vervolgens in een derde cel de formule maken en dan de formules in de aparte cellen in de derde cel zetten. Dan ben je er ook.

[migchiell]

Orion84 schreef op woensdag 25 november 2015 @ 10:25:
Oplossing lijkt me dan simpel: splits de data, zodat start en eind in aparte cellen staat.

Alternatief is om een ingewikkelde formule te bouwen die de twee losse delen extraheert uit de complete string en ze daarna van elkaar aftrekt.

Maar dan gebruik je die formule dus om er alsnog twee aparte waarden van te maken, toch?

[Wannial]

Er 2 aparte waardes van maken is helaas geen optie, het moet in 1 cel blijven.

Het gaat hier niet om 1 celletje waar iets berekend moet worden, maar om zo'n 15 000 per maand, om dan ipv 15k naar 30k cellen te moeten gaan is niet echt gewenst

[ Voor 52% gewijzigd door Wannial op 25-11-2015 10:33 ]

[Bolletje]

migchiell schreef op woensdag 25 november 2015 @ 10:27:
[...]


Maar dan gebruik je die formule dus om er alsnog twee aparte waarden van te maken, toch?

Nee, berekening kan je in één keer doen.

Wannial schreef op woensdag 25 november 2015 @ 10:29:
er 2 aparte waardes van maken is helaas geen optie, het moet in 1 cel blijven.

Er al aan gedacht om een ander werkblad te gebruiken voor je berekeningen?

[ Voor 34% gewijzigd door Bolletje op 25-11-2015 10:32 ]

[Maasluip]

Wannial schreef op woensdag 25 november 2015 @ 10:29:
er 2 aparte waardes van maken is helaas geen optie, het moet in 1 cel blijven.

Met een formule kan dat. Als ik een formule maak als '=RIGHT(A1;5)-LEFT(A1;5)' dan is de uitkomst 0,25 (dagen) doe dat keer 24 en je hebt 6 uur.

[Wannial]

Bolletje schreef op woensdag 25 november 2015 @ 10:29:
[...]

Er al aan gedacht om een ander werkblad te gebruiken voor je berekeningen?

Zeker aan gedacht, maar met zo'n 15 000 berekeningen leek me dit niet heel practisch.
Ik ben ook niet de enige die er in moet werken dus het moet wel simpel en overzichtelijk blijven, en de kans dat andere mensen iets slopen moet natuurlijk ook beperkt blijven (nu kan je dat werkblad wel verbergen maar dit lijkt me geen optimale manier)

[Rrob]

wat maasluip zegt
of met find() zie: http://www.wallstreetoasi...ing-part-of-cell-in-excel

edit> wat juggels zegt, dat werkt ook met tijden met 3 cijfers. alleen *1440/60 is een beetje een ingewikkelde manier om *24 te noteren

[ Voor 43% gewijzigd door Rrob op 25-11-2015 11:00 ]

[Juggels]

Er van uitgaande dat je waarde in cel A1 staat kun je inderdaad e.e.a. met find() doen:
=((MID(A1;FIND("-";A1)+1;LEN(A1))-LEFT(A1;FIND("-";A1)-1))*1440)/60

En anders wat Maasluip voorstelt

[Fr33z]

ik zou nog eens sterk overwegen om het gewoon te splitsen. Een formule zoals hierboven is bijna niet te debuggen voor anderen. Je kunt misschien ook twee kolommen toevoegen met daarin de start en eindtijd en die voor je berekening gebruiken, en die dan verbergen?

startkolom:
=LINKS(A1;VINDEN.SPEC(":";A1)-1)

eindkolom:
=RECHTS(A1;LENGTE(A1)-VINDEN.SPEC(":";A1))

dan krijg je twee kolommen met bijv. 12:00 en 14:00. Als je deze verbergt dan kan de orignele input zo blijven

[Arjan90]

Volgens mij maak je het zelf echt veel moeilijker dan het is. 15.000 berekeningen is niets voor Excel, dat stelt echt weinig voor. Al zijn het er 30.000 om de begin- en eindtijd te bepalen en vervolgens nog 15.000 om de tijd te berekenen, dat moet geen probleem zijn. Bovendien maakt het het debuggen veel makkelijker als de data aanlevering verandert.

Je document is me trouwens nog erg onduidelijk, hoe is de opbouw? Je geeft aan dat het om 15.000 waarden gaat, ik neem aan dat die niet in één rij staan maar juist in één kolom (dus van rij 1 tot en met 15.000). Zeker dan kun je het beste een inleesbestandje maken wat geen onderhoud vergt. Je maakt bijvoorbeeld in de kolommen A tot en met C je berekeningen (A en B rekenen apart de begin en eindtijd uit, C berekent vervolgens het aantal uur). Vervolgens plak je wekelijks/dagelijks/maandelijks in kolom D je waarden (desnoods met transponeren, als je een document krijgt waarin het wordt uigespuugd verdeeld over een hele rij), trek je de formules in A tot en met C door tot het einde en je bent er. Dan kan iedereen het zonder problemen gebruiken.


@hierboven: waarom zoek je op ":"? Dan houd je geen rekening met de minuten... En je eindkolom klopt nu ook niet, maar volgens mij moet jouw dubbele punt een streepje zijn.

[ Voor 3% gewijzigd door Arjan90 op 25-11-2015 11:15 ]

[Orion84]

Wannial schreef op woensdag 25 november 2015 @ 10:29:
Er 2 aparte waardes van maken is helaas geen optie, het moet in 1 cel blijven.

Het gaat hier niet om 1 celletje waar iets berekend moet worden, maar om zo'n 15 000 per maand, om dan ipv 15k naar 30k cellen te moeten gaan is niet echt gewenst

Je data structureren zodat je eenvoudig de benodigde berekeningen kan doen lijkt me een stuk wenselijker dan allerlei ingewikkelde formules implementeren (die veel kans stuk gaan bij de eerste de beste subtiele aanpassing aan de sheet).

Zorgen dat je data bruikbaar is is veel belangrijker voor je efficiëntie en de performance van een sheet, dan het aantal cellen beperken door data te combineren in 1 cel en daar vervolgens allerlei moeilijke formules op los te moeten laten.

[Wannial]

Bedankt voor alle tips, ik heb eerst deze optie geprobeerd, die werkt prima voor uren op dezelfde dag.

Maasluip schreef op woensdag 25 november 2015 @ 10:34:
[...]

Met een formule kan dat. Als ik een formule maak als '=RIGHT(A1;5)-LEFT(A1;5)' dan is de uitkomst 0,25 (dagen) doe dat keer 24 en je hebt 6 uur.

Echter als je van 18:00 tot bijvoorbeeld 02:00 de dag erna inplant, dan gaat hij over de emmer..



Ik zal eens eea gaan proberen met de forume van Juggles en de anderen

Dank aan iedereen!

[Hooglander1]

Wannial schreef op woensdag 25 november 2015 @ 11:21:
Bedankt voor alle tips, ik heb eerst deze optie geprobeerd, die werkt prima voor uren op dezelfde dag.

[...]


Echter als je van 18:00 tot bijvoorbeeld 02:00 de dag erna inplant, dan gaat hij over de emmer..

[afbeelding]

Ik zal eens eea gaan proberen met de forume van Juggles en de anderen

Dank aan iedereen!

Met een If-statement het tweede tijdstip afvangen. Als dat kleiner is dan het eerste deel, tel er dan 24 uur bij op.

Dan heb je altijd een correcte berekening

[Juggels]

Fr33z schreef op woensdag 25 november 2015 @ 10:53:
ik zou nog eens sterk overwegen om het gewoon te splitsen. Een formule zoals hierboven is bijna niet te debuggen voor anderen.

Het is inderdaad niet de mooiste formule (en houdt ook zeker geen rekening met dag overstijging), maar voldoet wel exact aan de oorspronkelijke requirements

Niettemin ben ik het met de mensen eens die voorstellen om e.e.a. toch te splitsen naar aparte kolommen (die je eventueel zou kunnen verbergen). Zeker nu ook duidelijk wordt dat de tijden over meerdere dagen verspreid kunnen worden maak je het onnodig gecompliceerd wanneer je het met een enkele formule probeert op te lossen.

[Arjan90]

Je huidige probleem is gemakkelijk af te vangen als je met aparte cellen werkt met daarin de data gesplitst...


Interessant trouwens, wat gebeurt er als het systeem opeens 18:00-2:00 uitspuugt ipv 18:00-02:00? Je huidige formule is niet optimaal wat dat betreft en erg foutgevoelig.

[ Voor 47% gewijzigd door Arjan90 op 25-11-2015 11:27 ]

[Wannial]

Arjan90 schreef op woensdag 25 november 2015 @ 11:26:
Je huidige probleem is gemakkelijk af te vangen als je met aparte cellen werkt met daarin de data gesplitst...


Interessant trouwens, wat gebeurt er als het systeem opeens 18:00-2:00 uitspuugt ipv 18:00-02:00? Je huidige formule is niet optimaal wat dat betreft en erg foutgevoelig.

de uren worden handmatig in de bovenste kolom ingevoegd

En als aparte cellen een optie was geweest dan had ik dat echt wel gedaan! Helaas is dat nu (nog) geen optie.


Grappig overigens, ik zie nu net in mijn screenshot van hierboven dat de SOM functie niet meer werkt om alle uren van die week bij elkaar op te tellen

[ Voor 22% gewijzigd door Wannial op 25-11-2015 11:35 ]

[hamsterrip]

Probeer anders:

=ALS(RECHTS(B1;5)<=LINKS(B1;5);RECHTS(B1;5)+1-LINKS(B1;5);RECHTS(B1;5)-LINKS(B1;5))

Dan vang je het verspringen op.

[Wannial]

hamsterrip schreef op woensdag 25 november 2015 @ 11:34:
Probeer anders:

=ALS(RECHTS(B1;5)<=LINKS(B1;5);RECHTS(B1;5)+1-LINKS(B1;5);RECHTS(B1;5)-LINKS(B1;5))

Dan vang je het verspringen op.

Dit werkt inderdaad perfect!

Echter werkt de SOM functie nier meer als ik alle uren bij elkaar laat optellen van die week

[hamsterrip]

Doe die dan ook eens maal 24

[Paul]

Wannial schreef op woensdag 25 november 2015 @ 11:31:
de uren worden handmatig in de bovenste kolom ingevoegd

Bij 15.000 keer per maand iets handmatig invullen is de kans dat het fout wordt ingevuld (bijvoorbeeld: iemand zet spaties om de - ) natuurlijk erg groot. Doe je daar ook iets mee of is het aan de mensen zelf om dat op te vangen?

Wat doet som niet dan? Ik test het hier en =som(b2:d2) (ik heb in rij 1 drie kolommen met 12:00-18:00) levert netjes 0,75 op?

[Arjan90]

Wannial schreef op woensdag 25 november 2015 @ 11:38:
[...]


Dit werkt inderdaad perfect!

Echter werkt de SOM functie nier meer als ik alle uren bij elkaar laat optellen van die week

De som werkt perfect. Je berekent nu namelijk het verschil tussen 12:00 en 18:00, die worden in Excel uitgedrukt als decimalen met een "tijdsweergave" (zoals 0,01 met een percentageweergave wordt weergegeven als 1%). 12:00 is 0,5 (een halve dag) en 18:00 is 0,75 (driekwart dag). Je berekent dus het verschil tussen 0,75 - 0,50, de uitkomst is 0,25. Vandaar je uitkomst in de totale som à 1,25.

Je moet er alleen voor zorgen dat je óf de som vermenigvuldigt 24 of de tussentijdse berekeningen al vermenigvuldigt met 24.

[Wannial]

Ik krijg dit dus in de uiterst rechtse kolom (dit is het totaal van maandag t/m zondag)

Dit is met de formule per cel:
=ALS(RECHTS(B3;5)<=LINKS(B3;5);RECHTS(B3;5)+1-LINKS(B3;5);RECHTS(B3;5)-LINKS(B3;5))

Arjan90 schreef op woensdag 25 november 2015 @ 11:44:
[...]

De som werkt perfect. Je berekent nu namelijk het verschil tussen 12:00 en 18:00, die worden in Excel uitgedrukt als decimalen met een "tijdsweergave" (zoals 0,01 met een percentageweergave wordt weergegeven als 1%). 12:00 is 0,5 (een halve dag) en 18:00 is 0,75 (driekwart dag). Je berekent dus het verschil tussen 0,75 - 0,50, de uitkomst is 0,25. Vandaar je uitkomst in de totale som à 1,25.

Je moet er alleen voor zorgen dat je óf de som vermenigvuldigt 24 of de tussentijdse berekeningen al vermenigvuldigt met 24.

Ik krijg nu dus een andere uitkomst met de formule.. Als ik nu die 06:00:00 ga vermenigvuldigen met 24 dan klopt er natuurlijk niets meer van.

En gek genoeg krijg ik in de rij eronder ineens 16:00:00 als antwoord terug...?


edit: net de notatie als standaard gezet dan krijg ik inderdaad een decimaal getal terug, en als ik dan de SOM *24 laat doen is het antwoord correct.
Maar als ik dan heel die rij weer op tijdnotatie zet dan zegt SOM ineens weer 00:00:00 als antwoord?

[ Voor 9% gewijzigd door Wannial op 25-11-2015 11:52 ]

[Paul]

Dat antwoord klopt, je geeft echter alleen het tijds-deel van je veld weer Eigenlijk staat daar overal 0 januari 1900 6 uur 'sochtends, en in het laatste veld staat 1 januari 1900 6 uur 's ochtends

Het is een tijd-veld, niet een 'aantal uren'-veld. Excel zal daar nooit 30:00:00 neerzetten, je zult dus (helaas, want 6:30 wordt dan 6.5) een getal-veld moeten gebruiken.

[Orion84]

Dat komt omdat de "tijd" opmaak in Excel een stand op de klok aanduidt, niet een aantal uren.

1,25 = 1 dag + 6 uur. Als je dat dus formatteert als "Tijd", dan laat hij alleen de uren zien (dus 6 uur).

Die tweede reeks komt op 5x0,3333... = 1,6666... = 16:00 uur.

Je moet dus gewoon vermenigvuldigen met 24 en niet die tijdsopmaak toepassen.

[Wannial]

Ik heb de weergave nu op Standaard gezet overal in plaats van Tijd, en de formule nu zo gezet

=ALS(RECHTS(B6;5)<=LINKS(B6;5);RECHTS(B6;5)+1-LINKS(B6;5);RECHTS(B6;5)-LINKS(B6;5))*24

Dan geeft hij 8 als antwoord overal onder, en dan werkt SOM prima en staat het netjes op 40 uur.


Ik denk dat ik hem dan nu zo helemaal heb.... Iedereen ontzettend bedankt voor de hulp!


edit: ah toch nog niet helemaal... deze formule staat natuurlijk overal, en als ik op een dag geen urne invul (er zijn in 1 rij nooit alle 7 dagen ingevuld), dan gaat hij natuurlijk toch een waarde proberen te berekenen en dan krijg ik #WAARDE terug in het vakje waar niets instaat, met als gevolg dat ook de SOM weer niet werkt.



Als iemand hier nog een oplossing voor weet dan ben ik je eeuwig dankbaar!

[ Voor 38% gewijzigd door Wannial op 25-11-2015 12:02 ]

[Arjan90]

Ik ga je het antwoord niet voorkauwen in een formule Maar: als je nou eens eerst bij de formule controleert of er überhaupt een waarde is ingevuld in (in dit geval) B6? Dus, als B6 niet leeg is, dan pas wil je die formule gaan uitvoeren.

[Wannial]

Arjan90 schreef op woensdag 25 november 2015 @ 12:04:
Ik ga je het antwoord niet voorkauwen in een formule Maar: als je nou eens eerst bij de formule controleert of er überhaupt een waarde is ingevuld in (in dit geval) B6? Dus, als B6 niet leeg is, dan pas wil je die formule gaan uitvoeren.

Daar ben ik inderdaad mee aan het proberen nu om nog een ALS toe te voegen

[Lustucru]

Orion84 schreef op woensdag 25 november 2015 @ 11:55:
Dat komt omdat de "tijd" opmaak in Excel een stand op de klok aanduidt, niet een aantal uren.
1,25 = 1 dag + 6 uur. Als je dat dus formatteert als "Tijd", dan laat hij alleen de uren zien (dus 6 uur).
Die tweede reeks komt op 5x0,3333... = 1,6666... = 16:00 uur.

Paul schreef op woensdag 25 november 2015 @ 11:55:
Het is een tijd-veld, niet een 'aantal uren'-veld. Excel zal daar nooit 30:00:00 neerzetten

Uh, dat geldt voor de standaardtijdinstellingen. Excel kent ook het format [uu]:mm:ss en dat geeft gewoon het aantal uren aan. 1,26 zal dan weergegeven worden als 30:14:24 (dertig uur,14 minuten,24 seconden)

, je zult dus (helaas, want 6:30 wordt dan 6.5) een getal-veld moeten gebruiken.
[...]
Je moet dus gewoon vermenigvuldigen met 24 en niet die tijdsopmaak toepassen.

Als de weergave in hh:mm moet dan zou ik toch de aangepaste tijdsopmaak toepassen en vooral in datum/tijd blijven rekenen.

[ Voor 17% gewijzigd door Lustucru op 25-11-2015 12:21 ]

[Wannial]

Lustucru schreef op woensdag 25 november 2015 @ 12:16:
Als de weergave in hh:mm moet dan zou ik toch de aangepaste tijdsopmaak toepassen en vooral in datum/tijd blijven rekenen.

De tijdsweergave is geen probleem meer, ik heb nu de cel op Standaard opmaak staan wat perfect is.
Het enige probleem waar ik nu nog tegenaanloop is dat als een cel leeg is hij wel die formule wilt gaan uitvoeren en dan dus met #WAARDE terugkomt waardoor de SOM op het einde om de uren vande week te berekenen niet meer werkt.

Als ik deze formule invul dan krijg ik #ONWAAR terug, als F3 leeg is dan zegt hij #WAARDE
=ALS(F3="";ALS(RECHTS(F3;5)<=LINKS(F3;5);RECHTS(F3;5)+1-LINKS(F3;5);RECHTS(F3;5)-LINKS(F3;5))*24)


volgens mij doe ik iets fout met haakjes, maar ik zie hem zo snel even niet... Of mag die 2e ALS daar niet?

[ Voor 39% gewijzigd door Wannial op 25-11-2015 12:39 ]

[Lustucru]

Wannial schreef op woensdag 25 november 2015 @ 12:25:
[...]
De tijdsweergave is geen probleem meer, ik heb nu de cel op Standaard opmaak staan wat perfect is.
Het enige probleem waar ik nu nog tegenaanloop is dat als een cel leeg is hij wel die formule wilt gaan uitvoeren en dan dus met #WAARDE terugkomt waardoor de SOM op het einde om de uren vande week te berekenen niet meer werkt.

Als ik deze formule invul dan krijg ik #ONWAAR terug
=ALS(F3="";ALS(RECHTS(F3;5)<=LINKS(F3;5);RECHTS(F3;5)+1-LINKS(F3;5);RECHTS(F3;5)-LINKS(F3;5))*24)

We hebben verschillende meningen over perfect. Ik vind je formule tamelijk onleesbaar geworden en als mensen geen vol uur hebben gewerkt vind ik 3:40 minder verwarrend van 3,67.

Ook is als() de verkeerde keuze om een fout af te vangen. Daar is de functie als.fout() voor.

Tot slot zou ik eerder kiezen om het verschil modulair te berekenen. Je wilt twee tijden van elkaar aftrekken en het resultaat moet tussen 0 en 1 liggen:

code:

=als.fout(rest(rechts(A1;5)-links(A1;5);1);0)

meer heb je niet nodig.

[Wannial]

Lustucru schreef op woensdag 25 november 2015 @ 12:35:
[...]

We hebben verschillende meningen over perfect. Ik vind je formule tamelijk onleesbaar geworden en als mensen geen vol uur hebben gewerkt vindt ik 3:40 minder verwarrend van 3,67.

Ook is als de verkeerde keuze om een fout af te vangen. Daar is de functie als.fout() voor.

Tot slot zou ik eerder kiezen om het verschil modulair te berekenen. Je wilt twee tijden van elkaar aftrekken en het resultaat moet tussen 0 en 1 liggen:

code:

=als.fout(rest(rechts(A1;5)-links(A1;5);1));0)

meer heb je niet nodig.

Dankjewel, ik zal hier zeker ook eens naar kijken.

Hier zijn we echter gewend om 6:30 te lezen als 6.5, dit is voor ons dus prima

[NovapaX]

Twee vragen:
Kun je in excel je formules niet verdelen over regels en voorzien van inspringingen?
Zit er geen tijdsduur celnotatie in excel?

[Wannial]

Hebbes!

=ALS(EN(F3="";F3="");"";ALS(RECHTS(F3;5)<=LINKS(F3;5);RECHTS(F3;5)+1-LINKS(F3;5);RECHTS(F3;5)-LINKS(F3;5))*24

[Orion84]

Wat is het nut van twee keer dezelfde check in een EN()

NovapaX schreef op woensdag 25 november 2015 @ 13:22:
Twee vragen:
Kun je in excel je formules niet verdelen over regels en voorzien van inspringingen?

Ja, dat kan, met alt+Enter kan je regeleindes invoegen en spaties toevoegen kan ook.

Zit er geen tijdsduur celnotatie in excel?

Zie Lustucru in "[excel] Tijd laten berekenen a.d.h.v. 2 waardes uit 1 cel"

[ Voor 9% gewijzigd door Orion84 op 25-11-2015 14:48 ]

[Wannial]

Ik probeer de formule ondertussen wat uit te breiden, als het rooster langer dan 5u ingepland staat dat er automatisch een half uur (pauze) afgetrokken word. Alleen kom ik er niet uit waarom mijn formule niet werkt, ik heb het laatste stukje toegevoegd.

H312 is de cel waar de tijd in staat, zeg 12:00-20:00
H314 is de cel waar dan de waarde naartoe word geschreven, als deze waarde dan hoger is dan 5 moet er automatisch 30 minuten af (0.5)

=ALS(EN(H312="";H312="");"";ALS(RECHTS(H312;5)<=LINKS(H312;5);RECHTS(H312;5)+1-LINKS(H312;5);RECHTS(H312;5)-LINKS(H312;5))*24)ALS(H314>5;-0,5)

[Orion84]

Je kan die extra ALS er niet domweg achter plakken, die moet er omheen. De originele formule bepaalt de waarde van H314, dus je krijgt dan:

ALS((origineleformule)>5;(originele formule)-0,5;(originele formule))

[Paul]

Sowieso werkt er zomaar iets achter gaan typen niet Copy/paste-foutje?

Volgens mij kom je dan in het gebied waar het makkelijker (gegrijpbaarder) is om een macro te gebruiken, of een regel toe te voegen. Die regel hoeft niet zichtbaar te zijn of zelfs maar in de buurt van je bron- of doelregel, maar in één formule kun je (bij mijn weten, maar ik heb vandaag van Lustucru al iets nieuws geleerd m.b.t. het weergeven van uren ) geen variabelen gebruiken, en dus zit je eindeloos formules te herhalen en aan elkaar te plakken.

Je zou de huidige regel met daarin het aantal uur kunnen verbergen en er een nieuwe regel onder zetten met als formule =ALS(H314 >= 5; H314 - 0,5; H314)

[Wannial]

Heh verdorie, ik dacht al zoiets maar het viel natuurlijk te proberen...


Als ik hier mee aan de gang ga

Lustucru schreef op woensdag 25 november 2015 @ 12:35:
[...]

We hebben verschillende meningen over perfect. Ik vind je formule tamelijk onleesbaar geworden en als mensen geen vol uur hebben gewerkt vind ik 3:40 minder verwarrend van 3,67.

Ook is als() de verkeerde keuze om een fout af te vangen. Daar is de functie als.fout() voor.

Tot slot zou ik eerder kiezen om het verschil modulair te berekenen. Je wilt twee tijden van elkaar aftrekken en het resultaat moet tussen 0 en 1 liggen:

code:

=als.fout(rest(rechts(A1;5)-links(A1;5);1));0)

meer heb je niet nodig.

Dan krijg ik de melding : U hebt voor deze functie te weinig argumenten ingevuld.

uhm?

[ Voor 84% gewijzigd door Wannial op 25-11-2015 15:54 ]

[Lustucru]

Er staat een haakje teveel bij ;1))

offtopic:
@Paul: Je kunt zoiets dergelijks in één cel bereiken door iteratieve berekeningen (met aantal iteraties=2) aan te zetten. Maar aangezien dat een globale instelling is zou ik dat niet snel iemand aanraden.

[ Voor 9% gewijzigd door Lustucru op 25-11-2015 16:15 ]

[Wannial]

Lustucru schreef op woensdag 25 november 2015 @ 16:12:
Er staat een haakje teveel bij ;1))

offtopic:
@Paul: Je kunt zoiets dergelijks in één cel bereiken door iteratieve berekeningen (met aantal iteraties=2) aan te zetten. Maar aangezien dat een globale instelling is zou ik dat niet snel iemand aanraden.

Ahja zo doet hij het wel, maar ik krijg 0 terug (wat 11 moet zijn).

[Meneer iCy]

Is je gegevensbron toevallig een CSV/XML bestand? Dan zou je wellicht eerst iets kunnen powershellen om je beginwaarde te laten splitsen voordat het in een XLS/XLSX wordt gegoten.

m.a.w. wat is je bron

[Paul]

iCy schreef op woensdag 25 november 2015 @ 16:56:
m.a.w. wat is je bron

Medewerkers die een dagbesteding of urendeclaratie of iets dergelijks met de hand opstellen

Wannial schreef op woensdag 25 november 2015 @ 11:31:
de uren worden handmatig in de bovenste kolom ingevoegd

[RoadRunner84]

Ik weet niet precies wat Excell wel en niet kan, maar kan je geen transformatie naar ISO interval maken, en daar fijn handelingen op doen?
=VERVANG("-", "/", A2+"T"+A3)
Want 2014-01-04T12:00/18:00 is een legaal ISO tijdsinterval.
en dan blij met dat interval rekenen, verder geen sommen of wat dan ook.

[Icephase]

RoadRunner84 schreef op woensdag 25 november 2015 @ 17:04:
Ik weet niet precies wat Excell wel en niet kan, maar kan je geen transformatie naar ISO interval maken, en daar fijn handelingen op doen?
=VERVANG("-", "/", A2+"T"+A3)
Want 2014-01-04T12:00/18:00 is een legaal ISO tijdsinterval.
en dan blij met dat interval rekenen, verder geen sommen of wat dan ook.

Dat lijkt me nog véél ingewikkelder! Nee Excel kan prima doen wat TS wil, en alle tips zijn al gegeven maar TS heeft blijkbaar moeite om het in een werkbaar geheel te gieten. Mijn advies zou zijn: ga op Excel-cursus (ik vind dit topic nou niet echt geschikt om het werk voor TS voor te kauwen) of huur iemand in die dit voor jullie kan maken.

[Lustucru]

Wannial schreef op woensdag 25 november 2015 @ 16:50:
[...]


Ahja zo doet hij het wel, maar ik krijg 0 terug (wat 11 moet zijn).

Basic excel functie debuggen: zorg dat je cursor in de functie staat en klik op het fx icoontje. Excel zal dan laten zien hoe de functie berekend wordt.
ik gok erop dat je invoercel niet het formaat 00:00-00:00 heeft

[Wannial]

Ik heb de formule nu vervangen voor

=ALS.FOUT(REST(RECHTS(E3;5)-LINKS(E3;5);1);0) en alle vakken op tijd gezet zodat we mooi 08:00 en 08:30 krijgen.


Zit nu alleen nog even te kijken hoe ik netjes pauzes kan laten aftrekken, moet dan -0,01 doen om een kwartier af te trekken, dus nog even zien hoe ik dit overzichtelijker kan maken, want een half uur is dan weer 0,021. Wel fijn dat als dalijk alles bij elkaar opgeteld word ik niet ineens met afrondingen zit en met 10.000 uur ineens een uur mis omdat er naar beneden afgerond word

[ Voor 110% gewijzigd door Wannial op 26-11-2015 11:10 ]

[Reptile209]

Ga alsjeblieft niet dat soort constanten gebruiken. Doe dan gewoon (15/60/24), of desnoods (0,25/24) voor een kwartier in je formule. Dan zorgt Excel dat je geen afronding hebt, en snap je over twee weken ook nog steeds dat het over een kwartier ging.

Maar zoals al eerder opgemerkt: je probeert nu wel erg veel in één formule te vangen. Maak daar een macro van. Dan kan je de berekening in overzichtelijke stappen opdelen en nog comments toevoegen ook. Dan snapt zelfs iemand anders over 2 jaar nog wat je doet... ook input-validatie kan dan opeens veel makkelijker.

[ajakkes]

Ik zou de formule ook zo opgebouwd hebben. (Wanneer ik geen controle heb over de bron template). Maar aangezien ik begrijp dat de uren handmatig ingevoerd worden is de keuze om twee losse datum cellen te gebruiken en deze tijd opmaak te geven wel een betere.

Maar zolang TS dat niet wil zijn de huidige stappen de logische keuze.

Wat vaak helpt is eerst per berekening een aparte cel en deze later in elkaar plakken.

Dus B2: =links(B1;vind.spec("-";B1))
B4: =als(B3-B2<0;B3+24-B2;B3-B2)

En dan in B4 alle B2 vervangen door de tekst in B2 zonder het = teken.

Dan zie je veel beter wat je fout doet.

Voor zo iets simpels zou ik geen macro schrijven.