Excel: Zachowanie formatowania przy wpisywaniu godzin w dni wolne i zliczanie nadgodzin

Witam mam grafik z ewidencją czasu pracy, dni ustawowo wolne (niedziele, święta itp.) są oznaczone kolorem czerwonym i x w komórce, formuła która to formatuje wygląda tak: komórka AV5 jest sformatowana właśnie na kolor czerwony i wstawiony x. Od pewnego czasu muszę pracować w niektóre niedziele, i po wpisaniu godzin w komórkę oznaczoną jako wolny dzień tracę w niej formatowanie :/, Jak zrobić żeby po wpisaniu liczby a nie x komórka taka zachowywała czerwony kolor i była nadal zliczana jako dzień wolny? Makra VBA raczej nie wchodzą w grę ponieważ arkusz jest używany na 32 i 64 bitowym systemie i miałem z nimi czasem problem. Excel 2007

Druga sprawa, mój dzień pracy to 12 godzin, czy jest możliwość zliczenia nadgodzin? To znaczy jeśli w komórkach będą wartości większe niż 12 to excel obliczy mi sumę nadgodzin? Na w miesiącu mam dwa dni po 14 godzin czyli 4 godziny nadgodzin jest jakiś sposób to policzyć?

Powiem że trochę "nieodpowiedni" warunek w formatowaniu - taka trochę abstrakcja. [nie wiadomo jakie masz dane].

Zgodnie z tym co przedstawiłeś (trochę się domyślam i mogę się mylić) czerwony kolor komórki pojawia się wtedy gdy wpiszesz w niej "x". Więc co się dziwisz że jak wpiszesz godzinę to tracisz format?
A na marginesie, jeżeli jest to prawda z tym x i czerwienią, to formuła w warunku =licz.jeżeli() jest nieodpowiednia. Innymi słowy aż dziw że działa - przecież wystarczyło dać warunek ="x" proste, jasne i na pewno działa.

Ponieważ nie wpisujesz x a wpisujesz godziny tracisz format. Teraz jest pytanie (nie znam twojego arkusz) Po czym ma excel poznać że w danej komórce jest niedziela? Masz przynajmniej daty obok albo słowo Niedziela? Załącz przykład będzie prościej coś doradzić (do czego przyporządkować formatowanie warunkowe).

Co do drugiej sprawy: Da się (np za pomocą licz jeżeli() i suma.jeżeli() sumujesz wszystkie powyżej 12 godzin i odejmujesz wynik mnożenia licz.jeżeli [powyżej 12] razy 12 i masz łącznie nadgodziny)

Dziękuje za zainteresowanie się tematem. W sumie nie jestem pewny czy dobrze się wyraziłem nie jestem biegły w tej materii. Nie wiem również czy mogę udostępnić arkusz ponieważ został zakupiony ze strony: http://www.excel.edu.pl/ark1.html, ale skoro zapłaciłem to chyba stałem się jego właścicielem więc fragment załączam.
Chodzi o ostatnią komórkę „dni ustawowo wolne” nie wiem jak zmienić formule aby w niedziele które pracuje zachował formatowanie komórki. Kombinowałem ze zliczaniem kolorowych komórek korzystając z innego wątku:


Ale poległem
Co do nadgodzin to wiem jak to zrobić z jedną komórką ale wysiadam z zakresem C6:AG6 . Zamówiłem książkę http://helion.pl/ksiazki/excel-2010-pl-biblia-john-walkenbach,ex21bi.htm mam nadzieje że mi rozjaśni w głowie.

A co mi tam. Jak szaleć to szaleć z formatowaniem. Myślę, że przykład w pliku Cię zadowoli. Nie mam tak poważnego narzędzia jak Ty ale do zabawy z xlsx-ami używam OO. Mam nadzieję, że nic nie namotał (a czasem potrafi)
Ostatnio złapałem go na oszustwie z datami. Na tej samej dacie pomiędzy OO a MsO jest dwa dni. Ale do tematu. Formatowanie warunkowe polega (w tym przypadku) na zmontowaniu daty z wiersza nagłówkowego tabelki i pierwszej kolumny i wyliczenie, który to dzień tygodnia. Rok dodałem ręcznie, ale możesz go pobrać z którejkolwiek komórki arkusza. Masz dwa kolory na dni żółty sobota, czerwony niedziela i jeden na dni nienależące do miesiąca (to siwy). Jak się uprzesz to możesz dodać jeszcze kolorek na święta państwowe, kościelne, partyjne i jakie tam sobie jeszcze wymyślisz. Dopracowanie pozostawiam...
potomnym.

