PDA

Vollständige Version anzeigen : Excel 2003 - Datum des letzten Tages des Vormonats


zra8886
07.09.2011, 15:46
Hi,

ich benötige eine dynamische Formel für die Berechnung des letzten Tages des Vormonats.

Beachten werden soll aber auch noch, dass das bis Datum, falls älter als berechnetes Datum des letzten Tages des Vormonats, eingetragen wird.

Ist ein bisschen kompliziert zu beschreiben, deshalb mal ein Beispiel, wenn ich die Datei heute öffnen würde:

<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:96px;" /><col style="width:96px;" /><col style="width:106px;" /><col style="width:165px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>H</td><td>I</td><td>J</td><td>K</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " ><b>von</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " ><b>bis</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Tagesdatum</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >letzterTagVormonat</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >01.02.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.01.2012</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >07.09.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.08.2011</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >01.10.2010</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.08.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >07.09.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.08.2011</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >01.10.2010</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.07.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >07.09.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >31.07.2011</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>J3 </td><td>=HEUTE()</td><td>&nbsp;</td></tr><tr><td>J4 </td><td>=HEUTE()</td><td>&nbsp;</td></tr><tr><td>J5 </td><td>=HEUTE()</td><td>&nbsp;</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>
die Spalte Tagesdatum wird nicht benötigt, die habe ich nur zur Verdeutlichung mit aufgenommen.

Unter dynamisch stelle ich mir vor:
Wenn ich die Datei im Oktober öffne, geht es bis max. 30.09.
Wenn ich die Datei im November öffne, geht es bis max. 31.10.
usw.

Wenn was unklar ist: Bitte, bitte nachfragen.

mfg
Roland

EarlFred
07.09.2011, 15:51
Hallo Roland,

so?
=MIN(I3;DATUM(JAHR(HEUTE());MONAT(HEUTE());0))

Grüße
EarlFred

chris-kaiser
07.09.2011, 15:56
hi

sowas?

<P><blockquote><table border=3 cellpadding=1><tr><td>&nbsp;</td><td align=center bgcolor=#C0C0C0>A</td><td align=center bgcolor=#C0C0C0>B</td><td align=center bgcolor=#C0C0C0>C</td></tr><tr><td align=center bgcolor=#C0C0C0>1</td><td align=left>von</td><td align=left>bis</td><td align=left>letzterTagVormonat</td></tr><tr><td align=center bgcolor=#C0C0C0>2</td><td align=left>01.02.2011</td><td align=left>31.01.2012</td><td align=left>31.08.2011</td></tr><tr><td align=center bgcolor=#C0C0C0>3</td><td align=left>01.10.2010</td><td align=left>31.07.2011</td><td align=left>31.07.2011</td></tr></td></tr></table></blockquote></P><br><br>

<P><blockquote><table border=3 cellpadding=1><tr><td>&nbsp;</td><td align=center bgcolor=#C0C0C0>A</td><td align=center bgcolor=#C0C0C0>B</td><td align=center bgcolor=#C0C0C0>C</td></tr><tr><td align=center bgcolor=#C0C0C0>1</td><td align=left>von</td><td align=left>bis</td><td align=left>letzterTagVormonat</td></tr><tr><td align=center bgcolor=#C0C0C0>2</td><td align=left>01.02.2011</td><td align=left>31.01.2012</td><td align=left>=WENN(B2&lt;HEUTE();B2;DATUM(JAHR(HEUTE());MONAT(HEUTE());0))</td></tr><tr><td align=center bgcolor=#C0C0C0>3</td><td align=left>01.10.2010</td><td align=left>31.07.2011</td><td align=left>=WENN(B3&lt;HEUTE();B3;DATUM(JAHR(HEUTE());MONAT(HEUTE());0))</td></tr></td></tr></table></blockquote></P><br><br>

mücke
07.09.2011, 16:03
Moin Roland,

oder so?
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:132px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">von</td><td style="text-align:center; ">bis</td><td >letzterTagVormonat</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">01.02.2011</td><td style="text-align:center; ">31.01.2012</td><td style="text-align:center; ">31.08.2011</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">01.10.2010</td><td style="text-align:center; ">21.08.2011</td><td style="text-align:center; ">21.08.2011</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">01.10.2010</td><td style="text-align:center; ">31.07.2011</td><td style="text-align:center; ">31.07.2011</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >J3</td><td >=MIN(I3;HEUTE<span style=' color:008000; '>()</span>-TAG<span style=' color:008000; '>(HEUTE<span style=' color:#0000ff; '>()</span>)</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

EarlFred
07.09.2011, 16:11
@Dirk

schöne Verkürzung! Liegt nah - und doch so fern ;)

