PDA

Vollständige Version anzeigen : bedingte Formatierung für Zeitraum (Kalender)


amica libera
27.06.2015, 08:50
Hallo zusammen,
für einen Kalender möchte ich den Zeitraum für Urlaub farblich darstellen.
Dazu möchte ich die bedingte Formatierung nutzen.
Aus der Eingabe "von" und "bis" sollen die entsprechen Tage ermittelt und in einer separaten Zeile farblich gekennzeichnet werden.
Für den ersten Zeitraum (im Beispiel 4.1. bis 8.1.) habe ich das auch hinbekommen. Weiß nun aber nicht, wie ich die weiteren Zeiträume "einbauen" kann (im Beispiel 18.1. bis 22.1.).
Habe die Datei als Anhang beigefügt.

Kann mir jemand von Euch helfen?
Vielen Dank schon mal im Voraus.

Hajo_Zi
27.06.2015, 08:56
Hallo Doris,

<br/><b><em>Urlaub</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:70px;" /><col style="width:70px;" /><col style="width:70px;" /><col style="width:70px;" /><col style="width:70px;" /><col style="width:70px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Fr</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Sa</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">So</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mo</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Di</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mi</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">02. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">03. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">05. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">06. 01.</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#00B0F0 ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>D1:I1</td><td>=TEXT(D2;"ttt")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>E2:I2</td><td>=D2+1</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="11" > Bedingte Formatierung Haupttabelle 1 </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td > Wird angewendet auf &nbsp; </td><td> Nr. Bed. *Regeltyp&nbsp; </td><td> Operator &nbsp; </td><td> Formel1 &nbsp; </td><td> Formel2 &nbsp; </td><td> Format Schrift Füllfarbe &nbsp; </td><td> Unterstrichen &nbsp; </td><td> Schrift- farbe </td><td> Muster &nbsp; </td><td> Musterfarbe </td><td> Typ </td><td> Bereich </td></tr><tr><td>$D$3:$AH$1048576</td><td style="text-align:left; ">01.Bed.: Formel ist</td><td>&nbsp;</td><td style="text-align:left; ">=UND($A3&lt;=D$2;$B3&gt;=D$2)&nbsp;</td><td>&nbsp;</td><td style="background-color:#00B0F0; ;text-align:right " >15773696</td><td style="text-align:left; "> &nbsp; </td><td>&nbsp;</td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">D3:AH1048576</td></tr></table><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="5" > Bedingte Formatierung Haupttabelle 2 </td><td colspan="3" > oberere/unterer Bereich </td><td> &nbsp; </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td> Wird angewendet auf &nbsp; </td><td> Nr. Bed.&nbsp;</td><td> Format Zelle </td><td> Anhalten </td><td> Auswahl &nbsp; </td><td> Anzeige &nbsp; </td><td> Anzahl &nbsp; </td><td> Durchschnitt &nbsp; </td><td> Typ &nbsp; </td><td> Bereich </td></tr><tr><td>$D$3:$AH$1048576</td><td>01.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">D3:AH1048576</td></tr></table><b>Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.</b><br/><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 19.05 einschl. 64 Bit</td></tr></table><br/>

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

da war der Bereich zu groß für den ersten Beitrag.

amica libera
27.06.2015, 09:23
Guten Morgen Hajo,
vielen Dank für die prompte Antwort.
Leider habe ich mich missverständlich ausgedrückt.
Die farbliche Markierung soll nur in der Zeile 3 erscheinen.

Hast Du dafür evtl. eine Idee?

Hajo_Zi
27.06.2015, 09:35
Hallo Doris,

man muss die Aufgabe nicht beschreiben. Die kennen wir ja alle.
Ich habe jetzt 2 Lösungen erstellt und bin damit raus. Da ich nicht für den Papierkorb arbeite.

<br/><b><em>Urlaub</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:86px;" /><col style="width:86px;" /><col style="width:97px;" /><col style="width:70px;" /><col style="width:70px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Fr</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Sa</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">von</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">bis</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01. 01.</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">02. 01.</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04.01.2016</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">08.01.2016</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">18.01.2016</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">22.01.2016</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>D1:E1</td><td>=TEXT(D2;"ttt")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>E2</td><td>=D2+1</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="11" > Bedingte Formatierung Haupttabelle 1 </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td > Wird angewendet auf &nbsp; </td><td> Nr. Bed. *Regeltyp&nbsp; </td><td> Operator &nbsp; </td><td> Formel1 &nbsp; </td><td> Formel2 &nbsp; </td><td> Format Schrift Füllfarbe &nbsp; </td><td> Unterstrichen &nbsp; </td><td> Schrift- farbe </td><td> Muster &nbsp; </td><td> Musterfarbe </td><td> Typ </td><td> Bereich </td></tr><tr><td>$D$3:$AH$3</td><td style="text-align:left; ">01.Bed.: Formel ist</td><td>&nbsp;</td><td style="text-align:left; ">=ODER(UND($A3&lt;=D$2;$B3&gt;=D$2);UND($A4&lt;=D$2;$B4&gt;=D$2))&nbsp;</td><td>&nbsp;</td><td style="background-color:#00B0F0; ;text-align:right " >15773696</td><td style="text-align:left; "> &nbsp; </td><td>&nbsp;</td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">D3:AH3</td></tr></table><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="5" > Bedingte Formatierung Haupttabelle 2 </td><td colspan="3" > oberere/unterer Bereich </td><td> &nbsp; </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td> Wird angewendet auf &nbsp; </td><td> Nr. Bed.&nbsp;</td><td> Format Zelle </td><td> Anhalten </td><td> Auswahl &nbsp; </td><td> Anzeige &nbsp; </td><td> Anzahl &nbsp; </td><td> Durchschnitt &nbsp; </td><td> Typ &nbsp; </td><td> Bereich </td></tr><tr><td>$D$3:$AH$3</td><td>01.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">D3:AH3</td></tr></table><b>Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.</b><br/><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 19.05 einschl. 64 Bit</td></tr></table><br/>

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

amica libera
27.06.2015, 09:39
Hallo Hajo,
hat geklappt!
Vielen Dank und schönes Wochenende!

amica libera
27.06.2015, 11:22
Hallo Hajo,
habe noch mal etwas herumexperimentiert und eine Lösung gefunden, die mehrere Datumseingaben berücksichtigt.
Die Datumsbereiche habe ich mit Namen definiert ("Urlaub_von" und "Urlaub_bis").
Formel für die bedingte Formatierung:
=ODER((Urlaub_von<=D$2)*(Urlaub_bis>=D$2))

Vielleicht suchen ja auch andere danach (ähnlich verzweifelt wie ich).