Verticaal zoeken met tijden en uren

[lauennien]

Beste allemaal,

In bijgevoegde sheet wordt een getal berekend door een specifieke datum te zoeken, vervolgens naar het uur verder te kijken en een half uur daarna. Dit werkt prima. Echter snap excel niet wanneer je een volgende dag in gaat. Om 23:00 's avonds kijkt hij dus naar de datum van vandaag en vervolgens naar de ochtend i.p.v. de volgende dag. Kan iemand mij hier helpen?

Verder wil ik graag een 2e berekening waarin hij de komende 24 uur uitrekent (die 48 cellen optelt afhankelijk van de datum en tijd).

Een voorbeeldbestand is bijgevoegd.

Bedankt allemaal!!

 

[snb]

Probeer eerns:

=SUMPRODUCT((Sheet3!$A$1:$A$39807=TEXT(TODAY();"jjjjmmdd"))*((Sheet3!$B$1:$B$39807=(HOUR(NOW())+1)*100)+(Sheet3!$B$1:$B$39807=(HOUR(NOW())+1)*100+30))*(Sheet3!$C$1:$C$39807))

 

[lauennien]

Bedankt! Best wel logisch eigenlijk ;-). Ik ga er vanavond is naar kijken. Ik zit op een citrix server waar ik geen datum/tijd kan veranderen. Uit proberen kan dus even niet. Op mijn laptop zit geen office omdat ik alles online doe Als ik de formule bekijk moet het zo kloppen. Bedankt SNB!!

Iemand nog een suggestie om die 48 getallen makkelijker op te tellen? Anders maak ik er gewoon een hele lange formule van ;-)

 

[VenA]

Gebruik een extra kolom om van de datum en tijd een echte datum te maken. Waarschijnlijk heb je de gegevens niet goed geïmporteerd dus wordt de formule wat langer.

=DATUM(LINKS(A2;4);DEEL(A2;5;2);RECHTS(A2;2))+TIJD(LINKS(HERHALING(0;4-LENGTE(B2))&B2;2);RECHTS(HERHALING(0;4-LENGTE(B2))&B2;2);0)

=SOMPRODUCT(($D$2:$D$193>NU())*($D$2:$D$193<NU()+1)*($C$2:$C$193))

om de 24 uur aantallen te bereken

Ik denk dat de andere dan ook wel een stukje korter kan worden.

 

[HSV]

Je eerste kan zeker een stuk korter.

=--TEKST(A1;"0000\/00\/00")

Voor de volledigheid:

=--TEKST(A1;"0000\/00\/00")+--TEKST(B1;"00\:00")

 

[lauennien]

Bedankt voor de verdere reacties. Hier ga ik morgen even in duiken! De reactie van snb werkt niet. Hij gaat na 23.00 nu op 0.

Het is inderdaad een optie om een extra kolom in te voegen. Echter staat het nu zo in het "master" file waar dit uit geimporteerd wordt. Echter kan ik het natuurlijk ook laten importeren in een sheet waar deze extra kolom al in staat. Een interessante oplossing de wederom erg logisch is.

De opbouw van de code van HSV ken ik niet. Leuk om dit te leren. De code van VenA is inderdaad een stuk korter. Ik was al lang blij dat ik hem werkend had gekregen :d

 

[lauennien]

Bedankt voor de tips. De formule is versimpeld en werkt nu wanneer we een volgende dag in gaan. Echter werkt deze alleen met een beperkt bereik (zie cel H124 t/m H127). Wanneer ik in alle A cellen laat zoeken zoekt hij de laatste waarde die niets met de datum van vandaag te maken heeft.

Iemand een oplossing? (Zie voorbeeld).

 

[VenA]

Ik begrijp je vraag niet. Wat wil je zoeken? En waarom 'zweeft' het ergens in het voorbeeldje?

@HSV,

=--TEKST(A1;"0000\/00\/00")+--TEKST(B1;"00\:00")

is net iets korter:d Ik houd het bij de lange formule die door bijna iedereen te begrijpen is. Hocus pocus uit de gedachten van een ontwikkelaar is niet door iedereen te begrijpen. En de help kent deze ook niet.

 

[lauennien]

Ik had het in het midden gezet omdat het dan even naast de datum van vandaag stond. De formule die daar staat dient het getal behorend bij die datum en tijd op te zoeken. Deze werkt binnen een klein bereik. Als ik het bereik vergroot naar heel "A" (andere formule) zoekt hij iets anders op gek genoeg. Ik vind het lastig uit te leggen. Hopelijk helpt dit.

 

[HSV]

@HSV,
is net iets korter:d Ik houd het bij de lange formule die door bijna iedereen te begrijpen is.

Gelukkig zijn we geen siamese tweeling.

Als antwoord op de vraag:
Sorteren van klein naar groot.

 

[snb]

Ik dacht dat je met getallen gemakkelijker kon rekenen dan met datums:

Voor het laatste bestand van de OP (waar kolom A dus overbodig is)
En sorteren er ook niet toe doet:

