Just nu i M3-nätverket
Gå till innehåll

Rekommendera Poster

Hej.

 

Jag har ett litet knepigt problem jag skulle behöva hjälp med.

Jag håller på att skapa en tidsrapportering i Excel.

 

Jag jobbar blandat vardagar, helger, dagar och nätter. Alla dessa tider ger olika betalt per timme.

Så när jag ska skicka in min tidsrapport vill dom att timmarna ska vara uppdelade för varje OB nivå vi som vi har.

Nivåerna vi har är

OB1= 06:00-18:00 vardagar

OB2= 18:00-06:00 vardagar

OB3= 06:00-18:00 helg

OB4= 18:00-06:00 helg

 

Så, om jag t.ex börjar jobba 04:00 en tisdag morgon, och sen slutar klockan 20:00 samma kväll, då har jag alltså jobbat 12 timmar OB1, och 4 timmar OB2.

 

Det jag skulle vilja kunna göra är att skriva i den tid jag jobbat i två celler. Startdatum i ena och slutdatum i andra. Ex. 04:00 - 20:00

Sen vill jag att excel räknar ut hur många timmar OB2 som det blir, samt hur många timmar OB1 det blir.

 

Jag hade tänkt att ha separata celler för varje OB nivå.

 

Går detta att göra? Och om det går, går det att få den att känna av om det är helg också?

Alla datumena är datumformaterade och all tid är tidsformaterad.

 

Tacksam för hjälp.

 

/Fredrik

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Antag att datumet står i A2, starttiden står i B2 och sluttiden i C2

 

Vi börjar med att ignorera veckosluten. Då kan du räkna ut OB2 med hjälp av

=(B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24)

 

eller, om du vill ha det som decimaltal (lättare att sumera):

=((B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24))*24

 

Ob1 blir naturligtvis

=((C2-B2)+(B2<6/24)*(B2-6/24)+(C2>18/24)*(18/24-C2))*24

eller, enklare

=(C2-B2) - den cell där ob2 står.

 

För att hantera veckosluten så kan du exempelvis använda en omsats och VECKODAG()

 

Det här blir då en lämplig formel för OB4

=OM(VECKODAG(A2;2)>=6;((B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24))*24;0)

och det här för OB2

=OM(VECKODAG(A2;2)<6;((B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24))*24;0)

Ja, du fattar principen

 

Alla ovanstående formler har uppenbara brister. Den klarar inte röda dagar utanför veckoslutet och det skiter sig om arbetstiden går över midnatt.

 

Vill du hantera röda dagar så finns funktionerna "workday" osv. Men det är lite överkurs.

 

Vill du hantera arbetstider som går över midnatt så är det enklaste att skriva in både datum och tid i varje cell med start/stopptid och anpassa formlerna efter det. Annars blir du nästan tvungen att göra en massa vilkor som hanterar konstiga fall (om starttid är större än sluttid t.ex).

 

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

Ps:

Jag skrev in tidera som 6/24 osv. Du kan naturligtvis länka till en cell istället. Då kan du ju enkelt ändra start/stopptider

 

[inlägget ändrat 2008-06-26 16:17:13 av MH2]

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Hmmm. jag gjorde visst en tankevurpa där.

 

Om cellerna H2 och I2 antas innehålla start- och stopptid för OB1 så borde formeln för OB2 nog se ut så här:

 

=(B2<$H$2)*(MIN(C2;$H$2)-B2)+(C2>$I$2)*(C2-MAX(B2;$I$2))

Sen får man hänga på helghantering osv.

 

Vill du tillåta dygnsövergång så kan du testa

 

=OM(B2<C2;(B2<$H$2)*(MIN(C2;$H$2)-B2)+(C2>$I$2)*(C2-MAX(B2;$I$2));(B2<$H$2+1)*(MIN(C2+1;$H$2+1)-MAX(B2;$I$2))+(C2+1>$I$2+1)*(C2-$I$2))

Och, om ob2 står i cell E2 så blir väl OB1 så här:

=OM(B2<C2;C2-B2;C2+1-B2)-E2

 

Inte speciellt snyggt om jag får säga det själv..

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Hej.

 

Enligt första inlägget du skrev så har jag fått till det med OB1 och OB2 tiden. Edna problemet är när tiden går över midnatt.

 

Sen blev det lite snurrigt med andra inlägget där.

Du skrev att om man antar att cellerna H2 och I2 innehåller start och stopptid för OB1, så skulle formeln för OB2 se ut:

=(B2<$H$2)*(MIN(C2;$H$2)-B2)+(C2>$I$2)*(C2-MAX(B2;$I$2))

 

Jag förstår inte om jag ska använda samma celler som beskrevs i meddelandet innan.

 

Jag kan beskriva hur jag lagt upp cellerna så kanske du förklara vilka formler som ska vara vart.

 

A2: Datumet

B2: Startid för skiftet jag jobbat

C2: Stopptid för skftet jag jobbat.

D2: Där vill jag att OB1 timmarna ska hamna (06-18 vardag)

E2: Där vill jag att OB2 timmarna ska hamna (18-06 vardag)

F2: Där vill jag att OB3 timmarna ska hamna (06-18 helg)

G2: Där vill jag att OB4 timmarna ska hamna (18-06 helg)

 

Jag är väldigt tacksam för hjälpen och hoppas du kan hjälpa mig att klura ut detta.

 

Jag kan göra ett exempel på hur jag skulle vilja hur det ska se ut.

A2: 2008-07-01

B2: 04:00

C2: 19:00

D2: 12 (antal timmar mellan 06-18)

E2: 3 (antal timmar mellan 18-06)

F2: 0 (inga helgtimmar jobbade i detta exempel)

G2: 0 (inga helgtimmar jobbade i detta exempel)

 

 

 

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Okej, nu fick jag till det andra exemplet också. Var nog lite trött igår kväll när jag försökte få ihop det. Nu fungerar det utmärkt med nattöverträdelsen också.

Ska försöka få till helgtimmarna också bara. Har inte hunnit tittat på det än bara..

 

Tack än en gång för grym support.

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Jag tänkte säga att det är enkelt. Bara att sätt formeln från inlägg 2 i omformeln från inlägg 1.

Men så enkelt är det ju inte. Du måste ju kunna hantera övergången mellan helg och vardag också. Det finns väl skift som börjar vardag och startar helg t.ex?

 

Hmmm, varför känns det som om man missar någon enkel lösning här?

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Jo det finns ju sådana skift också. Där blir det ju onekligen ett problem.

Men jag har inte ens fått riktig ordning på med här med helgerna heller.

Använder mig av:

 

=OM(VECKODAG(A2;2)>=6;((B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24))*24;0) för OB4 tiden och:

 

=OM(VECKODAG(A2;2)<6;((B2<6/24)*(6/24-B2)+(C2>18/24)*(C2-18/24))*24;0) för OB2 tiden under en helgdag (lördag)

 

men får ett utropstecken i OB2 rutan (inkonsekvent formel?) och i OB4 rutan blir det 00:00

 

Kommer inte på nån lösning på det där. Jag tycker att allt det här med "större än","mindre än" är en djungel :)

 

Jag har ju fått OB1 tiderna och OB2 tiderna att fungera bra nu. Enda kruxet är väll att om jag kopierar formlerna rakt neråt i kolumnerna så står det 18:00 i OB1 cellen och 06:00 i OB2 cellen, innan jag skrivit i någon start och stopptid för jobbet.

 

Är det enklaste att göra en villkordsstyrd formatering eller går det att lösa på annat sätt?

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Får se om E-forum vill vara med och leka nu. Det är verkligen segt.

 

Vilkordsstyrd formatering är inget bra om du skall summera data sedan. Jag glömde att kolla tomma data. Pinsamt...

 

Nedanstående exempel bygger på någorlunda mänskliga arbetstider. Klarar inte pass på >24 timmar. Du kan inte starta före midnatt och jobba till efter 18 dagen efter och förmodligen någonting till som jag har missat.

 

Antag att kolumn a-c ser ut som förut

 

___A____B_____C__

Datum_starttid_sluttid

 

Jag funderar på om man kan göra det lite enklare för sig med dolda hjälpkolumner

Vi kan börja med att lägga in 2 kolumner som lägger ihop datum och tid så att vi får det lättare att hantera midnatt. Vi lägger helt enkelt ihop datum och tid (+ en dag om sluttiden är mindre än starttiden).

 

Formeln för startid_med_datum (i D2) blir

=OM(ANTAL(A2:C2)=3;B2+A2;"")

 

och formeln för sluttid_med_datum (i E2) blir

=OM(D2<>"";OM(B2<C2;A2+C2;A2+C2+1);"")

 

OM vilkoret kollar om det finns några data. Annars retuneras en tom cell. Inte helt nödvändigt eftersom kolumn D och E skall ju döljas. Men bara för att visa principen

 

Sen gör vi 2 kolumner med start/stopptid för OB. Inklusive datum (lite onödigt. Men det är lättare än att skriva in det i varje formel)

I OB_start (F2) skriver vi

=A2+TIDVÄRDE("18:")

Och I OB_slut (G2)

=A2+TIDVÄRDE("06:")

 

Sen gör vi en snurra för att lista ut när helgen startar/slutar också (obs! använder "workday()" som finns i analysis toolpack. Kolla att det är ikryssat i "Verktyg->tillägg")

 

Helg_start (H2)

=OM(ELLER(VECKODAG(E2;2)>5;VECKODAG(D2;2)>5);Workday(E2;-1)+1;"")

 

Helg_slut (I2)

=OM(H2<>"";Workday(D2;1);"")

 

Ok. Då vet vi det mesta vi behöver men vi lägger till två hjälkolumner till med total arbetstid respektive total helgtid. Det är bra att ha:

Total_tid (J2)

=OM(ANTAL(A2:C2)=3;(E2-D2)*24;0)

 

Total_helgtid (K2)

=OM(ANTAL(H2:I2)=2;(MIN(I2;E2)-MAX(H2;D2))*24;0)

 

De här hjälpkolumnerna kan du dölja om du vill. Markera och gå till menyn "Format"->kolumn->dölj

 

Ok. Nu kommer vi äntligen till de kolumner du egentligen ville ha.

__l___M___N__O

Ob1_Ob2_ob3_ob4

 

Ob1 (L2)

=OM(J2<>0;J2-M2-N2-O2;0)

Ob2 (M2)

=OM(J2<>0;((D2<G2)*(MIN(E2;G2)-MIN(D2;F2))+(E2>F2)*(MIN(E2;G2+1)-MAX(F2;D2)))*24-O2;0)

Ob3 (N2)

=OM(K2<>0;K2-O2;0)

Ob4 (O2)

=OM(K2<>0;((D2<G2)*(D2>H2)*(MIN(E2;G2)-D2)+(E2>F2)*(MIN(E2;I2;G2+1)-MAX(F2;D2;H2)))*24;0)

 

Jag rekomenderar att du testar en massa olika varianter och ser att det blir rätt. Det här käns ärligt talat lite rörigt...

/m

 

[inlägget ändrat 2008-06-27 15:20:27 av MH2]

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Har lite problem med sista OB4 formeln. Blir ett utropstecken där..

Antar att det är det här som är fel:

 

(E2>F2)

 

Vad ska det stå istället?

 

 

Edit:

 

Fick ordning på det där.

Men nu är problemet med workdayformeln i H2 och I2. Det står #NAMN? i båda cellerna när jag ställer datumet i A2 på 2008-07-19 (lördag)

 

Jag har bockat för Analysis ToolPak under "Tillägg"

 

Använder mig utav Office 2007...

 

