[Løst] Regne ut tid i Excel, trenger hjelp til formel

[krokanrullen]

Jeg vil regne ut antall timer fra en dato til en annen, hvor kun tiden mellom kl 8 og 16 fra mandag til fredag skal regnes med. Eks. antall timer fra fredag 6.5.2011 kl 09:30 til mandag 9.5.2011 kl 10:40 hvor svaret skal bli 9 timer og 10 minutt istedenfor 73 timer. Kan noen hjelpe?

[xstnc]

Jeg vil regne ut antall timer fra en dato til en annen, hvor kun tiden mellom kl 8 og 16 fra mandag til fredag skal regnes med. Eks. antall timer fra fredag 6.5.2011 kl 09:30 til mandag 9.5.2011 kl 10:40 hvor svaret skal bli 9 timer og 10 minutt istedenfor 73 timer. Kan noen hjelpe?

 

 

Edit: Uhm.. Svarte vel ikke helt på ditt spm..

Endret 11. mai 2011 av xstnc

[deaktivert443556]

kun tiden mellom kl 8 og 16 fra mandag til fredag skal regnes med. Eks. antall timer fra fredag 6.5.2011 kl 09:30 til mandag 9.5.2011 kl 10:40 hvor svaret skal bli 9 timer og 10 minutt istedenfor 73 timer.

Regner jeg helt feil her, eller skal svaret bli 11 timer og 50 minutter? Jeg tenker 6t30m på fredag og 5t20m på mandag...

[krokanrullen]

Regner jeg helt feil her, eller skal svaret bli 11 timer og 50 minutter? Jeg tenker 6t30m på fredag og 5t20m på mandag...

 

Du har nok misforstått meg litt her.

 

Det jeg skal fram til er å finne ut hvor mange arbeidstimer det er mellom tidspunkt1 og tidspunkt2. Det skal brukes til å regne ut responstid fra innmeldt skade til tekniker har sett på saken, men for å få en rettferdig oversikt kan jeg ikke ta med timer utenom arbeidstid.

 

Hvis innmeldt skade skjer mandag kl 09:00 og tekniker har utført jobben samme dag kl 15:00 blir responstid 6 timer, men hvis innmelding skjer mandag kl 12:00 og jobben utført tirsdag kl 10:00 er jo reel responstid 6 timer her også, mens excel regner dette til å bli 22 timer.

Enda verre blir det jo ved et fredag til mandag tilfelle...

[deaktivert443556]

Ja, jeg tok fra tidspunktet på andre dato frem til kl 16:00 i stedet for fra kl 08:00 og frem til 10:40.

 

Problemet er å ikke legge til lørdag og søndag i regnestykket, men jeg har en idé som jeg tror kan løse det i én stor formel. Er det forresten i det hele tatt mulig at responstiden er mer enn fem arbeidsdager? Hvis den aldri er det, da kan det bli litt lettere.

[deaktivert443556]

Jeg orket ikke å knake ut den formelen jeg tenkte på i går, så da lagde jeg heller en funksjon via VBA.

 

Function RESPONS(Fra As Range, Til As Range)
Dim Dato1, Dato2, aDager, aHeleDager, aTimer, aMinutt, i, temp, aFra, aTil
aFra = Fra
aTil = Til
If aTil < aFra Then
   temp = aTil
   aTil = aFra
   aFra = temp
End If
aTimer = 0
If aFra - Round(aFra, 0) > 0 Then
   Dato1 = Round(aFra, 0)
Else
   Dato1 = Round(aFra, 0) - 1
End If
If aTil - Round(aTil, 0) > 0 Then
   Dato2 = Round(aTil, 0)
Else
   Dato2 = Round(aTil, 0) - 1
End If
If Weekday(Dato1, vbMonday) > 5 Then
   Dato1 = Dato1 + 8 - Weekday(Dato1, vbMonday)
   aFra = Dato1 + 8 / 24
End If
If Weekday(Dato2, vbMonday) > 5 Then
   Dato2 = Dato2 - 8 + Weekday(Dato2, vbMonday)
   aTil = Dato2 + 16 / 24
End If
kl1 = aFra - Dato1
If aFra - Dato1 < 8 / 24 Then aFra = Dato1 + 8 / 24
If aFra - Dato1 > 16 / 24 Then aFra = Dato1 + 16 / 24
If aTil - Dato2 < 8 / 24 Then aFra = Dato1 + 8 / 24
If aTil - Dato2 > 16 / 24 Then aFra = Dato1 + 16 / 24

aDager = Dato2 - Dato1
If Dato2 = Dato1 Then
   aTimer = (aTil - aFra) * 24
Else
   aTimer = aTimer + 8 - ((aFra - Dato1) * 24 - 8)
   aTimer = aTimer + ((aTil - Dato2) * 24 - 8)
   If aDager > 2 Then
       For i = Dato1 + 1 To Dato2 - 1
           If Weekday(i, vbMonday) < 6 Then aTimer = aTimer + 8
       Next
   End If
End If
aTimer = Round(aTimer, 2)
If Round(aTimer, 0) > aTimer Then
   aMinutt = Round((aTimer - Round(aTimer, 0) + 1) * 60, 0)
   aTimer = Round(aTimer, 0) - 1
Else
   aMinutt = Round((aTimer - (Round(aTimer, 0))) * 60, 0)
   aTimer = Round(aTimer, 0)
End If
RESPONS = aTimer / 24 + aMinutt / 24 / 60
End Function

Så hvis du har tidspunkt for innmeldt skade i A1 og utført tidspunkt i A2, da blir formelen i Excel =RESPONS(A1;A2)

 

