Utmaning gällande handläggningstid mellan tid och datum - Excel - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

Sök Forumets startsida

Forum

Diskussioner

Excel

Utmaning gällande handläggningstid mellan tid och datum

Föregående

Nästa

2023-05-08 12:48

Kendo

Medlem sedan: 2023-03-28

28 inlägg

Utmaning gällande handläggningstid mellan tid och datum

Hej,

Jag försäker räkna ut handläggningstiden baserat på nedan information men lyckas inte själv! Finns det någon här som har sitter på kunskapen vore det oerhört uppskattat.

Kriterierna är följande.

Vardagar (mån-fre) ska handläggningstiden beräknas mellan 07:00 och 18:00 - och på helgdagar (lör-sön) mellan 10:00 och 14:00.
Vet inte om det finns något sätt att identifiera röda dagar som helgdagar men annars skulle det duga gott med mån-fredag och lördag-söndag som parametrar. Om prio är 1 ska handlängningstiden däremot räknas dygnets alla timmar.

	A	B	C	D	E
1	Prio	Start datum	Start tid	Slut datum	Slut tid
2	1 Låg	2023-04-03	7:26:08	2023-04-03	7:51:00
3	2 Låg	2023-04-03	7:32:03	2023-04-03	7:52:00
4	3 Låg	2023-04-03	7:32:08	2023-04-03	7:54:00

Vänligen
Ken

2023-05-08 13:57

Gondi

Medlem sedan: 2023-03-28

341 inlägg

Re: Utmaning gällande handläggningstid mellan tid och datum

Hej Ken,

Jag hjälpte Nisse med en liknande fråga för några veckor sedan. Kolla in den här tråden och se om den hjälper dig.

www.excelforum.se/Forumetsstartsida/tabid/174/forumid/2/threadid/10594/scope/posts/Default.aspx

2023-05-08 15:41

anonymous

Medlem sedan: 2020-06-29

474 inlägg

Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Ändrad av anonymous på 2023-05-08 15:43:56

Du kan identifiera helger på flera olika sätt. Det här ger t.ex Numret på veckodagen (mån1->sön7)
=VECKODAG(B2;2)

Och då kan du använda ett vilkor i stil med

=OM(VECKODAG(B2;2)<6;veckokod;helgkod)

Ett smartare sätt är att använda NETTOARBETSDAGAR

=NETTOARBETSDAGAR(B2;D2)=0
Om den är 0 är det helg, annars är det veckodag.

=OM(NETTOARBETSDAGAR(B2;D2)>0;veckokod;helgkod)

Fördelen är att du kan lägga en lista med röda-dagar på ett hjälpblad och lägga in in i formeln. Om du vill skilja på helger och annan ledighet (t.ex semester) så kan du använda den här formeln istället:

NETTOARBETSDAGAR.INT(startdatum; slutdatum; [helg]; [lediga])

Sen måste du bara slå ihop villkoren. Eftersom "PRIO1" är den konstigaste får den komma först (jag vet inte om det är texten "prio1" du letar efter. Ändra till det som faktiskt står i kolumn A)

=OM(A2="prio1";E2-C2....

Sen vill du ha begränsningar på start/sluttid. Exempel för vardagar. Starttiden (som du vill räkna på) måste var Minst kl 07, eller tiden som står. DVS

=MAX(C2;"07:00")

Sen vill du även se till att den starttid du fått fram ligger före 18:00.

=MIN(MAX(C2;"07:00");"18:00")

Samma sak med sluttiden

=MIN(MAX(E2;"07:00");"18:00")

och då blir handläggningstiden på varddagar

=MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")

och på helger

=MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00")

Så vi bygger vidare på om-formeln

=OM(A2="prio1";(E2-C2);OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

med radbrytningar:

=OM(A2="prio1";(E2-C2);
OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");
MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

Jag tog inte med datumen i beräkningara. Vill du att formeln skall funka över midnatt får du ändre C2 till (B2+C2) osv

Formatera din cell som tid eller räkna om till decimaltal enligt Gondis instruktioner

2023-05-10 09:06

Kendo

Medlem sedan: 2023-03-28

28 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Tusen tack!

Jag kom nästan i mål med din hjälp men har två kvarvarade detaljer som jag inte lyckas lösa.

Om jag kör denna formel =OM(D12="1 Låg";(H12+G12-F12+E12);OM(NETTOARBETSDAGAR(E12;G12)=0;MIN(MAX(H12;"10:00");"14:00")-MIN(MAX(F12;"10:00");"14:00");MIN(MAX(H12;"07:00");"18:00")-MIN(MAX(F12;"07:00");"18:00")))

Fungerar inte Rad 13 enlig nedan tabell när G13 överskrider mer än ett dygn, den räknar fortfarande 14 timmar, precis som rad 12 fast det skiljer ett dygn.

Ovan formel fungerar inte heller med övriga prioriteringar utan endast med "1 Låg" annars får jag ####### som syns i I14 i nedan tabell.

Kör jag nedan formel istället funderar det med alla fyra prioriteringar men då räknas endast timmarna för ett dygn och formeln ta rinte hänsyn till om slutdatum är senare än startdatum.

=OM(D12="1 Låg";(H12+G12-F12+E12);OM(NETTOARBETSDAGAR(E12;G12)=0;MIN(MAX(H12;"10:00");"14:00")-MIN(MAX(F12;"10:00");"14:00");MIN(MAX(H12;"07:00");"18:00")-MIN(MAX(F12;"07:00");"18:00")))

	D	E	F	G	H	I	J
12	1 Låg	2023-04-12	18:00	2023-04-14	08:00	14:00	Fungerar utefter angivnga förutsättningar
13	1 Låg	2023-04-12	18:00	2023-04-15	08:00	14:00	Fungerar inte när stängt datum överskrider mer än 1 dygn
14	4 Låg	2023-04-12	18:00	2023-04-14	08:00	########	Funderag endast på Prioritet "1 Låg", inte 2 3 eller ""4 Låg"

Mvh
Kendo

*********************************************************

anonymous skrev:

Du kan identifiera helger på flera olika sätt. Det här ger t.ex Numret på veckodagen (mån1->sön7)
=VECKODAG(B2;2)

Och då kan du använda ett vilkor i stil med

=OM(VECKODAG(B2;2)<6;veckokod;helgkod)

Ett smartare sätt är att använda NETTOARBETSDAGAR

=NETTOARBETSDAGAR(B2;D2)=0
Om den är 0 är det helg, annars är det veckodag.

=OM(NETTOARBETSDAGAR(B2;D2)>0;veckokod;helgkod)

NETTOARBETSDAGAR.INT(startdatum; slutdatum; [helg]; [lediga])

=OM(A2="prio1";E2-C2....

Sen vill du ha begränsningar på start/sluttid. Exempel för vardagar. Starttiden (som du vill räkna på) måste var Minst kl 07, eller tiden som står. DVS

=MAX(C2;"07:00")

Sen vill du även se till att den starttid du fått fram ligger före 18:00.

=MIN(MAX(C2;"07:00");"18:00")

Samma sak med sluttiden

=MIN(MAX(E2;"07:00");"18:00")

och då blir handläggningstiden på varddagar

=MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")

och på helger

=MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00")

Så vi bygger vidare på om-formeln

=OM(A2="prio1";(E2-C2);OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

med radbrytningar:

=OM(A2="prio1";(E2-C2);
OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");
MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

Jag tog inte med datumen i beräkningara. Vill du att formeln skall funka över midnatt får du ändre C2 till (B2+C2) osv

Formatera din cell som tid eller räkna om till decimaltal enligt Gondis instruktioner

2023-05-10 12:52

anonymous

Medlem sedan: 2020-06-29

474 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Om du är ute efter texten "låg" så har inte excel någon bra ja/nej funktion utan du får göra en knorr:

=ANTAL.OM(D12;"*låg*")=1

eller med sök (som ger positionen där "låg" står. Dvs siffran 3, eller ett felmeddelande om "låg" inte hittas:

=ÄRTAL(SÖK("låg";D12))

dvs din formel får börja med någonting i stil med:

=OM(ANTAL.OM(D12;"*låg*")=1;

Om du vill ha med datumet så är det enklast att köra en parantes runt de datum/tidspar som hör ihop. Annars missar du minustecknet för starttiden. Dvs

(H12+G12-F12+E12) bör skrivas som (H12+G12)-(F12+E12). Eller (H12+G12-F12-E12)

=OM(ANTAL.OM(D12;"*låg*")=1;(G12+H12)-(E12+F12)....

Men. Om du vill köra över flera dagar med diverse gott och blandat blir det svårt. Här har du ett excempel när folk har klurat hårt. Men jag tror inte att de klarar en blandning av helger och varddagar.

https://exceljet.net/formulas/get-work-hours-between-dates-and-times

'=(NETWORKDAYS(start,end)-1)*(upper-lower)
+IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper)
-MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)

Där start och end är start/slutdatum medan UPPER och Lower är tiden dina jobb startar på

I dina data borde det här gälla (för varddagarna):

=(NETTOARBETSDAGAR(E12;G12)-1)*("18:00"-"10:00")+OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(REST(G129+H12;1);"18:00";"07:00");"18:00")-MEDIAN(NETTOARBETSDAGAR(E12;E12)*REST(E12+F12;1);"18:00";"07:00")

Det här är första delen som ger antal arbetstimmar under dagarna som ligger mellan två datum och som inträffar under arbetstid (på varddagar, inte helger)

=(NETTOARBETSDAGAR(E12;G12)-1)*("18:00"-"07:00")

Dvs hela dagar * hur stor del av dagen man jobbar

del två lägger till arbetstiden för den första och sista dagen (om de är arbetsdagar). De använder medianvärdet istället för mina mina min/max. Men det borde bli lika

+OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(REST(G12+;1);"18:00";"07:00");"18:00")

och starttiden

+OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(REST(G129+H12;1);"18:00";"07:00");"18:00")

-MEDIAN(NETTOARBETSDAGAR(E12;E12)*REST(E12+F12;1);"18:00";"07:00")

Se till att visningsformatet för tiden tillåter att tiden visar mer än 24 timmar.

Eller ett format som även visar dagar:

Nu är problemet hur tusan du skall lägga till helgernar.

Jag antar att det här ger antalet helgdatum mellan två datum

=G12-E12+1-(NETTOARBETSDAGAR(E12;G12))
och timmar
=(G12-E12+1-(NETTOARBETSDAGAR(E12;G12)))*("14:00"-"10:00")

sen får du köra rad 2 och tre, fast med bakvända "omvilkor". Antar jag...

fast där tog min ork slut.

2023-05-10 15:26

anonymous

Medlem sedan: 2020-06-29

474 inlägg

Re: Utmaning gällande handläggningstid mellan tid och datum

Ändrad av anonymous på 2023-05-10 15:30:04

kolla version 2 på den här sidan "smartare". Fast innan de förenklade:

https://chandoo.org/wp/working-hours-...

Totalt antal varddagar under perioden

=NETTOARBETSDAGAR(G12;I12)

och helger (alla dagar som inte är jobbdagar)

=(DAGAR(I12;G12)+1-NETTOARBETSDAGAR(G12;I12))

då kan du börja med att räkna ut hur många timmar det hade blivit om du hade arbetat hela dagarna (11 t /vardag, 4t /helgdag)

=NETTOARBETSDAGAR(G12;I12)*("18:00"-"07:00")+((DAGAR(I12;G12)+1)-NETTOARBETSDAGAR(G12;I12))*("14:00"-"10:00")

sen drar du bort alla timmar du inte jobbade sista dagen (med mediantricket) Dvs om du slutar före 14/18 så räknas de timmarna bort

=OM(NETTOARBETSDAGAR(I12;I12);"18:00"-MEDIAN(J12;"07:00";"18:00");"14:00"-MEDIAN(J12;"10:00";"14:00"))

samma sak för första dagen:

=OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(H12;"07:00";"18:00")-"07:00";MEDIAN(J12;"10:00";"14:00")-"10:00")

Slå ihop till en lång jävla formel (en rad):

=NETTOARBETSDAGAR(G12;I12)*("18:00"-"07:00")+((DAGAR(I12;G12)+1)-NETTOARBETSDAGAR(G12;I12))*("14:00"-"10:00")
-OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(H12;"07:00";"18:00")-"07:00";MEDIAN(J12;"10:00";"14:00")-"10:00")
-OM(NETTOARBETSDAGAR(I12;I12);"18:00"-MEDIAN(J12;"07:00";"18:00");"14:00"-MEDIAN(J12;"10:00";"14:00"))

När du är nöjd med hur den funkar kan du lägga in "prio"-vilkoret (skall det vara hög eller låg?):

=OM(ANTAL.OM(D12;"*hög*")=1;(G12+H12)-(E12+F12);
NETTOARBETSDAGAR(G12;I12)*("18:00"-"07:00")+((DAGAR(I12;G12)+1)-NETTOARBETSDAGAR(G12;I12))*("14:00"-"10:00")
-OM(NETTOARBETSDAGAR(G12;G12);MEDIAN(H12;"07:00";"18:00")-"07:00";MEDIAN(J12;"10:00";"14:00")-"10:00")
-OM(NETTOARBETSDAGAR(I12;I12);"18:00"-MEDIAN(J12;"07:00";"18:00");"14:00"-MEDIAN(J12;"10:00";"14:00")))

(alla fyra rader är en formel. Obs att det blev en slutparates till nu)

Jag har inte testat speciellt noga så du får göra lite exempel och se om du får ut rätt svar.

2023-06-01 13:22

Kendo

Medlem sedan: 2023-03-28

28 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Hej,

JAg är jättetacksam för all hjälp och jag är NÄSTNA hemma med formeln nedan.

=OM(D12="1 Låg";(H12+G12-F12+E12);OM(NETTOARBETSDAGAR(E12;G12)=0;MIN(MAX(H12;"10:00");"14:00")-MIN(MAX(F12;"10:00");"14:00");MIN(MAX(H12;"07:00");"18:00")-MIN(MAX(F12;"07:00");"18:00")))

Det enda som kvarstår är att formeln inte fungerar över midnatt.
Du skre motsvarande "För att formeln skall funka över midnatt får du ändre F3 till (E3+F3) osv" men jag får det inte att fungera. Skulle du kunna ge skriva ett exempel?

EX får jag 14:00 som resultat för nedan

D E F G H I

Prioritet	Registrerat datum	Registrerat tid	Stängt datum	Stängt tid	Lösningstid
1 Låg	2023-04-12	18:00	2023-04-15	08:00	14:00

anonymous skrev:

Du kan identifiera helger på flera olika sätt. Det här ger t.ex Numret på veckodagen (mån1->sön7)
=VECKODAG(B2;2)

Och då kan du använda ett vilkor i stil med

=OM(VECKODAG(B2;2)<6;veckokod;helgkod)

Ett smartare sätt är att använda NETTOARBETSDAGAR

=NETTOARBETSDAGAR(B2;D2)=0
Om den är 0 är det helg, annars är det veckodag.

=OM(NETTOARBETSDAGAR(B2;D2)>0;veckokod;helgkod)

NETTOARBETSDAGAR.INT(startdatum; slutdatum; [helg]; [lediga])

=OM(A2="prio1";E2-C2....

Sen vill du ha begränsningar på start/sluttid. Exempel för vardagar. Starttiden (som du vill räkna på) måste var Minst kl 07, eller tiden som står. DVS

=MAX(C2;"07:00")

Sen vill du även se till att den starttid du fått fram ligger före 18:00.

=MIN(MAX(C2;"07:00");"18:00")

Samma sak med sluttiden

=MIN(MAX(E2;"07:00");"18:00")

och då blir handläggningstiden på varddagar

=MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")

och på helger

=MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00")

Så vi bygger vidare på om-formeln

=OM(A2="prio1";(E2-C2);OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

med radbrytningar:

=OM(A2="prio1";(E2-C2);
OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");
MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

Jag tog inte med datumen i beräkningara. Vill du att formeln skall funka över midnatt får du ändre C2 till (B2+C2) osv

Formatera din cell som tid eller räkna om till decimaltal enligt Gondis instruktioner

2023-06-01 13:22

Kendo

Medlem sedan: 2023-03-28

28 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Hej,

JAg är jättetacksam för all hjälp och jag är NÄSTNA hemma med formeln nedan.

=OM(D12="1 Låg";(H12+G12-F12+E12);OM(NETTOARBETSDAGAR(E12;G12)=0;MIN(MAX(H12;"10:00");"14:00")-MIN(MAX(F12;"10:00");"14:00");MIN(MAX(H12;"07:00");"18:00")-MIN(MAX(F12;"07:00");"18:00")))

EX får jag 14:00 som resultat för nedan

D E F G H I

Prioritet	Registrerat datum	Registrerat tid	Stängt datum	Stängt tid	Lösningstid
1 Låg	2023-04-12	18:00	2023-04-15	08:00	14:00

anonymous skrev:

Du kan identifiera helger på flera olika sätt. Det här ger t.ex Numret på veckodagen (mån1->sön7)
=VECKODAG(B2;2)

Och då kan du använda ett vilkor i stil med

=OM(VECKODAG(B2;2)<6;veckokod;helgkod)

Ett smartare sätt är att använda NETTOARBETSDAGAR

=NETTOARBETSDAGAR(B2;D2)=0
Om den är 0 är det helg, annars är det veckodag.

=OM(NETTOARBETSDAGAR(B2;D2)>0;veckokod;helgkod)

NETTOARBETSDAGAR.INT(startdatum; slutdatum; [helg]; [lediga])

=OM(A2="prio1";E2-C2....

Sen vill du ha begränsningar på start/sluttid. Exempel för vardagar. Starttiden (som du vill räkna på) måste var Minst kl 07, eller tiden som står. DVS

=MAX(C2;"07:00")

Sen vill du även se till att den starttid du fått fram ligger före 18:00.

=MIN(MAX(C2;"07:00");"18:00")

Samma sak med sluttiden

=MIN(MAX(E2;"07:00");"18:00")

och då blir handläggningstiden på varddagar

=MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")

och på helger

=MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00")

Så vi bygger vidare på om-formeln

=OM(A2="prio1";(E2-C2);OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

med radbrytningar:

=OM(A2="prio1";(E2-C2);
OM(NETTOARBETSDAGAR(B2;D2)=0;MIN(MAX(E2;"10:00");"14:00")-MIN(MAX(C2;"10:00");"14:00");
MIN(MAX(E2;"07:00");"18:00")-MIN(MAX(C2;"07:00");"18:00")))

Jag tog inte med datumen i beräkningara. Vill du att formeln skall funka över midnatt får du ändre C2 till (B2+C2) osv

Formatera din cell som tid eller räkna om till decimaltal enligt Gondis instruktioner

2023-06-02 04:00

Gondi

Medlem sedan: 2023-03-28

341 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Hej Ken,

Det är bara två små saker som behöver justeras.

Lägg till parenteser runt F12+E12.

=OM(D12="1 Låg";(H12+G12-(F12+E12))....

Gör om visningsformatet för kolumn I från tt:mm till [tt]:mm så att de ackumulerade timmarna visas när lösningstiden är längre än 24 timmar.

2023-06-02 06:59

Kendo

Medlem sedan: 2023-03-28

28 inlägg

Re: Du kan identifiera helRe: Utmaning gällande handläggningstid mellan tid och datum

Tack Gondi! Det gjorde susen! :D

Upptäckte dock att det kvarstår en sista detaljt. Om det står något annat än "1 Låg" i D14 så beräknas inte tiden över midnatt. De andra värden som finns är "2 Låg" "3 Låg" och "4 Låg". Det som skiljer "1 Låg" från övriga värden är att "1 Låg" ska beräknas över dygnets alla timmar.

=OM(D14="1 Låg";(H14+G14-(F14+E14));OM(NETTOARBETSDAGAR(E14;G14)=0;MIN(MAX(H14;"10:00");"14:00")-MIN(MAX(F14;"10:00");"14:00");MIN(MAX(H14;"07:00");"18:00")-MIN(MAX(F14;"07:00");"18:00")))

Vänligen
Ken

Gondi skrev:

Hej Ken,

Det är bara två små saker som behöver justeras.

Lägg till parenteser runt F12+E12.

=OM(D12="1 Låg";(H12+G12-(F12+E12))....

Gör om visningsformatet för kolumn I från tt:mm till [tt]:mm så att de ackumulerade timmarna visas när lösningstiden är längre än 24 timmar.

Sida 1 av 4

Utmaning gällande handläggningstid mellan tid och datum