Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


29. Februar in Berechnung einbeziehen
#1
Hallo Zusammen,
ich benötige bitte mal Eure Hilfe bei einer Berechnung.
Ich habe mir einen Dienstplan erstellt, der soweit auch funktioniert.
Allerdings rechnet er im Februar nur bis zum 28. 02 22 
Wie muss ich meine Formel verändern, damit der automatisch auch den 29.02
in die Berechnungen einbezieht?

Februar

ACADAEAFAGAHAIAJAK
19. KW
2FrSaSoMoArbeits-
Stunden
Sonntags
stunden
Feiertags-
stunden
326272829
422278
5244
62222266
76
8202
92222264
Formeln der Tabelle
ZelleFormel
AC1=WENN(ISTFEHLER(FINDEN(AC3;Feiertagsliste));WENN(WOCHENTAG(AC3;2)=1;KÜRZEN((AC3-WOCHENTAG(AC3;2)-DATUM(JAHR(AC3+4-WOCHENTAG(AC3;2));1;-10))/7); "" );WVERWEIS(AC3;Berechnen_der_Feiertage;2;WAHR))
AD1=WENN(ISTFEHLER(FINDEN(AD3;Feiertagsliste));WENN(WOCHENTAG(AD3;2)=1;KÜRZEN((AD3-WOCHENTAG(AD3;2)-DATUM(JAHR(AD3+4-WOCHENTAG(AD3;2));1;-10))/7); "" );WVERWEIS(AD3;Berechnen_der_Feiertage;2;WAHR))
AE1=WENN(ISTFEHLER(FINDEN(AE3;Feiertagsliste));WENN(WOCHENTAG(AE3;2)=1;KÜRZEN((AE3-WOCHENTAG(AE3;2)-DATUM(JAHR(AE3+4-WOCHENTAG(AE3;2));1;-10))/7); "" );WVERWEIS(AE3;Berechnen_der_Feiertage;2;WAHR))
AF1=WENN(AF3="";"";WENN(ISTFEHLER(FINDEN(AF3;Feiertagsliste));WENN(WOCHENTAG(AF3;2)=1;KÜRZEN((AF3-WOCHENTAG(AF3;2)-DATUM(JAHR(AF3+4-WOCHENTAG(AF3;2));1;-10))/7); "" );WVERWEIS(AF3;Berechnen_der_Feiertage;2;WAHR)))
AG1=WENN(AG3="";"";WENN(ISTFEHLER(FINDEN(AG3;Feiertagsliste));WENN(WOCHENTAG(AG3;2)=1;KÜRZEN((AG3-WOCHENTAG(AG3;2)-DATUM(JAHR(AG3+4-WOCHENTAG(AG3;2));1;-10))/7); "" );WVERWEIS(AG3;Berechnen_der_Feiertage;2;WAHR)))
AH1=WENN(AH3="";"";WENN(ISTFEHLER(FINDEN(AH3;Feiertagsliste));WENN(WOCHENTAG(AH3;2)=1;KÜRZEN((AH3-WOCHENTAG(AH3;2)-DATUM(JAHR(AH3+4-WOCHENTAG(AH3;2));1;-10))/7); "" );WVERWEIS(AH3;Berechnen_der_Feiertage;2;WAHR)))
AC2=AC3
AD2=AD3
AE2=AE3
AF2=AF3
AG2=AG3
AH2=AH3
AC3=AB3+1
AD3=AC3+1
AE3=AD3+1
AF3=WENN(AE3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AE3=0;0;AE3+1))
AG3=WENN(AF3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AF3=0;0;AF3+1))
AH3=WENN(AG3>=DATUM(JAHR($B$3);MONAT($B$3)+1;TAG($B$3))-1;"";WENN(AG3=0;0;AG3+1))
AI4=SUMME(D4:AE4)-AJ4
AJ4=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D4:AE4)
AK4=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D4:$AH4)
AI5=SUMME(D5:AE5)-AJ5
AJ5=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D5:AE5)
AK5=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D5:$AH5)
AI6=SUMME(D6:AE6)-AJ6
AJ6=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D6:AE6)
AK6=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D6:$AH6)
AI7=SUMME(D7:AE7)-AJ7
AJ7=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D7:AE7)
AK7=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D7:$AH7)
AI8=SUMME(D8:AE8)-AJ8
AJ8=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D8:AE8)
AK8=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D8:$AH8)
AI9=SUMME(D9:AE9)-AJ9
AJ9=SUMMENPRODUKT((REST($D$2:$AE$2;7)=1)*1;D9:AE9)
AK9=SUMMENPRODUKT(($D$47:$AH$47=1)*1;$D9:$AH9)
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
AC21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AD21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AE21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AF21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AG21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AH21. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AC31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AD31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AE31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AF31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AG31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AH31. / Formel ist = WOCHENTAG(D2;2) > 5Abc
AC41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH41. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AC51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH51. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AC61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH61. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AC71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH71. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AC81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH81. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AC91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AD91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AE91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AF91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AG91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc
AH91. / Formel ist =ODER(WOCHENTAG(D$3;2)=7;WOCHENTAG(D$3;2)=6)Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Schon jetzt vielen Dank für Eure Hilfe.
freundlich grüßt
R@1ner

Excel 2013
to top
#2
Hallo,

Bei Excel 2013 gibt es einige Funktionen, die Dir das Leben erleichtern: z.B. Kalenderwoche oder Wennfehler.
Wochentag kann man ersetzen durch Rest, wobei das Oder in der bed. Form. Unnötig ist, man kann mit >5 beide Werte erschlagen.

Wo soll denn der 29.2. mitberechnet werden????
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#3
(20.04.2015, 10:39)BoskoBiati schrieb: Hallo,