Har du någon bra lösning? =)

[inlägget ändrat 2008-06-29 16:52:53 av freddejonsson]

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Hej,

 

Jag har inte satt mig in i den redan angivna lösningen, men eftersom jag har stött på liknande problem ett antal gånger tyckte jag det var dags att (åtminstone för mig själv) reda ut det hela och hitta ett generellt angreppssätt som kan anpassas på denna typ av problem. Intresserade kan ju fortsätta läsa ...

 

Grundproblemet är att bestämma hur mycket av en angiven arbetstid som faller inom en viss tidsperiod. Dvs, att mäta överlappet av två tidsintervall. Detta går att beräkna med om-satser som tar hänsyn till diverse möjligheter, men kan också uttryckas med matematiska formler.

 

Låt A och B vara start- och sluttid för en definierad tidsperiod och (a,B) motsvarande för en arbetstid. Om A<B och a<b kan intervallens överlapp beräknas enligt:

=max[0,(min(B,B)-max(A,a))]

 

Detta uttryck kan lätt formuleras i XL, men jag vill kunna använda det på vektorer och då funkar inte max/min-funktionerna så bra. Lyckligtvis fås samma resultat genom uttrycket:

=(a<B)(b>A)[(b-(b>B)(b-B))-(a+(a<A)(A-a))]

 

Beväpnad med denna allmänna formel är det dags att öppna XL. (Se bifogat exempel.)

 

Eftersom XL i grunden är ett tabellverktyg definierar jag de olika arbetsperioderna i en tabell. Alla perioder är inom ett dygn. (Genom att lägga till datum går det att definiera andra sorts perioder.) Detta gör att en period som 18:00-06:00 måste delas upp i två perioder, 18:00-00:00 och 00:00-06:00. Notera att 00:00 som starttid har tidväre 0, medan 00:00 som sluttid har tidvärde 1, dvs ett dygn senare. Tabellen innehåller en kolumn för vilka dagar perioden är giltig (vardag/helg), vilken naturligtvis kan utökas med flera sorter av dagar. (v och h kan också bytas mot siffror om det underlättar.) Det går naturligtvis att lägga till fler kolumner om man så vill, t.ex. en för timersättning. För att kunna referera till tabellen i formler definierar jag den som ett namngivet område, "period".

 

Nu till inmatning och beräkning av arbetstider. Om vi tillåter arbetstider som passerar midnatt måste de delas upp i två delar, vilket görs med några väl valda om-satser i K,L,N,O-kolumnerna. Bestämning av vilken sorts dag det är (vardag/helg) i J och M kan göras på olika sätt beroende på hur inmatningen är uppbyggd. Ett sätt är att slå upp inmatningsdatumet i en tabell med alla datum och deras respektive kategori. Ett annat att använda formler, t.ex. VECKODAG och liknande. Glöm dock inte att Dag2 är datumet efter inmatningsdatumet.

 

Slutligen finalen!

För varje typ av arbetsperiod (OB1-OB4) kan nu samma formel användas för att beräkna motsvarande del av arbetstiden. Formeln är en direkt tillämpning av uttrycket ovan, fast dubblerat för att täcka både Dag1 och Dag2.

Cell P4:

=PRODUKTSUMMA((P$3=INDEX(period;0;1))*($J4=INDEX(period;0;2))*($K4<=INDEX(period;0;4))*($L4>=INDEX(period;0;3))*
(($L4-($L4>INDEX(period;0;4))*($L4-INDEX(period;0;4)))-($K4+($K4<INDEX(period;0;3))*(INDEX(period;0;3)-$K4))))
+PRODUKTSUMMA((P$3=INDEX(period;0;1))*($M4=INDEX(period;0;2))*($N4<=INDEX(period;0;4))*($O4>=INDEX(period;0;3))*
(($O4-($O4>INDEX(period;0;4))*($O4-INDEX(period;0;4)))-($N4+($N4<INDEX(period;0;3))*(INDEX(period;0;3)-$N4))))

