Tijden uit laten rekenen in Excel 2010, maar dan anders.

[coolhand]

Het volgende:

Tijden moeten worden opgeteld als:

* het valt tussen 03:00 en 21:00.

Vb1:
Blok begint om 02:30 en eindigt om 03:30 ==> Totale duur is 00:30

Vb2:
Blok begint om 12:00 en eindigt om 13:00 ==> Totale duur is 01:00

Vb3:
Blok begint om 20:00 de ene dag en loopt door tot de volgende dag 13:00 ==> Totale duur is 12:00

De volgende formules heb ik geprobeerd:

=IF(AND(G2="Count";H2="Count");(C2+D2)-(A2+B2);IF(G2="Count";H2-B2;IF(H2="Count";(D2-G2);"")))

Hierbij staat in:
G2 de volgende formule:

=IF(B2>0,125;"Count";0,125)

==> Doel hiervan is de tekst Count weer te geven als hij binnen de range valt.

en in H2:

=IF(D2<0,791666666666667;"Count";0,791666666666667)

==> Doel hiervan is de tekst Count weer te geven als hij binnen de range valt.

De gedachte achter kolom I was de volgende:

- Als G en H Count => Normaal
- Als G Count is en H niet => Tijd in B gebruiken en gecorrigeerde tijd uit H
- Als G geen Count is en H wel => Gecorrigeerde tijd uit G gebruiken en normale tijd uit D.


Zoals in de bijlage te zien is rekent hij rij 1 en 2 goed door, echter in rij 3 gaat het fout. Hij komt daar met 64:80:00 terwijl het 40:38 moet zijn (immers op 19/3 08:38, op 21/3 en 22/3 16:00)

 

[coolhand]

Ik heb ondertussen de volgende formules aangepast:
Kolom G ==>

=IF(AND(B2>=0,125;B2<=0,791666666666667);"Count";0,125)

Kolom H ==>

=IF(D2>0,791666666666667;0,791666666666667;"Count")

Hij blijft echter incorrect rekenen op het moment dat de datumgrens overschreden wordt??

Iemand enig idee??

 

[Cobbe]

(immers op 19/3 08:38, op 21/3 en 22/3 16:00)

en 20/3 (24uur) dan?

 

[coolhand]

Hoe bedoelt u?

Excel komt daar met 64:38, terwijl het 40:38 zou moeten zijn (1x 08:38 en 2x 16:00).

 

[Cobbe]

19/03/2012	10:22	22/03/2012	3:00	64:38:00

In uw berekening doe je :

"22/03/2012 3:00 - 19/03/2012 10:22 = 64:38:00

en dat klopt als een bus.

 

[Haije]

coolhand,

het valt tussen 03:00 en 21:00

even ter controle:
19-03 van 10:22 tot 21:00 = 10:38 uur
20-03 van 03:00 tot 21:00 = 18:00 uur
21:00 van 03:00 tot 21:00 = 18:00 uur

10:28 + 18:00 + 18:00 = 46:38 en NIET 40:38

wijzig de formule in kolom E in:

=(C2+D2)-(A2+B2)[COLOR="#FF0000"]-((C2-A2)*6/24)[/COLOR]

De rode toevoeging trek voor elke dag 6 uur af, nl van 0=3 en van 21-24 uur

 

[coolhand]

Bedankt voor het meedenken, echter op die manier trekt hij standaard 6 uur af van elke waarde. Het is echter niet gezegd dat iedere invoer een volledige dag beslaat.

In de nieuwe bijlage heb ik een grafische voorstelling gemaakt van de methode.
Bekijk bijlage Uren probleem grafisch.xlsx

 

[Haije]

coolhand,

jou veronderstelling klopt niet!
Er wordt alleen 6 uur afgetrokken als de einddatum verschilt van de begindatum.
Heb je het geprobeerd? Bij mij wijzigden de uitkomsten op de andere regels niet!

 

[coolhand]

Hi Haije,

Jij hebt gelijk in je antwoord. Verder zie ik dat ik het tijdsblok ook verkeerd heb weergegeven. Het moet zijn van 05:00 - 21:00. Voor de factor heb ik dus nu 8/24 ipv 6/24 genomen.