Bei Excel 2013 gibt es einige Funktionen, die Dir das Leben erleichtern: z.B. Kalenderwoche oder Wennfehler.
Wochentag kann man ersetzen durch Rest, wobei das Oder in der bed. Form. Unnötig ist, man kann mit >5 beide Werte erschlagen.

Wo soll denn der 29.2. mitberechnet werden????

Hallo Edgar,
danke für deine rasche Antwort. Bin nicht so der Profi. Habe mir den Dienstplan "zusammengebastelt"
In den Spalten AI und AJ muss der 29 mitgerechnet werden.
freundlich grüßt
R@1ner

Excel 2013
to top
#4
Hallo,

Arbeitsblatt mit dem Namen 'Tabelle2'
 YZAAABACADAEAFAGAH
18. KW      9. KW  
2MoDiMiDoFrSaSoMo  
322.02.201623.02.201624.02.201625.02.201626.02.201627.02.201628.02.201629.02.2016  

NameBezug
FT=Tabelle2!$AA$21:$AA$31

ZelleFormel
Y1=WENN((ZÄHLENWENN(FT;Y3)=0)*(REST(Y3;7)=2);KALENDERWOCHE(Y3;21);"")
Y2=WENN(Y3="";"";Y3)
Y3=WENN($D$3+SPALTE(U1)>MONATSENDE($D3;0);"";$D$3+SPALTE(U1))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


in AI 4:


Code:
=SUMME(D4:AH4)-AJ4

In AJ4:


Code:
=SUMMENPRODUKT((REST($D$2:$AH$2;7)=1)*1;D4:AH4)
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#5
(20.04.2015, 10:56)BoskoBiati schrieb: Hallo,

Arbeitsblatt mit dem Namen 'Tabelle2'
 YZAAABACADAEAFAGAH
18. KW      9. KW  
2MoDiMiDoFrSaSoMo  
322.02.201623.02.201624.02.201625.02.201626.02.201627.02.201628.02.201629.02.2016  

NameBezug
FT=Tabelle2!$AA$21:$AA$31

ZelleFormel
Y1=WENN((ZÄHLENWENN(FT;Y3)=0)*(REST(Y3;7)=2);KALENDERWOCHE(Y3;21);"")
Y2=WENN(Y3="";"";Y3)
Y3=WENN($D$3+SPALTE(U1)>MONATSENDE($D3;0);"";$D$3+SPALTE(U1))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


in AI 4:





Code:
=SUMME(D4:AH4)-AJ4

In AJ4:





Code:
=SUMMENPRODUKT((REST($D$2:$AH$2;7)=1)*1;D4:AH4)
Hallo Edgar, Danke für deine Hilfe.
Der einfachheit habe ich die Datei mal hochgeladen.:
Zu den geteilten Dateien "Freigegebene D"
Ist bei der Telekom im Mediencenter.

Bestimmt ist es so einfacher mein Problem zu verstehen.
Die Spalten AI und AJ sollten halt drei Jahre lang bis AE und im 4. Jahr (Schaltjahr) bis AF
rechnen.
Hoffentlich war das so verständlicher.
Danke
freundlich grüßt
R@1ner

Excel 2013
to top
#6
Hallo R@iner, (sei nicht Böse..., aber) nutze doch bitte die Möglichkeit Dateien hier direkt im Forum hochzuladen...
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#7
Hallo, diese Anfrage kommt mir doch seltsam bekannt vor... ... das hatten wir doch schon, oder?
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#8
(20.04.2015, 12:03)freiminute schrieb: Bestimmt ist es so einfacher mein Problem zu verstehen.
Die Spalten AI und AJ sollten halt drei Jahre lang bis AE und im 4. Jahr (Schaltjahr) bis AF
rechnen.
Hoffentlich war das so verständlicher.
Danke

Hallo, ich denke das ginge so..:

=SUMMENPRODUKT((REST($D$2:INDEX(A$2:$AH2;VERGLEICH(0;A3:AH3;-1));7)=1)*1;$D$4:INDEX(A$4:$AH4;VERGLEICH(0;A3:AH3;-1)))
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#9
Hallo,

ist doch so einfach zu lösen:



Arbeitsblatt mit dem Namen 'Februar'
 BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1 5.KW      6.KW      7.KW      8.KW      9.KW   
22016MoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMo  Arbeits-

Stunden
Sonntags
stunden
3Februar0102030405060708091011121314151617181920212223242526272829  
4   2222222    12 2222222   2 2   278

NameBezug
Feiertage=Berechnung!$A$7:$R$7

ZelleFormel
B3=--("1."&AP1&"."&B2)
D1=WENN(D3<>"";WENN((ZÄHLENWENN(Feiertage;D3)=0)*(REST(D3;7)=2);KALENDERWOCHE(D3;21)&".KW";WENNFEHLER("   "&WVERWEIS(D3;Berechnung!$7:$8;2;0);""));"")
D2=D3
D3=WENN($B$3+SPALTE(A1)-1>MONATSENDE($B3;0);0;$B$3+SPALTE(A1)-1)
AI4=SUMME(D4:AH4)-AJ4
AJ4=SUMMENPRODUKT((REST($D$2:$AH$2;7)=1)*1;D4:AH4)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#10
Hi Edgar, sicher, wenn man die dritte Zeile so abändert...  21

Dafür geht B3 einfacher... =--(AP1&-B2)
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
Lightbulb 29. Februar Ausblenden oder Markieren? ChristianS 15 2.072 19.11.2015, 11:56
Letzter Beitrag: Rabe
  Wenn 29.Februar dann =D34 sonst =D33 nobody 20 5.495 15.08.2014, 14:25
Letzter Beitrag: Rabe

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste