Excel-Formel: Finden Sie heraus, wie viel Zeit in bestimmten Zeiträumen verbracht wurde

413
SupaMonkey

Versuchen, hier ein etwas komplexeres Überstunden-Arbeitsblatt herauszufinden:

Mitarbeiter wird bezahlt:

  • 1x Stundensatz an Wochentagen (Mo-Fr) von 08:30 bis 17:30 Uhr
  • 1,5x Stundensatz an Wochentagen (Mo-Fr) vor 08:30 Uhr oder nach 17:30 Uhr
  • Samstags 1,5x Stundensatz
  • An Sonn- und Feiertagen 2x Stundensatz

Blattlayout:

|Date |Day |Slip No |Name |Destination |Start Time |End Time |Total Hours Worked |Basic Hours |OT @ 1.5 |OT @ 2.0

Also suche ich nach Formeln für Kolumnen

  • [H] = geleistete Arbeitsstunden
  • [I] = Grundstunden
  • [J] = OT @ 1,5
  • [K] = OT @ 2,0

Ich suche nur nach einer hh: mm-Darstellung von dem, was gearbeitet wurde; Es ist also kein "Stundensatz" -Feld erforderlich.

[H] =MOD(G6-F6,1) [I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2)) [J] =H6-I6 [K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)

[H] = arbeiten;

[K] = arbeiten;

[I] / [J] sind das Problem und ich habe das Gefühl, wenn ich mich einfach zum Laufen bringen könnte, werde ich auch die Lösung für J haben. J ist nur "Gesamtstunden" minus "Grundstunden".

tblPublicHolidays ist eine Liste der Feiertage, die wir haben.

Daten! E2 = Startzeit - dh: 08:30 Uhr

Daten! F2 = Endzeit - dh: 17:30 Uhr

Für diese Demo hat diese erste Zeile (6) eine Startzeit von 18:30 und eine Endzeit von 18:45. Ich habe die [I] -Formel von https://exceljet.net/formula/total-hours-that-fall- zwischenween- times erhalten, aber aus folgenden Gründen scheint das nicht zu funktionieren:

  • [I] erscheint als eine Reihe von Hashwerten (#), wenn ich das Format als 'Time' habe
  • Wenn ich [I] in das Zahlenformat umwandle, wird es als -0.04 angezeigt
  • [J] scheint immer 1 Stunde mehr zu haben als erwartet (in diesem Beispiel 1:15 statt 0:15)
0
Sie können #s erhalten, wenn der Inhalt zu breit ist, um in der Spaltenbreite angezeigt zu werden. Was sehen Sie, wenn Sie die Spaltenbreite anpassen? fixer1234 vor 5 Jahren 0
Entschuldigung, ich habe es nicht erwähnt. Ich habe die Kolumne skaliert - tut nichts. Nur wenn Sie das Format in "number" ändern, erhält ich die erwähnte -0.04. SupaMonkey vor 5 Jahren 0
Sollte es möglich sein, eine negative Zeit in [I] zu erhalten (möglicherweise im Zusammenhang damit, was Ihnen den 1-Stunden-Fehler in [J] gibt)? -.04 entspricht ungefähr - 1 Stunde (dh 0,04 eines Tages). Wenn Sie kein Ergebnis mit negativer Zeit produzieren sollten, kann dies ein Hinweis sein. Wenn eine negative Zeit zulässig ist, besteht der Grund dafür, dass Sie # erhalten, wahrscheinlich, dass Sie unter Windows arbeiten und die "1900-Basis" für Datum / Uhrzeit verwenden, was negative Zeiten nicht verarbeiten kann. Die Mac-Version von Excel verwendet standardmäßig die "1904-Basis", die negative Zeiten verarbeiten kann, und Sie können dies in der Windows-Version auswählen. fixer1234 vor 5 Jahren 0
Also: `Data! E2 = 8: 30` &` Data! F2 = 17: 30` [link] (https://uploads.disquscdn.com/images/e7643136925984892b7e6ae61bdb03826a2a52ef97f8e340d082fc74e86e740c.jpg) MIN : 30 in der ersten Instanz `` MAX (F6, niedriger) = 18:30 in der ersten Instanz` Die letzten beiden Beispiele, bei denen die 'Startzeit' / 'Sammlungszeit' vor der 'Niedrigeren' liegt - es funktioniert gut Die erste zwei Beispiele, bei denen die 'Startzeit' / 'Erfassungszeit' nach der 'Oberen' liegt - funktioniert nicht. SupaMonkey vor 5 Jahren 0
Klingt sehr ähnlich zu [this] (https://stackoverflow.com/questions/51721083/calculate-the-weighted-minutes-between-two-dates-in-excel/51763302) .. Bitte ziehen Sie in Betracht, Beispieldaten hinzuzufügen (Sie haben Füge deine Formeln ein, was gut ist .. aber es gibt keine Möglichkeit für andere, es zu testen ..) p._phidot_ vor 5 Jahren 0

1 Antwort auf die Frage

0
SupaMonkey

Danke für die Hilfe, aber das ist es, was ich selbst gelandet habe (angenommen, Startreihe ist 6):

A6 (DATE) B6 (DAY) =IF(A6<>"",TEXT(A6,"dddd"),"") F6 (START TIME) I6 (END TIME) L6 (HOURS WORKED) =IF(AND(F6>0,I6>0),MOD(I6-F6,1)*24,"") M6 (BASIC HOURS) =IF(AND(F6>0,I6>0),IF(AND(WEEKDAY($A6)>1,WEEKDAY($A6)<7),IF(F6 < I6,MAX(0,MIN(I6,upper)-MAX(F6,lower))*24,MAX(0,upper - F6)+MAX(0,I6 - lower)*24),0),"") N6 (Overtime @ 1.5) =IF(AND($F6>0,$I6>0),IF(AND(WEEKDAY($A6)>1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=TRUE),(L6-M6),0),"") O6 (Overtime @ 2.0) =IF(AND($F6>0,$I6>0),IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$L6,0),"")

Verwandte Probleme