Formel: Udregn samlet antal timer uden for to klokkeslet
Hej
Jeg benytter et hjemmestrikket tidsregistreringsark. Jeg vil gerne kunne registrere hvor meget tid samlet jeg bruger uden for "normal arbejdstid".
Jeg kan fint finde formler på udregning af antal timer inden for to klokkelset, men har brug for formel som beregner antal timer (og minutter) uden for to klokkeslet. Så f.eks. alle tider angivet uden for 8-17, skal tælles sammen. Så hvis der angives følgende 3 registgreringer 1.00-7.00, 7.00-10.00 og 11.00-18.30, så skal resultatet være 8.30 timer. Jeg har allerede en celle hvor den samlede tid udregnes for hver registrering. Om muligt vil jeg gerne bruge en formel, som udregner samtlige 35 mulige registreringspunkter.
Det er ikke lykkedes mig at finde logikken til at vende det rundt, til det jeg ønsker. Formlen lægger heller ikke om til at udregne samtlige værdier i et range, så lige nu har jeg lavet en lang formel som behandler samtlige 35 mulige registreringspunkter. Så, meeeget lang :) Men formlen virker ikke som den skal i forhold til det jeg ønsker.
Så findes der en formel som kan lave check på samtlige mulige 35 registreringsmuligheder.
Prøver at opstille hvad jeg har:
A1 B1 C1 Start tid Slut tid Varighed 1.00 7.00 6.00 7.00 10.00 3.00 11.00 18.30 7.30
Dem er der 35 rækker af. Og så har jeg brug for udregning af samlet tid som ligger uden for 8-17 for alle 35 rækker. I eksemplet er der i alt 16.30 timer og 8.30 af dem ligger uden for 8-17.
Håber det giver mening. Og hvis ikke der er en "range"/SUM formel som smart kan indfange det hele, så nøjes jeg gerne med formlen der kan udregne det pr. række. Så sætter jeg det selv sammen for samtlige rækker.
Jeg kan ikke rigtig se, hvodan f.eks. 07:00 - 10:00 kan blive 3 timer, hvis du vil have timer uden for intervallet 07:00 og 17:00. Det burde vel så være 1 time, da kun 1 time ligger uden for dit interval. Eller er der en anden logik i din opstilling?
A1 B1 C1 Start tid Slut tid Varighed 1.00 7.00 6.00 7.00 10.00 3.00 11.00 18.30 7.30
Fra 7.00-10.00 er der tre timer. Ud af de tre timer er 2 af dem inden for "normal" tid (8.00-10.00) og 1 time er uden for "normal" tid 7.00-8.00.
Kolonne C er blot en opmåling af hvor langt tid der mellem tid start og slut. Det er beregningen af hvor mange timer som ligger uden for "normal" tid for samtlige rækker jeg har brug for. Fiktivt kunne man forestille sig en kolonne D som udregnede antal timer uden for *"normal" tid. Og så blev alle D rækker lagt sammen i en celle. Jeg er bare ikke interesseret i at tilføre kolonne D, men blot have cellen, hvor det hele bliver regnet ud.
Kan man bruge en SUM funktion til at sammenregne alle 35 rækker som et range baseret på ovenstående formel? Eller er jeg nødt til at bruge SUM og så ellers sætte alle 35 rækker ind?
Nu har jeg fået bygget den samlede formel, som håndterer alle 35 rækker, men den er jo obskur lang. Så håber stadig på en der har the golden formula, til at forenkle den. Nedenfor den nye formel.
Hvis nogen overhovedet skal overveje en "golden formula" tror jeg du bliver nødt til at uploade din egen samlede formel (med eksempler på data og ønskede resultater) til for eksempel Dropbox. Det vil i hvert fald være en forudsætning for, at jeg vil engagere mig i det, uden dog at love, at jeg vil kunne komme med noget brugbart.
#6 - Jeg erindrer, at du i #2 skrev, at du ikke var interesseret i at inddrage en ekstra kolonne, til at udregne timer, udenfor det opstillede interval. Men det er jo faktisk det du gør i din formel, hvor den er betinget af, at du har en ekstra kolonne, for at udregne de faktiske tider indenfor de 2 intervaller!
Nej, formlen jeg for nuværende har bygget op, er ikke afhængig af en ekstra kolonne med udregningen af hvor mange af timer som ligger uden for normal tid. Den kolonne som du ser i eksempelarket udregner tid fra angivet starttid og angivet sluttid og denne eksisterer allerede i min opbygning, og det er helt fint den er der. Selve udregningen af timer uden for normal tid foregår inde i formlen. Og det er denne formel jeg spørger om kan simplificeres. Det jeg mente i #2 var at jeg gerne ville være fri for at oprette endnu en kolonne som for hver række registreringer også viser timer uden for normal tid. Håber det giver mening.
Det med at du ikke bruger en ekstra kolonne, kan jeg nu ikke rigtig se ikke skulle være tilfældet. Hvis jeg ser i din formel herunder: =SUM(HVIS(OG(B5<>"";D5<>"");F5-(HVIS(B5<D5;MAKS(0;MIN(D5;E3)-MAKS(B5;B3));MAKS(0;E3-D5)+MAKS(0;D5-B3))) Så er den jo afhængig af en mellemregning i kolonne F (vist med Fed skrift) i din formel.
Ellers så laver nedenstående formel, det samme som din formel, men uden mellemregning i en ekstra kolonne. =MEDIAN(0;1/3;1/3-A1)+HVIS(MEDIAN(0;17/24;17/24>A1);0;B1-A1)
#9: Er ikke helt sikker på hvad der er løsningen i dit forslag. I Sheet1, ligner det samme løsning som jeg fandt på Exceljet. "Udenfor" kolonnen er netop den jeg ønsker at undgå (om muligt). Sammenregningen i celle F11 i Tabel1, regner antal timer i alt ud - ikke timer udenfor? Når jeg formaterer den til [t]:mm, tæller den 13 (4+7+2).
#10: Det med den ekstra kolonne. Tror måske vi taler forbi hinanden. Når jeg siger ekstra kolonne, så er det en kolonne som viser for hver række af tidsregistrering, tiden forbrugt uden for normal tid. Det er denne kolonne jeg ønsker at undgå at skulle tilføre mit ark. Jeg har allerede en kolonne hvor det sammenlagte tidsforbrug for hver enkelt tidsregistrering udregnes. Så for én registrering, f.eks. 7-10, vil der fremgå 3:00. Min nuværende formel benytter denne kolonne, og det er helt OK at denne kolonne er der. Jeg ville gerne kunne udregne tid forbrugt uden for normal tid for samtlige rækker registreringer i én celle, og uden at have en kolonne for hver tidsregistrering som viser tid forbrugt uden for normal tid.
Den formel jeg har nu fungerer således. Men, som I kan se, er den meget tung (lang), og tanken var om det var muligt at koge den ned med noget "range", eller anden logik, uden at skulle tilføre denne ekstra kolonne med "tid forburgt uden for normal tid".
Jeg har forsøgt at lege med MEDIAN, men det lader til at jeg ikke kan få det til at fungere med range. I eksempel filen, har jeg forsøgt at omskrive formel til at tage range:
Umiddelbart er det stadig ikke den løsning jeg søger.
Kolonne A: start tid registrering Kolonne B: slut tid registrering Kolonne C: samlet tid fra start til slut tid
Disse kolonner eksisterer i mit ark.
Nu vil jeg gerne have én celle, hvor den samlede tid uden for normal arbejdstid udregnes for samtlige rækker af registreringer. Dette vil jeg gerne opnå uden at der oprettes endnu en kolonne (D), hvor antal timer uden for normal tid udregnes per registering (række). Der skal bare være kolonne A, B og C.
Den formel jeg leverede til sidst, udregner dette, uden at have kolonne D. Meeen, den er møj lang og uigennemskuelig. Og derfor spørger jeg til om den kan smartificeres, evt. med noget range eller andet trylleri.
Med mindre jeg har glippet noget i de foreslåede løsninger, så opfylder disse ikke ønsket. Hvis jeg har misset løsningen, beklager jeg meget og vil selvfølgelig værdsætte pointeren til hvor løsningen fremgår.
#13 - Jeg ved ikke, hvor "kæden" så at sige hopper af. Men et eller andet sted, så er der noget der ikke stemmer. I dit indlæg #13, har du igen skrevet, at du vil have beregnet timer "Uden for normal arbejdstid", hvilket du har gjort mange gange i dine indlæg. Og du ønsker ikke en ekstra kolonne til at gøre dette (hvilket heller ikke er nødvendigt).
I mit indlæg #12 har jeg netop lavet en sådan formel, med et eksempel vist.
Din egen formel, som du nævner løser problemet, skriver du, er jo afhængig af en ekstra kolonne for at kunne udregne de timer du ønsker.
Så nu ville jeg egentlig gerne vide, hvad det er i min formel i #12 der IKKE virker!
Og så ville det, for at undgå yderligere skriverier, og misforståelser, være bedst hvis du uploadede dit Ark, som det ser ud, og ikke blot de tilfældige opstillinger du har uploadet, eller vist, tidligere.
Formler fra såvel fhansen82 som ebea og jeg selv virker på en enkelt linje, men ikke på et range og uden at være fuldstændig skråsikker, så tror jeg ikke det er muligt. Det vil sige, det er måske muligt, hvis du har Office 365 og er tilknyttet det der hedder Office Insider og dermed har adgang til de spritnye funktioner LET og LAMBDA.
#15 - For at lave én samlet formel, dækkende et range, er det nødvendigt at fjerne den mellemregnings kolonne, som både OP har brugt, og du selv har brugt. Det gør formlen som jeg uploadede i #12. Men når så OP bliver ved at påstå at formlen ikke gør dette, så er det også som udgangspunkt, svært at lave en samlet formel.
OP skriver selv: Kolonne A: start tid registrering Kolonne B: slut tid registrering Kolonne C: samlet tid fra start til slut tid Disse kolonner eksisterer i mit ark.
Men det er jo ikke korrekt, da der er en ekstra kolonne i C kolonnen, hvor mellemregningen laves (B1-A1), og så først derefter formlen for at lave resten af udregningen!
Og så anser jeg det for rent tidsspilde, at bruge mere tid på dette.
#18 Det jeg ikke "mente virkede" var at den samlede tid uden for normal tid bliver beregnet samlet i én celle, uden at der oprettes endnu en kolonne med de individuelle udregnede timer uden for normal tid pr. række. Hvis jeg forstår dit oplæg korrekt, så er der oprettet en kolonne som beregner tid uden for normal tid pr. række (kolonne C), og så er der lavet en celle som viser summen af disse (celle E1). Men det er netop denne ekstra kolonne (kolonne C) som jeg gerne vil være foruden og indkoorporere beregningen i én og samme celle E1 i dit eksempel.
Her et nyt upload af mit ark. Det er i celle M57 at jeg ønsker udregningen af samlet antal timer uden for normaltid. Som nævnt tidligere er det lykkedes mig at få det til at virke, men formlen er meget lang, og jeg kan ikke gennemskue om resultat kan opnås via noget SUM el. lign. og den vej skære formlen ned. Formlen skal være dynamisk, således hvis tiderne som angiver start og slut for normaltid ændres, så sker dette ligeledes i formlen.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