Przyznam że trochę mnie drażni że za takie "badziewie" (piszę o głównym pliku) ktoś żąda pieniędzy i to 30 zł. Jeszcze żeby to miało właśnie wbudowany (automatyczny) kalendarz ze świętami w tym wielkanocą, albo by liczyło nadgodziny (choć w tym układzie ktoś w ogóle tego nie przewidział by tu był jakikolwiek automat)
To jest tylko zwykłe liczydło - taka kartka papieru z sumatorem.

Z drugiej strony jeżeli ktoś chce takie rzeczy kupować to czemu nie, ale ja wstydziłbym się proponować coś takiego za pieniądze.

Szkoda że autor topiku nie odwiedził forum wcześniej. Było tysiące wątków liczących czas pracy itp.

Powracając do głównego zagadnienia wątku.
Rok jest w komórce AE2 pozwoliłem sobie zmodyfikować nieznacznie formatowanie wprowadzonego przez kolegę walka33 (o czym właściwie pisał)

Druga rzecz trzeba poza kolorami pamiętać o tym żeby wstawiać w nich "x" bo inaczej nie policzą się "Dni ustawowo wolne od pracy". (Dziwaczne podejście głównego twórcy arkusza ... ale... )

Wprowadziłem "automat" liczący nadgodziny. Trzeba tylko wpisać dla danego pracownika jaką ma dniówkę 8 czy 12 godzinną a może inną (dodałem oznaczoną komórkę przy nazwisku za nr ewidencyjnym).
Obliczenia te nie są tak na wprost bo jeżeli raz pracowałeś 14 godzin a w kolejnym dniu tylko 10 to nadgodziny ci się nie należą. (Co prawda nie wiem jak jest u ciebie ale standardowo tak z jest).
A co z nocnymi ???
Ręce opadają.

---------
Cały czas nie mogę się nadziwić... Jak bym z czymś takim (tak prostym arkuszem) wyskoczył do naszej kadrowej to by mnie wyśmiała bo nie po to ma kombajn w postaci excela by cos jeszcze samemu liczyć.

Bardzo wam dziękuje! Oto mi chodziło, to wszystko jest bardziej skomplikowane niż przepuszczałem tym bardziej że niemiałem do tej pory do czynienia z programami biurowymi (prywatnie wystarcza mi LaTeX).
Została tylko kwestia zliczania tych kolorowych komórek do ostatniej kolumny żeby nie trzeba było wykorzystywać tego nieszczęsnego X, przeglądam od paru dni internet i to szacowne forum i wszystkie rady okazały się nieskuteczne w moim przypadku, może mógłby mnie z któryś z kolegów naprowadzić pod jakim kątem mam szukać informacji?
Jeszcze raz bardzo dziękuje zaoszczędziliście mi mnóstwo czasu którego jest taki deficyt

Co żeś się "czepił" kolorowych komórek Trzeba normalnie policzyć dni wolne i tyle.
Nie wiem czy w nowszej wersji excela jest juz formuła "dni wolne" wale w starej jest dni pracujące pod "Networkdays()" i dzięki niej można łatwo policzyć ile jest wolnych
Na początku licze dni danego miesiąca (od daty końca odejmuję date początku) i odejmuje dni pracujące w danym miesiącu i mam:



Dodając jeszcze zakres stworzonej gdzieś (może być w innym arkuszu) tabelki z datami świąt w tym miejscu formuły [razem ze średnikiem]:

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;Tu_zakres)

Będziesz miał wyliczone wszystkie dni ustawowo wolne.

Ponizej przykład ale bez świąt bo nie wiem gdzie je docelowo wrzucić tabelkę.

A propos liczenia dni wolnych. Biegając po różnych źródłach i ćwicząc zabawę w arkuszach (podkreślam, tylko zabawę, bo twierdzę zawsze, że laik jestem) przypomniała mi się ulubiona funkcja Suma.Iloczynów() którą także można zaprząc do obliczenia dni roboczych w określonym przedziale czasu. Założenia są następujące:
L2 - data początkowa
L3 - data końcowa
L4:L5 - przykładowy zakres świąt
a formułka wygląda następująco:

Prawda, że prosta i miła w obsłudze? Ale czasem liczy lepiej niż wbudowana funkcja.
To było tylko jako ciekawostka. Nie ma przymusu stosowania.

Cytat:

Przyznam że trochę mnie drażni że...

Ja rzekłbym, że nie tylko trochę. Ale podaż ustanawia popyt (czy może odwrotnie?)

