Extragere ore suplimentare

[iang]

Bună ziua!
Aş dori să extrag din tabel orele suplimentare pentru zilele de sâmbăta si duminica si cele din cursul săptămânii (acestea doar care depăşesc 8 ore) pentru fiecare persoană.
De reţinut că ordinea zilelor se modifică de la o lună la alta.
Am încercat eu ceva....dar când se schimbă luna apar probleme.

[csaba1960]

Buna

In atașament ai o soluție, conține macrou.
Am modificat putin fișierul am introdus anul si luna.
La schimbarea lunii, o rutina actualizează automat zilele săptămânii.

Cod: Selectaţi tot

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Target.Address = "$B$3" Then
    For i = 3 To 33
    Cells(4, i) = WeekdayName(Weekday(DateSerial(Cells(2, 2), Cells(3, 2), Cells(3, i))), True, 1)
    Next
End If
End Sub

La apăsarea butonului se face calculul orelor suplimentare.

Cod: Selectaţi tot

Sub calculOreSupl()
Dim nRr As Integer, i As Integer, j As Integer
Dim oreS As Integer, oreW As Integer
nRr = a.Cells(a.Rows.Count, "B").End(xlUp).Row
For i = 5 To nRr
oreW = 0: oreS = 0
    For j = 3 To 33
    If IsNumeric(Cells(i, j)) = False Then GoTo et1
     If Cells(i, j) <= 8 Then GoTo et1
        If Cells(4, j) = "D" Or Cells(4, j) = "S" Then
        oreW = oreW + Cells(i, j)
        Else: oreS = oreS + Cells(i, j) - 8
        End If
et1:
    Next
Cells(i, 34) = oreS: Cells(i, 35) = oreW:
Next
End Sub

Eu am setările pentru limba romana, daca ai pentru alta limba, trebuie sa modifici rândul If Cells(4, j) = "D" Or Cells(4, j) = "S" Then.
Pentru weekend orice ora lucrata am considerat ora suplimentara.
In coloana B nu poate fi nimic sub tabel.

Realizat in Excel 2013, in principiu ar trebui sa meargă si in Excel 20003.

[iang]

Mulţumesc frumos!
Tabelul lucrează foarte bine, nu m-am aşteptat ca rezolvarea să vină în macro......
Îmi cer mii de scuze că nu am specificat de la început..... eu căutam o formulă dacă există...... ca să o pot îngloba la tabelul care pe care îl am deja!

[TudyBTH]

Buna,
Ca alternativa la solutia prezentata de dl Csaba1960, pentru eventualitatea in care doriti o solitie fara macro.

Pentru orele suplimentare din cursul saptamanii o formula CSE (se introduce cu tastele Ctrl+Shift+Enter, nu doar Enter)

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3))-8);0)*(WEEKDAY($C$4:INDEX($C$4:$AG$4;COUNT($C$3:$AG$3));2)<6)*($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3))>8))

Pentru orele din weekend , tot cu CSE

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));($C5:INDEX($C5:$AG5;;COUNT($C$3:$AG$3)));0)*(WEEKDAY($C$4:INDEX($C$4:$AG$4;COUNT($C$3:$AG$3));2)>5))

In fisierul atasat am introdus cateva formule care automatizeaza afisarea zilei din luna si a zilei din saptamana in functie de luna curenta (B2).

Tot in fisierul atasat, mai gasiti un set de formule care folosesc doua nume definite (ore si zile), utilein cazul in care doriti sa le folositi pe o alta structura a fisierului, fiind mai usor de implementat.

[iang]

Super tare! Genială ideia dv..... Mă chinui să descifrez formulele..... ......e suficient să schimb data si se aranjează totul.....perfect!
Am căutat la formatări condiţionate da nu am găsit nimic legat de colorarea zilelor de sâmbătă si duminică.
Acasă am office 2010 şi acum mă obişnuiesc cu el, la lucu am 2003.
Vă mulţumesc foarte frumos!

[TudyBTH]

Buna,

La Conditional Formatting am folosit formula

Cod: Selectaţi tot

=WEEKDAY(C$4;2)>5

Capture1.JPG

Versiunea 2003 nu accepta din cate stiu eu mai multe formatari conditionate pentru acelasi range, de aceea pentru zilele din luna 29-31 am folosit formula

Cod: Selectaţi tot

=IF(AD3="";"";IF(MONTH($B$2)=MONTH(AD4+1);AD3+1;""))

Astfel, pentru lunile mai scurte de 31 de zile va afisa null strig ("") in locul zilei din luna. Este important pentru ca asta se verifica in formulele de calcul pentru excluderea eventualelor valori introduse la aceste zile (nu le ia in considerare) si deasemenea pentru a ascunde si zilele din saptamana in aceasta zona.

Cod: Selectaţi tot

=IF(AE3="";"";DATE(YEAR($B$2);MONTH($B$2);AE$3))

cu formatarea celulelor C4:AG4 custom "ddd".
Pentru calculul orelor suplimentare am definit doua nume si am adaptat formulele pentru a fi mai usor de "mutat" in alte configuratii.
Primul nume "zile" este definit cu formula

Cod: Selectaţi tot

=OFFSET(Foaie1!$C$4;;;1;COUNT(Foaie1!$C$3:$AG$3))

A doua definitie se refra la orele inregistrate in linia in care se face calculul. De aceea foloseste o adresare reletiva pe linii si este necesar ca inainte de a defini numele sa fie selectata o celula din aceeasi linie cu cea la care se face referire (in acest caz row 5). Odata selectata celula se defineste numele "ore" cu formula

Cod: Selectaţi tot

=OFFSET(Foaie1!$C4;;;1;COUNT(Foaie1!$C$3:$AG$3))

Odata rezolvate acestea, formulele pentru ore suplimentare devin:
pentru ore supl. L-V

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER(ore);(ore-8);0)*(WEEKDAY(zile;2)<6)*(ore>8))

iar pentru weekend

Cod: Selectaţi tot

=SUMPRODUCT(IF(ISNUMBER(ore);(ore);0)*(WEEKDAY(zile;2)>5))

[iang]

Mulţumesc foarte frumos şi pentru explicaţii.
Multă sănătate şi tot cei mai bun pentru dv. şi pentru cei dragi!