=SUMPRODUCT((VALUE(B1:B200&TEXT(C1:C200;"0000"))>201509261000)*(VALUE(B1:B200&TEXT(C1:C200;"0000"))<201509261130)*D1:D200)

of

=SUMPRODUCT((--(B1:B200&TEXT(C1:C200;"0000"))>201509261000)*(--(B1:B200&TEXT(C1:C200;"0000"))<201509261130)*D1:D200)

 

[WHER]

@ HSV,

Goed gevonden met die "0000\/00\/00"
In je tweede formule uit post 5 mogen de twee dubbele min-tekens eigenlijk weggelaten worden, de + tussen de twee helften van de formule is voldoende om hetzelfde effect te hebben.

 

[snb]

Waarom dan niet ?

=--TEXT(A1&TEXT(B1;"0000");"0000\/00\/00 00\:00")

 

[WHER]

Ook goed natuurlijk.

 

[lauennien]

Als ik het allemaal goed begrijp heb ik nu heel effectieve manieren om de gegevens om te zetten naar de datum en tijd. Echter nog geen oplossing om mijn formule naar de volgende dag te tillen. Het probleem blijft dat de formule verder moet kijken (in dit geval 1 uur en 1,5 uur). Dit werkt uitstekend behalve wanneer hij de volgende dag in moet gaan ( na 22.59 en tot 00:01)

Op dit moment heb ik de volgende formules die (gedeeltelijk) werken:

=SOMPRODUCT((Sheet3!$A$1:$A$39807=TEKST(NU();"jjjjmmdd"))*(Sheet3!$B$1:$B$39807=UUR(NU()+"1:00")*100)*(Sheet3!$C$1:$C$39807))+SOMPRODUCT((Sheet3!$A$1:$A$39807=TEKST(NU();"jjjjmmdd"))*(Sheet3!$B$1:$B$39807=UUR(NU()+"1:00")*100+30)*(Sheet3!$C$1:$C$39807))

Gebruikte ik nu maar deze gaat niet naar de volgende dag. Deze telt dan de getallen (omzet) van die ochtend op. Omdat de datum nog geen dag verder is maar de uren + 1 als tekst worden gezien.

=VERT.ZOEKEN(NU()+"1:00";$A2:$D96;4;WAAR)+VERT.ZOEKEN(NU()+"1:30";$A2:$D96;4;WAAR)

Deze leek te werken, echter springt deze telkens op #n/b. Dit ligt aan +1:00, als getal werkt dit ook niet. Als ik +1 doe (1 dag) dan werkt het wel. Echter zoekt hij dan weer het laatste getal van de huidige dag op.

De overige formules hadden alleen betrekking op het vertonen van de tijd/datum zoals het hoort. Echter kan ik hier dus nog niet goed mee zoeken. Iemand nog suggesties?

 

[snb]

Je hebt blijkbaar mijn oplossing uit #11 over het hoofd gezien.

 

[lauennien]

Hoi SNB,

Die formule heb ik wel gekopieerd en in de sheet gezet. Echter daarna over het hoofd gezien. Stom. Die werkt zeker. Echter moet de sheet automatisch rekenen. In de formule staan nu vaste dagen en tijdstippen waar ik eerst TEKST(NU();"jjjjmmdd" voor gebruikte. Dit lukte mij niet en zodoende had ik die even geparkeerd ;-). Hij moet dus automatisch doorrekenen en ieder half uur een half uur verder kijken.

 

[WHER]

Op basis van de suggesties van VenA, HSV en SNB betreffende de hulpkolom met datum/tijd.

 

[lauennien]

Bekijk bijlage Voorbeeld3.xlsmwhoahhh...!!!! Hij werkt. Super zeg WHER. En de rest natuurlijk ;-). Je hebt meteen mijn volgende probleem getackeld. Ik wilde namelijk ook een makkelijkere manier om eventueel een groter bereik te pakken (bijvoorbeeld de komende 24 uur). Bijgevoegd nog een keer het bestand. Ik heb een kleine aanpassing gedaan. Hij keek namelijk naar komend uur of 1,5 uur. Ik wilde echter dat hij nu 23.04 tussen 00.00-01.00 kijkt. Zo moet hij denk ik goed zijn.

Mijn eeuwige dank aan jullie allemaal!! Ik ga morgen nog even verder rommelen. Ik laat het topic nog even op open staan voor het geval ik weer een moeilijk momentje krijg.

 

[snb]

zonder hulpkolommen een half uur voruit 'kijken'

=SUMPRODUCT((--(Sheet3!A1:A192&TEXT(Sheet3!B1:B192;"0000"))>--TEXT(FLOOR(NOW();1/48);"jjjjmmdduumm"))*(--(Sheet3!A1:A192&TEXT(Sheet3!B1:B192;"0000"))<--TEXT(CEILING(NOW();1/48)+1/48;"jjjjmmdduumm"))*Sheet3!C1:C192)