De två första produkterna i varje PRODUKTSUMMA sorterar fram rätt arbetsperiod och rätt sorts dag i "period". Sedan kommer uträkningen av överlappande tid. För att ange kolumnerna i tabellen används INDEX(period;0;nr) som ger alla rader i kolumn nr i "period".

 

mvh

/Johan

 

[bild bifogad 2008-06-29 23:58:09 av Pejo]

1061894_thumb.jpg

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Ursäkta sent svar. Har inte kollat datorn på ett tag.

Jag har ingen aning. Ingen aning. " #NAMN?" tyder ju på att den inte känner igen Workdays överhuvudtaget (den delen av formeln körs ju bara när det är en helgdag).

 

Hmmm. Gooogle, google google.

 

Ok. I office 2007 har "Workday" tydligen blivit en "riktig" funktion. inte ett tillägg. Du kanske måste nog använda en svensk formel istället.

 

http://office.microsoft.com/sv-se/excel/HP100791861053.aspx

Jupp, den heter ARBETSDAGAR

(he, allt går igen. Toolkit-formeln hette ARBETSDAGAR i min gamla office 2000 också)

 

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Jadu, det där provade jag mig fram med också, men då blev det felmedelande under OB1,OB2,OB3,OB4 istället :)

Plus att den inte kände av att det var helg verkade det som...

Knepigt värre det här.

 

Jag kan ju bifoga en länk till dokumentet så om du vill och har lust kanske kan se om jag klantat till det. Men jag har kopierat av det du skrev, så det borde inte vara något fel..

 

Jag har precis börjat att lära mig Excel så jag har inte så många bra lösningar själv att komma med tyvärr..

 

https://secure.storegate.se/user/share.aspx?id=a4141e86-e3fd-4dcc-a1df-047e71f5aa39

 

 

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Det enda jag kan se direkt är att Eforum har ändrat ett större än tecken, >, till "HTML-kod" & g t ; (greater than)

 

I O2 står det (E2 & g t ; F2) där det skall vara (E2>F2)

Det blev förmodligen när jag var inne och redigerade inlägget.

 

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

Testar att redigera och se om det ställer till något

 

[inlägget ändrat 2008-06-30 13:53:17 av MH2]

 

Jo. Fast åt fel håll. Det ändrade & g t ; till >

 

PS. Ändra visningsformatet för hjälpcellerna så att du ser både datum och tid. Då blir det lättare att se vad som händer. Typ:

2008-07-16 23:00

 

Får se om jag orkar ta mig in i Pejos lösning lite senare. Det ser ju intressant ut.

[inlägget ändrat 2008-06-30 13:57:54 av MH2]

[inlägget ändrat 2008-06-30 13:58:59 av MH2]

 

Vad tusan, & g t ; skall vara sammanskrivet

[inlägget ändrat 2008-06-30 14:01:07 av MH2]

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Såg en annan sak. Du har bara ändrat WORKDAY-formeln för rad 5 och 6. Du måste nog kolla att du inte har olika formler på olika rader.

 

[inlägget ändrat 2008-06-30 14:13:12 av MH2]

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Hej igen.

Har varit borta ett par dagar och inte hunnit excela på ett tag.

Men nu har jag provat att ändra det där lilla felet med > och nu fungerar det perfekt. Tack så mycket för hjälpen, jag måste säga att jag är mycket imponerad av ditt kunnande!

Tack även till Pejo. Har inte riktigt satt mig in i den där formeln, men jag är övertygad om att den fungerar också :)

 

/Fredde

 

Dela detta inlägg


Länk till inlägg
Dela på andra webbplatser

Skapa ett konto eller logga in för att kommentera

Du måste vara medlem för att kunna kommentera

Skapa ett konto

Skapa ett nytt konto på vårt forum. Det är lätt!

Registrera ett nytt konto

Logga in

Redan medlem? Logga in här.

Logga in nu



×
×
  • Skapa nytt...