Ik heb je voorstel in mijn excelsheet verwerkt en inderdaad werkt dat voor een 2-tal voorbeelden die ik gegeven had. Echter in een 2-tal voorbeelden ook niet.

De voorbeelden waarin het niet werkt heb ik in het excel sheet in rijen 8 - 10 gezet.

Dit zijn de volgende regels:

1. Indien de starttijd voor 05:00 ligt neemt hij alsnog de starttijd mee
2. Indien de einddtijd na 21:00 ligt

Bekijk bijlage Uren probleem rev1.xlsx

 

[gast0224]

Als je wilt rekenen met tijden in Excel, kun je daar een aangepaste celeigenschap voor gebruiken. Deze celeigenschap kun je vinden bij de celeigenschappen op tabblad "Getal". Daar selecteer je de volgende eigenschap:

:mm:ss

in de cel van de uitkomst. Zie in de bijlage een voorbeeld. Je hebt dan geen formules nodig.

Bekijk bijlage Voorbeeld.xlsx

 

[coolhand]

Bedankt voor de input, Ik ben op de hoogte van de verschillende formats welke een andere uitwerking hebben. Ik gebruik in mijn excel sheet nu het format ==> Time => 37:30:55. deze notatie is nodig omdat er nog meerdere sheets aanvullend data gebruiken.

 

[sylvester-ponte]

of deze met vba:

groet sylvester

 

[coolhand]

Hoi Sylvester,

Dat zou inderdaad een goede mogelijkheid zijn, echter op mijn werkplek worden vba's en macro's vanwege beveiliging uitgeschakeld dus dat gaat helaas niet lukken.

 

[huijb]

@coolhand Zou je willen stoppen met het onnodig quoten van berichten. Heb nu al diversen reacties van je aangepast. Als je direct na een antwoord reageert hoef je niet te quoten

 

[coolhand]

@huijb, Will do.

 

[coolhand]

@ Sylvester:

Ik heb je sheet op mijn eigen laptop gebruikt en daar werkt het inderdaad "As advertised". Kan je mij misschien iets meer vertellen over de manier waarop de functie werkt:

=PauzeOfWerkVanTot(A2+B2;C2+D2;werktijdentabel)

Zover als ik het nu zie bepaalt hij aan de hand van de waardes in Cel A2+B2 en C2+D2 wat het bereik is, waarna hij eventueel het bereik wat in de werktijdentabel staat er af trekt. Ik kan in excel de formule niet verder uitkleden.

Ik ga toch proberen om VBA op mijn werkplek geactiveerd te krijgen, kan je misschien vertellen hoe de formule (want door jou zelf ontworpen denk ik) in mijn eigen werkblad te copieren is?

Alvast bedankt.

 

[sylvester-ponte]

hallo coolhand
hier een summiere uitleg:

open het bestand Bekijk bijlage Uren probleem (4.xlsm
na activeren van de macro's ed
druk op Alt F11
in module 1 staat de functie beschreven met een beetje uitleg
de functie gebruikt een andere functie "PauzeOfWerkPerDag"

zie uitleg

ps ik heb het programma ietsje verbeterd (regel toe gevoegd )

om de functie te kunnen gebruiken in een ander exel_bestand ,
moet je de hele tekst in module1 kopiëren en plakken in een module in het andere bestand.
eventueel moet je een module toevoegen.

groet sylvester

ps je kan de werktijden tabel naar beneden uitbreiden zo ver je maar wil zie voorbeeld

laat de werktijden in de werktijdentabel niet overlappen

 

[sylvester-ponte]

ik hoop dat je gezien hebt dat de vorige post is vervangen door een nieuwe
ik was in het voorbeeld bestand vergeten de formule door te trekken

groet sylveseter

 

[coolhand]

@sylvester,

Ik heb je nieuwe formule in mijn werkblad gecopieerd en het werkt perfect!! Hartelijk bedankt voor de hulp.
Ik snap nog niet helemaal wat alle regels in de VBA-code doen, dus daar moet ik mezelf nog even in bij scholen..........

Thnx.