Microsoft Excel

Herbers Excel/VBA-Archiv

Tage Zwischen KW | Herbers Excel-Forum


Betrifft: Tage Zwischen KW von: marco
Geschrieben am: 06.02.2012 22:55:20

Guten Abend zusammen,

ich steh bei Excel vor einen Problem mit Datum und KW und hoffe hier wird mir geholfen.

ich hab ne Liste:

Anfang: Ende:
01.01.12 31.01.12

Nun hab ich 52 KW Spalten und such ne Formel die mir in:
KW52 KW1 KW2
2 7 7
die Tage zum KW Ende ausrechnet. Also den Zeitraum mit Tagen auf KW aufteilt. Ist das möglich bestenfalls ohne VBA.

Danke

Gruß
Marco

  

Betrifft: AW: Tage Zwischen KW von: Josef Ehrensberger
Geschrieben am: 06.02.2012 23:21:43


Hallo Marco,

Tabelle1

 ABCDEFGHIJKLMN
1Anfang:Ende:            
201.01.201231.01.2012            
3   KW 49KW 50KW 51KW 52KW 01KW 02KW 03KW 04KW 05KW 06 
4   0001777720 
5              
6              
7              

Formeln der Tabelle
ZelleFormel
D4=SUMMENPRODUKT((KÜRZEN((ZEILE(INDIREKT($A$2&":"&$B$2))-DATUM(JAHR(ZEILE(INDIREKT($A$2&":"&$B$2))+3-REST(ZEILE(INDIREKT($A$2&":"&$B$2))-2;7)); 1;REST(ZEILE(INDIREKT($A$2&":"&$B$2))-2;7)-9))/7)=D3)*1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4






« Gruß Sepp »



  

Betrifft: Alternative mit Jahr von: Josef Ehrensberger
Geschrieben am: 07.02.2012 00:54:01


Hallo Marco,

Tabelle1

 ABCDEFGHIJKLM
1Anfang:Ende:           
201.01.201231.01.2012 2011201120112011201220122012201220122012
3   KW 49KW 50KW 51KW 52KW 01KW 02KW 03KW 04KW 05KW 06
4   0001777720

Formeln der Tabelle
ZelleFormel
D4=SUMMENPRODUKT((ZEILE(INDIREKT($A$2&":"&$B$2))>=("4.1."&D2)+D3*7-7-REST("2.1."&D2;7))*(ZEILE(INDIREKT($A$2&":"&$B$2))<=("4.1."&D2)+D3*7-7-REST("2.1."&D2;7)+6))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4






« Gruß Sepp »



  

Betrifft: mit Hlifszeile, aber einfacher von: Erich G.
Geschrieben am: 07.02.2012 00:57:01

Hi Marco,
wenn man in Hilfszeile 4 den jeweiligen Wochenbeginn als Datum berechnet, zählt es sich leichter.
In Zeile 2 habe ich - der Klarheit wegen - noch das KW-Jahr vorgegeben:

 ABCDEFGHIJK
1Anfang:Ende:         
201.01.201231.01.2012KW-Jahr20112011201220122012201220122012
3  KW5152123456
4  Datum19.12.1126.12.1102.01.1209.01.1216.01.1223.01.1230.01.1206.02.12
5  Tage01777720

Formeln der Tabelle
ZelleFormel
D4=7*DM((7&1-D2)/7+D3;)+177
D5=MAX(;MIN($B2+1;D4+7)-MAX($A2;D4))

Die Formel in Zeile 4 ist von amos/Frank Schneider:
http://www.online-excel.de/fom/fo_read.php?f=2&bzh=961&h=922&ao=1#a123x

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: und noch mal ohne Hlifszeile von: Erich G.
Geschrieben am: 07.02.2012 01:01:44

Hi,
ohne Hilfszeile gehts auch so:

 ABCDEFGHIJK
1Anfang:Ende:         
201.01.201231.01.2012KW-Jahr20112011201220122012201220122012
3  KW5152123456
4  Tage01777720

Formeln der Tabelle
ZelleFormel
D4=MAX(;MIN($B2+1;7*DM((7&1-D2)/7+D3;)+184)-MAX($A2;7*DM((7&1-D2)/7+D3;)+177))

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: und noch mal ohne Hlifszeile von: Marco
Geschrieben am: 07.02.2012 07:29:04

Guten Morgen zusammen,
Guten Morgen Erich,
erstmal vielen lieben Dank für die Hilfe zu so später Stunde.

Ich habe die Formeln mal angewendet. Komme aber bei der Summe nicht auf die Differenz als wenn ich die Daten so voneinander abziehe. Hängts vielleicht zusammen mit der Zeit?
Habe die Datei mal beigefügt.
https://www.herber.de/bbs/user/78769.xls


Danke
Gruß aus Duisburg
Marco


  

Betrifft: Bitte Prüfung prüfen von: Erich G.
Geschrieben am: 07.02.2012 08:24:27

Hi Marco,
überprüf doch mal deine Formel in H5 an einem einfachen Beispiel (1.1.12 bis 2.1.12, jeweils 0 Uhr).

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Bitte Prüfung prüfen von: Marco
Geschrieben am: 07.02.2012 09:13:39

Hallo Erich,

ich steh voll auf der Leitung, selbst DATDIF sagt in dem einfachen Beispiel 1 Tag.
Wo ist mein Denkfehler?

Gruß
Marco


  

Betrifft: Sorry - mein Fehler - melde mich später (owT) von: Erich G.
Geschrieben am: 07.02.2012 09:15:23




  

Betrifft: mit Uhrzeit anders von: Erich G.
Geschrieben am: 07.02.2012 10:54:42

Hi Marco,
wenn man eine Uhrzeit mit angibt, wird üblicherweise der bis-Termin anders interpretiert und muss daher
anders in die Berechnung einfließen.

 ABCDEF
2  20112012  
3  521  
4BeginnEnde26.12.201102.01.2012SummePrüfung
501.01.2012 07:0002.01.2012 10:090,7083333330,4229166671,131251,13125
601.01.2012 10:0902.01.2012 07:000,5770833330,2916666670,868750,86875
701.01.2012 00:0002.01.2012 00:001011
801.01.201202.01.20121122

Formeln der Tabelle
ZelleFormel
C4=7*DM((7&1-C2)/7+C3;)+177
C5=MAX(;MIN($B5;C$4+7)-MAX($A5;C$4))
E5=SUMME(C5:D5)
F5=B5-A5
C6=MAX(;MIN($B6;C$4+7)-MAX($A6;C$4))
C7=MAX(;MIN($B7;C$4+7)-MAX($A7;C$4))
C8=MAX(;MIN($B8+1;C$4+7)-MAX($A8;C$4))
F8=B8-A8+1

In Zeile 8 ist keine Uhrzeit angegeben. Dann wird üblicherweise davon ausgegangen, dass der bis-Tag voll zählt.
Daher steht in der Formel "$B8+1" statt "$B8". Mit Uhrzeit entspräche das "bis B8 um 24 Uhr".

Zwischen Zeile 7 und Zeile 8 ist in den Spalten A:B kein zahlenmäßiger Unterschied.
Nur das Format ist verschieden - in Zeile 8 wird keine Uhrzeit angezeigt.
Und deshalb wird "bis" meist anders interpretiert und die Formel muss sich ändern (auch die Prüfformel).
Es geht immer nur darum, ob man in den Formel beim bis-Termin mit "+1" 24 Std. addiert für "bis 24 Uhr".

Fazit: Formel C7 gilt für "mit Uhrzeit", Formel C8 gilt für "ohne Uhrzeit".
Alles klar?

Noch eine Bemerkung: In deinem ersten Beitrag war von Uhrzeiten gar keine Rede...
Und noch eine: Bei DATEDIF ist oft Vorsicht geboten.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: mit Uhrzeit anders von: Marco
Geschrieben am: 07.02.2012 11:56:34

Hallo Erich,

erstmal nochmal superlieben Dank für deine Hilfe,
das war genau das was ich brauchte.

Mea Culpa mit der fehlenden Zeitangabe. Ich dachte das Datum würde reichen und die Formel wäre übertragbar mit Zeit.

Ich glaube zum Thema Excel und Datum kann man ein Buch drüber schreiben ;)

Danke
und Grüße aus Duisburg

Marco


  

Betrifft: kleine Richtigstellung von: Erich G.
Geschrieben am: 07.02.2012 12:11:57

Hi Marco,
nur der Klarheit wegen:

Das Problem "mit/ohne Uhrzeit" hat überhaupt nichts mit Excel zu tun.

Ob man den bis-Termin mit oder ohne Uhrzeit denkt/schreibt, wirkt sich auch dann aus,
wenn man die Zeitspanne ganz ohne PC (das geht! ;-) ) im Kopf oder mit einem Bleistift auf einem Blatt Papier ausrechnet.

Hier ist Excel tatsächlich völlig unschuldig...

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


Beiträge aus den Excel-Beispielen zum Thema "Tage Zwischen KW"