walek33 napisał:

...funkcja Suma.Iloczynów() którą także można zaprząc do obliczenia dni roboczych...

Sprytne i ciekawe zastosowanie i ... nie do końca rozumiem. Kurcze ... gdzie logika ... chm...

Niemiej podstawiając na sztywno dane w tym przypadku gdzie trzeba wyliczyć początek i koniec okresu (a koniec na podstawie początku) trochę się zagmatwa. Poza tym od razu trzeba by było liczyć dni wolne [ =7 i =1 i <>0] a nie robocze. Czyli:

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))=7))+SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))=1))+SUMA.ILOCZYNÓW(--(LICZ.JEŻELI(L4:L5;WIERSZ(ADR.POŚR(DATA(LEWY($AE$2;4);B6;1)&":"&EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)))<>0))



Ale uważam że bardzo INSPIRUJĄCE zastosowanie ulubionej ostatnio funkcji .
Możesz wyjaśnić (nakierować na) te "--" i dlaczego to liczy? Jak adres pośredni z daty, co do tego mają wiersze, kurcze kto na to wpadł, jak to liczy?
ŁAŁ
walek33 Nakierujesz rządnego wiedzy drugiego laika na jakieś źródła wiedzy i wyjaśnień lub uchylisz tej wiedzy z własnych zasobów?

Przepraszam ale dalej nie rozumiem

Co ma funkcja (z 2007 i wyżej) licz.warunki() do dwóch minusów (--)

Dodam że do tej pory rozumiałem funkcję suma.iloczynów() nawet z zastosowaniem jej do właśnie wielowarunkowych obliczeń.

Chodzi o co innego.

Do tej pory wiedziałem że daty są kolejnymi liczbami (1 dla 1900.01.01 i tak dalej) a teraz się okazuje (moja niczym nie uzasadniona dedukcja) że robiąc wirtualny zakres poprzez adres pośredni od daty do daty tworze jakąś "wirtualną" tabelę z wierszami na konkretne daty do których się niby mogę odwoływać (sprawdzać je) i przyjmując że to jest prawda nie rozumiem konieczności zapisu -- przed formułą. Bez tego nie liczy a jak jest potrzebne to co to oznacza?
Gdzie mogę o tym poczytać. Bo nie lubię używać czegoś tylko dlatego że ktoś tak zrobił. Wolał bym to zrozumieć (nie mówię że zrozumiem ale spróbuje).
Może to jakiś zabieg - może właśnie o jakiś wirtualnych tabelach czy o czymś tak jak funkcje tablicowe z {} [co prawda w tym przypadku te znaki się same robią]

Wszystkim dziękuje za pomoc już wszystko działa jak powinno.

zerknijcie tutaj:
http://www.mrexcel.com/forum/showthread.php?t=75397
http://www.pcreview.co.uk/forums/excel-double-negatives-past-present-and-future-t973029.html
http://www.coderscity.pl/sutra94552.html

Te minusy:

Cytat:

wymuszaja konwersje wartosci Boolean na liczbe. Tak wiec nasza wartosc z kolumny B jest mnozona przez --(True) lub --(False) czyli 1 lub 0

A coś takiego Ci pomoże?


Z góry uprzedzam, że formuła nie jest moim osobistym wymysłem.

Ale już pozbyłeś się podwójnej negacji.

Coby Ci uprościć (lub nie) przemyślenia dodam że tą formułką można liczyć (przynajmniej w Ex97) tylko do 2079-06-05 następnego dnia się wyłoży.

walek33 napisał:

tylko do 2079-06-05

Może M$ wie więcej na temat naszego końca

A poważniej też zaraz spróbuję poeksperymentować.

Trochę Cię naprowadzę. Data końca ze względu na WIERSZ() jest ograniczona przez długość arkusza.
To ona tworzy wirtualną tablicę kolejnych liczb całkowitych, które wartością równe są kolejnym datom.
A te wartości powstają dzięki takiemu adresowaniu pośredniemu. Podobno jeden obraz znaczy więcej niż tysiąc słów.

Właśnie przed chwilą na to samo "wpadłem" (poprzez to że "graniczna" data 2079-06-05 to cyfra 65536 czyli maks wierszy dla 2003 i w dół - dla 2007 i w górę może być więcej)


Czyli to "zwykła" zabawa liczbami i sprytne (a nawet bardzo sprytne) przechodzenie z "systemu" dat na liczby i odwrotnie. A nie jakieś ukryte funkcje .

Bo przecież