Grüße
EarlFred

chris-kaiser
07.09.2011, 16:16
Hallo Dirk

da kann ich mich EarlFred nur anschließen.
:)

hary
07.09.2011, 16:18
Hallo Allemann
So is er(Muecke), wochenlang nix zu lesen, kam/sah/siegte und verschwand. LOL
gruss hary

mücke
07.09.2011, 16:24
Ein freundliches Moin in die Runde!

Bei so netten Worten werde ich ja ganz :redface:

hary
07.09.2011, 16:31
Hallo Dirk
Dein zweiter Vorname ist doch Datum. Gib's zu ;-))
gruss hary

mücke
07.09.2011, 16:36
Moin hary,

... ich gestehe :D

zra8886
08.09.2011, 08:10
;) In der Reihenfolge der Antworten

Hallo Fred, Chris und Dirk.

OT
ihr macht mich schwach.
Anfrage um: 15:46. Erste Antwort: 15:51; die Zweite: 15:56 und die Dritte: 16:03.
:mrcool: Ihr müsst doch schon auf so eine Frage gewartet haben?

BTT
Ich weiss jetzt nicht, welche Lösung ich einsetzen werde.

Ich habe nun die Qual der Wahl:
<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:96px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>I</td><td>J</td><td>K</td><td>L</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " ><b>bis</b></td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >EarlFred</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >chris-kaiser</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >mücke</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#339966; background-color:#FFFFFF; text-align:left; " >01.02.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >01.02.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >01.02.2011</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >01.02.2011</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>J3 </td><td>=MIN(I3;DATUM(JAHR(HEUTE());MONAT(HEUTE());0))</td><td>&nbsp;</td></tr><tr><td>K3 </td><td>=WENN(I3&lt;HEUTE();I3;DATUM(JAHR(HEUTE());MONAT(HEUTE());0))</td><td>&nbsp;</td></tr><tr><td>L3 </td><td>=MIN(I3;HEUTE()-TAG(HEUTE()))</td><td>&nbsp;</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.42</a><br/><br/>
Da alle 3 Lösungen perfekt funktionieren, werde ich euch NICHT mitteilen, welche Formel zum Einsatz kommt. Ich möchte da keinen von euch benachteiligen.
Ich danke euch dreien recht herzlich. Das erleichtert mir die Arbeit gewaltig. Und das ist nicht übertrieben. Datensatzanzahl von Jan 2011 bis Jul 2011 = 10.500. D. h. am Jahresende ist mit ca. 20.000 Datensätzen zu rechnen.

Ach, da fällt mir noch was ein dazu: Die Formel müssten doch auch in Access funktionieren, oder? Werde ich auf alle Fälle mal versuchen das umzusetzen.

mfg
Roland

Erich G.
08.09.2011, 13:38
Hi zusammen,
in diesem Beitrag habe ich mal zwei Formeln für den ersten Tag des Monats verglichen:
http://www.herber.de/forum/archiv/1160to1164/t1163569.htm#1163750

Ich finde den Unterschied recht deutlich...

EarlFred
08.09.2011, 14:52
Hallo Erich,

die Formel mit Tag() ist genauso einfacher zu verstehen wie schneller: Pro Datensatz habe ich eine Ersparnis von rund 3,4*10^-7 Sekunden festgestellt.

Grüße
EarlFred

zra8886
08.09.2011, 15:05
Hallo Fred,

...Pro Datensatz habe ich eine Ersparnis von rund 3,4*10^-7 Sekunden festgestellt.

Grüße
EarlFred

geht jetzt zwar an meiner Ursprungsfrage vorbei, macht aber nichts.

:eek: Mit welcher Stoppuhr kann man das denn messen?

mfg
Roland

zra8886
01.11.2011, 13:13
...
:eek: Mit welcher Stoppuhr kann man das denn messen?

mfg
Roland

Hallo Fred,

wie kann man sowas messen?

mfg
Roland

Thomas Ramel
01.11.2011, 13:47
Grüezi Roland

Mit FastExcel beispielsweise?

EarlFred
01.11.2011, 14:43
Hallo Roland,

habe einfach eine große Menge an Berechnungen durchgeführt und die Gesamtzeit durch Anzahl der Durchläufe geteilt.

Grüße
EarlFred

zra8886
01.11.2011, 15:38
Hallo Fred,
Merci

jetzt kann ich den Beitrag beruhigt schließen.

mfg
Roland