Edit: Rettet litt på koden

Endret 13. mai 2011 av Bradbury

[krokanrullen]

Jeg orket ikke å knake ut den formelen jeg tenkte på i går, så da lagde jeg heller en funksjon via VBA.

 

Hva gjør jeg med dette? Kan det legges inn som egendefinert funksjon i excel?

[deaktivert443556]

Ja, trykk Alt+F11. Klikk Insert i menylinjen og så Module. Lim inn hele koden og trykk Alt+F4 for å lukke vinduet og gå tilbake til Excel.

[krokanrullen]

Nå regner den riktig ja, men nå går det ikke å summere og regne ut gjennomsnitt. Summen blir 00:00:00 med mindre jeg plusser hver celle manuelt.. Hvorfor skjer dette?

[deaktivert443556]

Mmm, ja. Jeg tok visst en litt mer tekst-approach. Jeg har endret den siste linjen litt nå.

 

RESPONS = aTimer / 24 + aMinutt / 24 / 60

 

Dette gjør at du får resultatet som et tall. Så hvis responstiden er seks timer, da tilsvarer dette en kvart dag og resultatet blir "0,25". Du må derfor manuelt formatere de aktuelle cellene som klokkeslett for at de skal vises riktig. "06:00" i dette tilfellet.

[krokanrullen]

Mmm, ja. Jeg tok visst en litt mer tekst-approach. Jeg har endret den siste linjen litt nå.

 

RESPONS = aTimer / 24 + aMinutt / 24 / 60

 

Dette gjør at du får resultatet som et tall. Så hvis responstiden er seks timer, da tilsvarer dette en kvart dag og resultatet blir "0,25". Du må derfor manuelt formatere de aktuelle cellene som klokkeslett for at de skal vises riktig. "06:00" i dette tilfellet.

 

Nå fungerer alt helt som det skal. Tusen takk for hjelpen!

[Harald Staff]

Da blir vel denne mer for arkivet. Beklager sent svar, har vært noen døgn med bare iPad.

 

Gitt engelskspråklig Excel 2007 (sånt er kjekt å vite på forhånd) skal denne funke med start i A1 og slutt i B1:

 

=NETWORKDAYS(A1;B1)*TIME(8;0;0)-MAX(MOD(A1;1)-8/24;0)-16/24+MOD(B1;1)

 

Formatter sluttcella som [hh]:mm eller klokkeslettformat 37:30:55. Formelen tar pr nå ikke høyde for manglende innskrivning eller tider utenfor 9-16.

 

Edit PS: Bare for formel-alternativets skyld dette altså. Funksjonen RESPONS er i skjønneste orden ogs har allerede løst saken.

 

Beste hilsen Harald

Endret 13. mai 2011 av Harald Staff

[krokanrullen]

Da blir vel denne mer for arkivet. Beklager sent svar, har vært noen døgn med bare iPad.

 

Gitt engelskspråklig Excel 2007 (sånt er kjekt å vite på forhånd) skal denne funke med start i A1 og slutt i B1:

 

=NETWORKDAYS(A1;B1)*TIME(8;0;0)-MAX(MOD(A1;1)-8/24;0)-16/24+MOD(B1;1)

 

Formatter sluttcella som [hh]:mm eller klokkeslettformat 37:30:55. Formelen tar pr nå ikke høyde for manglende innskrivning eller tider utenfor 9-16.

 

Edit PS: Bare for formel-alternativets skyld dette altså. Funksjonen RESPONS er i skjønneste orden ogs har allerede løst saken.

 

Beste hilsen Harald

 

Norskspråklig Excel 2007 har jeg. Kunne du skrevet denne formelen om til norsk?

Idag når jeg logget meg på hadde excel deaktivert makroer og ga meg masse trøbbel, så hvis det finnes en formel jeg kan bruke vil jeg gjerne prøve

 

Start er forresten i K6 og slutt i L6, men det jeg jo forsåvidt endre selv.

Endret 16. mai 2011 av krokanrullen

[aklla]

Norskspråklig Excel 2007 har jeg. Kunne du skrevet denne formelen om til norsk?

Idag når jeg logget meg på hadde excel deaktivert makroer og ga meg masse trøbbel, så hvis det finnes en formel jeg kan bruke vil jeg gjerne prøve

 

Start er forresten i K6 og slutt i L6, men det jeg jo forsåvidt endre selv.

Noe slikt:

 

=NETT.ARBEIDSDAGER(A1;B1)*TID(8;0;0)-STØRST(REST(A1;1)-8/24;0)-16/24+REST(B1;1)

 

For å aktivere funksjonen NETWORKDAYS/NETT.ARBEIDSDAGER les her: http://support.microsoft.com/kb/259200

Denne er ikke aktiv by default...

[krokanrullen]

Norskspråklig Excel 2007 har jeg. Kunne du skrevet denne formelen om til norsk?

Idag når jeg logget meg på hadde excel deaktivert makroer og ga meg masse trøbbel, så hvis det finnes en formel jeg kan bruke vil jeg gjerne prøve

 

Start er forresten i K6 og slutt i L6, men det jeg jo forsåvidt endre selv.

Noe slikt:

 

=NETT.ARBEIDSDAGER(A1;B1)*TID(8;0;0)-STØRST(REST(A1;1)-8/24;0)-16/24+REST(B1;1)

 

For å aktivere funksjonen NETWORKDAYS/NETT.ARBEIDSDAGER les her: http://support.microsoft.com/kb/259200

Denne er ikke aktiv by default...

 

Takker! Funket glimrende den formelen.