Więc później wyciągnięcie z tego liczby reprezentującej datę poprzez wiersz() i podstawienie jej pod jedną z funkcji czasu np dzień tygodnia(reprezentant daty w postaci liczby) to juz pikuś.

Kurcze ktoś nieźle to wymyślił.



A co do "--" czyli zaprzeczenia negacji czy dwóch negacji to nigdy nie stosowałem więc dlatego byłem zdziwiony (zawsze mnożyłem przez 1).

Marku
Zgłupiałem do reszty zrobiłem tabelkę z kalendarzem świąt w arkuszu dane, wpisuje rok 2012, a mi wyświetla 1905 Poza tym, kombinuje jak koń (głupi) pod górę z tym zakresem ale jak bym nie wstawił dodatkowego zakresu ze świętami do twojej formuły to zawsze mam błąd :/
Czy możesz mi jeszcze z tym pomóc?

Formułka musi być bez rok. Sama wartość D3.

Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3
Ale jak teraz wstawić ten zakres do tej formuły?


Dodano po 4 [minuty]:

steb0 napisał:

Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3
Ale jak teraz wstawić ten zakres do tej formuły?

Kod: text Rozwiń Zaznacz wszystko Kopiuj do schowka

=(EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1)-DATA(LEWY($AE$2;4);B6;1)+1-NETWORKDAYS((DATA(LEWY($AE$2;4);B6;1));EDATE(DATA(LEWY($AE$2;4);B6;1);1)-1;[b]Tu_zakres[/b])

edycja:
Zresztą to nie mój kalendarz pobrałem go stąd http://dobraorganizacja.pl/kalendarz-kompaktowy-2012/

Dodano po 3 [minuty]:

[quote="steb0"]Po prostu przedobrzyłem powinno być odwołanie do A1 a nie do D3
Ale jak teraz wstawić ten zakres do tej formuły?


Dodano po 4 [minuty]:

Zresztą to nie mój kalendarz pobrałem go stąd http://dobraorganizacja.pl/kalendarz-kompaktowy-2012/

Czy dla każdego miesiąca trzeba dawać inną formułę czy można zrobić 1 uniwersalną?

Dodano po 1 [minuty]:

Hym.. po każdej edycji posta cytuje sam siebie coś z moją przeglądarką czy to tak jest na tym forum

Nie cytuj siebie tylko zmieniaj - nie będzie powtarzać.

steb0 napisał:

...Zresztą to nie mój kalendarz ...

Jeżeli byś umiał sam "wpaść" na obliczenie daty wielkanocy zapewne nie pytał byś się jak co robic w excelu
A kalendarz jak kalendarz do najbliższych lat może być. Jednak tak prosta funkcja wyliczająca wielkanoc ma pare błedów np rok 2079 pokazuje 16.04 a powinna 23.04; w 2204 pokazuje 15.04 a powinna 22.04; w 2207 pokazuje 12.04 a powinna 19.04 No ale to chyba dla tego pliku za daleka przyszłość

Przecież masz napisane gdzie wprowadzić zakres ze świętami [jest pogrubione miejsce gdzie].

Tak, dla każdego miesiąca formuła jest trochę inna ale wystarczy zblokować odpowiednio adres komórki i przeciągnąć w dół.

Do wartości roku skorzystałem z daty wpisanej dla pracownika

Docelowo uważam że w D3 arkusza dane powinna być formuła (jak już otworzysz cały skoroszyt z pracownikami):
=LEWY(lista'!E2;4)
wtedy wybierze rok z już gdzieś wpisanego (2012 r.).

Jesteś wielki dziękuje.

Już jutro powinienem dostać książkę więc mam nadzieje że nie będę już was męczył tak banalnymi pytaniami.

Myślałem że jak przeanalizuje kilka gotowych arkuszy to załapie (w mojej naiwności ) o co chodzi, "niestety" żeby coś zrobić trzeba wykuć funkcje, żeby można było to poskładać w jakąś logiczną całość. Jak przeglądam wasze wątki uświadamiam sobie jaka dzieli nas przepaść i ile czeka mnie pracy.

Dodano po 6 [minuty]:

Jeszcze jedno pytanie
Powiedzmy że mam 2 arkusze:
Jeden z rocznym rozliczeniem czasu, a jeden z miesięcznym, o ile niema problemu zrobić tak żeby roczny pobierał dane z miesięcznego, ale czy można zrobić żeby to działało w obie strony? Jeśli edytuje miesięczny aktualizuje się roczny, a kiedy wstawiam dane do rocznego, wtedy miesięczny korzysta z danych rocznego.