Hoe u voorwaardelijke opmaak in Excel instelt voor datums

Voorwaardelijke opmaak in Excel kan worden beschouwd als een waardevol hulpprogramma dat de gegevensanalyse en presentatie in het spreadsheet visueel kan verbeteren door bepaalde opmaak toe te passen op basis van vooraf gedefinieerde regels of voorwaarden. In dit bericht wordt uitgelegd hoe u voorwaardelijke opmaak in Excel voor datums kunt instellen. We hebben ook gedetailleerde voorbeelden toegevoegd die u kunt proberen terwijl u de gids leest.

Hoe u voorwaardelijke opmaak in Excel instelt voor datums

Voorwaardelijke opmaak helpt niet alleen om de cellen te markeren met behulp van kleurcodes, maar maakt ook de rijen, kolommen en cellen op die uit datums bestaan, op een manier die als visueel betekenisvol en aantrekkelijk kan worden beschouwd.

1. Voorgedefinieerd
2. Gebruiker gedefinieerde
3. Maak een op maat gemaakte opmaakregel
4. Feestdagen markeren in Excel
5. Voorwaardelijke opmaak van datums op basis van meerdere voorwaarden
6. Aankomende data en vertragingen benadrukken

Zorg ervoor dat u de details in Excel zorgvuldig doorneemt. Het biedt vele opties, waarvan sommige mogelijk buiten het bestek van deze handleiding vallen.

1] Vooraf gedefinieerde datumregels voor voorwaardelijke opmaak met datums

Voorwaardelijke opmaak met datums kan nuttig zijn bij het omgaan met gegevenssets die datumgerelateerde informatie bevatten. Excel biedt bijna tien verschillende opties om cellen op te maken op basis van datums, zoals hieronder geïllustreerd:

• Om een ​​cel op te maken met de datum als gegevenstype, klikt u op Home > Voorwaardelijke opmaak > Markeer celregels >Een datum komt voor

• De voorwaardelijke opmaakopties kunnen worden gekozen uit de vervolgkeuzemenu’s in het volgende venster. In de eerste vervolgkeuzelijst kunnen we het dagbereik selecteren, en de opmaakopties kunnen uit de tweede worden gekozen.

• Klik op OK om de gekozen opmaakregels toe te passen.

2] Door de gebruiker gedefinieerde datumregels voor voorwaardelijke opmaak met datums

Naast een reeks vooraf gedefinieerde regels biedt Excel ook de mogelijkheid om deze regels aan te passen op basis van gebruikersvereisten. Er zijn meerdere manieren om de aanpassingen in te stellen, zoals hieronder geïllustreerd:

Aangepast formaat gebruiken

1. Open het venster Een datum die voorkomt door de hierboven genoemde stappen te volgen.
2. Nadat u het dagbereik heeft geselecteerd, selecteert u Aangepast formaat in het vervolgkeuzemenu met opmaakopties.
3. Het venster Cellen opmaken verschijnt vanwege het bovenstaande, waar we de kleur, rand, lettertype en effecten van onze keuze en klik vervolgens op OK om de wijzigingen toe te passen.

Maak nieuwe regels

Deze methode omvat het maken van een geheel nieuwe regel voor voorwaardelijke opmaak, in tegenstelling tot de bovenstaande methode, waarbij we kunnen kiezen uit een paar vooraf gedefinieerde regels en alleen een aangepaste opmaak kunnen maken. Het maken van een nieuwe regel omvat het volgende:

• Selecteer de cellen waarop de opmaak moet worden toegepast.
• Op het tabblad Start klikt u op Voorwaardelijke opmaak > Nieuwe regel, waardoor het venster Nieuwe opmaakregel wordt geopend.

• In het genoemde venster kunnen we kiezen uit de beschikbare Regeltypen, gevolgd door het bewerken van de regelbeschrijving om de criteria voor voorwaardelijke regels in te stellen opmaak.
• Eenmaal voltooid, kunt u op de knop Opmaak in de rechterbenedenhoek van het venster klikken om de opmaak voor de genoemde regel te kiezen. een>
• In het venster Cellen opmaken dat wordt geopend, kunnen we het lettertype, de rand, de celkleur, de effecten, enz. kiezen, volgens keuze en klik eenmaal op OK in dit venster, gevolgd door het venster Nieuwe opmaakregel waarin de wijzigingen moeten worden toegepast.

3] Hoe u een op maat gemaakte opmaakregel met datums maakt

In het bovenstaande voorbeeld hebben we de stappen doorlopen voor het maken van een nieuwe regel voor voorwaardelijke opmaak op basis van reeds opgegeven criteria. Excel maakt het ook mogelijk om de genoemde opmaakregels toe te passen op door de gebruiker gedefinieerde criteria.

Stel dat we bijvoorbeeld de cellen moeten markeren met datums die ouder zijn dan 30 dagen of 60 dagen. In dat geval kan hetzelfde ook worden geïmplementeerd door datumformules en -functies toe te passen die beschikbaar zijn in Excel, zoals hieronder geïllustreerd:

A] Markeer huidige en oudere datums

Onder Selecteer een nieuwe regel in de optie Voorwaardelijke opmaak kiest u Gebruik een formule om te bepalen welke cel die u wilt opmaken, typ:

=$H4=TODAY() om het heden te benadrukken

=$H4>TODAY() om toekomstige dagen uit te lichten

=$H4<TODAY() om de voorgaande dagen te markeren

=$H4<=(TODAY()-30) om datums te markeren die meer dan 30 dagen oud zijn.

=$H4<=(TODAY()-60) om datums te markeren die meer dan 60 dagen oud zijn.

De bovenstaande formule controleert het geselecteerde datumbereik voor de huidige datum en zodra de overeenkomst is gevonden, wijzigt u de achtergrondkleur in Paars, zoals geselecteerd in de optie Opmaak. De $ gebruikt naast H geeft aan dat de kolom constant zou blijven, maar de rijen zouden variabel zijn.

• Zodra de Regelbeschrijving is vermeld, kunnen het lettertype, de kleuren en de effecten worden gekozen door op de knop Opmaak te klikken.
• Klik op OK in het venster Cellen opmaken, gevolgd door hetzelfde in het venster Nieuwe regel, zodat de wijzigingen van kracht worden. /span>

B] Markeer op basis van een door de gebruiker gedefinieerd datumbereik

Soortgelijke methoden kunnen worden toegepast voor het opmaken van de door gebruikers opgegeven datumbereiken. Als we bijvoorbeeld de datums moeten markeren die meer dan 30 dagen oud zijn, maar minder dan 45 dagen, kan de functie TODAY() worden gebruikt in combinatie met een logische operator zoals AND om de criteria op te geven die aan twee voorwaarden voldoen:

• Selecteer de cellen waarvoor de opmaak moet worden toegepast en open het venster Nieuwe regel.
• Voer in het veld De regelbeschrijving bewerken de onderstaande formule in,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

De bovenstaande formule gebruikt de operator AND om de datums in het geel te markeren die meer dan 30 dagen oud zijn, maar minder dan 45 dagen voor de gehele selectie, beginnend bij celnr. H2.

C] Weekends benadrukken

In hetzelfde bereik kunnen met behulp van de WEEKDAY()-formule ook de datums die in het weekend vallen worden gemarkeerd:

• Selecteer de cellen waarvoor de opmaak moet worden toegepast en open het venster Nieuwe regel.
• Voer in het veld De regelbeschrijving bewerken de onderstaande formule in,

=WEEKDAY($H2, 2)>5

De syntaxis van de weekdagformule kan worden weergegeven als WEEKDAY(serienummer, [retourtype]), waarbij serienummer het celnummer van de datum die we proberen te controleren (SA1 in dit geval).

De parameter return_types vertegenwoordigt het type voor de week die begint op maandag(1) tot en met zondag(7), rekening houdend met de startdag van de week als maandag (weergegeven door 1) en de laatste dag van de week als zondag (weergegeven door 7).

Return_types zijn echter optioneel in de formule en worden weergegeven tussen derde accolades. >5 wordt gebruikt om zaterdag(6) en zondag(7) te markeren voor het opgegeven retourtype.

D] Rijen markeren op basis van een specifieke datum

Deze optie kan handig zijn bij het verwerken van grote hoeveelheden gegevens waarbij een bepaalde datum moet worden benadrukt. Hetzelfde kan worden bereikt door de numerieke waarde van de datum te bepalen of door de functie DATEVALUE() te gebruiken. De numerieke waarde moet in eerste instantie worden bepaald om voorwaardelijke opmaak voor een specifieke datum toe te passen, aangezien Excel datums permanent opslaat als opeenvolgende serienummers. Om de numerieke waarde van een specifieke datum te vinden,

• Klik met de rechtermuisknop op de cel met de datum, klik op Cellen opmaken >Getal, en kies vervolgens Algemeen. Er wordt een numerieke waarde van vijf cijfers weergegeven in de cel, die kan worden genoteerd voor toekomstig gebruik.

• Klik op Annuleren zodra het getal is genoteerd, omdat we de datumnotatie voor de cel niet hoeven te wijzigen.
• Klik op Voorwaardelijke opmaak>Nieuwe regel> Selecteer een nieuwe regel> Gebruik een formule om te bepalen welke cel moet worden opgemaakt en typ de volgende formule,

=$G5=45249

• Met de functie DATEVALUE() kan hetzelfde worden gedaan door de onderstaande formule in te voeren,

=$G5=DATEVALUE("11/19/2023")

De $ die naast C wordt gebruikt, betekent dat de kolom constant zou blijven, maar dat de rijen variabel zouden zijn.

4] Feestdagen markeren in Excel

Aangezien feestdagen kunnen variëren op basis van demografische gegevens, heeft Excel geen ingebouwde functies om feestdagen te markeren. Om de feestdagen te kunnen identificeren, moeten we de data daarom in een andere kolom registreren en de formule countif toepassen om te controleren of de vakantiedata voorkomen. in het gegevensblad.

We hebben bijvoorbeeld een paar datums vermeld in kolom D en een lijst met feestdagen in kolom G. Via voorwaardelijke opmaak kunnen we de feestdagen in ROOD in kolom D markeren, indien aanwezig, door ze te vergelijken met de vakantiedata vermeld in kolom G met behulp van de onderstaande stappen:

• Open het dialoogvenster Nieuwe regel en kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt < /span>Selecteer regeltype.onder
• Voer de formule in =COUNTIF($G$3:$G$5,$D3)
• Klik op Formatteren > Vul en selecteer de kleur ROOD uit het palet en klik op OK< /span>.

De countif() controleert of de datums vermeld in cel G3 – G5 voorkomen met de datums vermeld in kolom D, beginnend bij cel D3 .

5] Voorwaardelijke opmaak van datums op basis van meerdere voorwaarden

Laten we uitgaan van een werkblad waarin we klachten hebben over verschillende huishoudelijke artikelen. Op het blad staan ​​de data vermeld waarop de klachten zijn ingediend en de verval- en sluitingsdata, indien van toepassing.

Laten we met behulp van voorwaardelijke opmaak de klachten WIT markeren voor de klachten die GESLOTEN zijn, ROOD voor de klachten die de vervaldatum hebben overschreden maar nog niet zijn gesloten, en BLAUW voor de klachten die de vervaldatum nog hebben bereikt.

Het proces omvat het maken van twee voorwaardelijke opmaakregels voor dezelfde gegevens, één voor degenen die de vervaldatum nog niet hebben bereikt en een andere voor degenen die de vervaldatum hebben overschreden. Om dit te doen,

• Open het venster Nieuwe regel voor voorwaardelijke opmaak en selecteer Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt onder Selecteer een regeltype.
• Voer de onderstaande formule in

=AND($I10="",$H10>$K$6)

• Klik op Formatteren, selecteer de BLAUWE kleur in het palet en klik op OK.
• Maak nog een Nieuwe regel met hetzelfde regeltype en voer de onderstaande formule in

=AND($I10="",$H10<$K$6)

• Klik op Opmaak, selecteer RODE kleur in het palet en klik op OK.

In deze formule controleren we eerst of de kolom Datum gesloten leeg is of niet om vast te stellen of de klacht nog open is EN en vervolgens controleren als de VERVALDATUM groter is dan de huidige datum of korter om te bepalen of de vervaldatum nadert ($H10>$K$6) of overschreden ($ H10<$K$6). De datum in cel K6 vertegenwoordigt de huidige datum.

6] Aankomende data en vertragingen benadrukken

Om te begrijpen hoe we vertragingen met betrekking tot datums in een Excel-werkblad kunnen markeren, bekijken we een voorbeeld waarin we een lijst met aandelen hebben met hun namen en vervaldata. Als we via voorwaardelijke opmaak degenen die binnenkort verlopen moeten markeren in GROEN en degenen die al zijn verlopen in ROOD,

• Selecteer het gegevensbereik en open het dialoogvenster Nieuwe regel.
• Kies Alleen cellen opmaken die bevatten onder Regeltype selecteren
• In Regelbeschrijving bewerken kiest u Celwaarde, Groter dan, =Nu()+30 in de respectievelijke vervolgkeuzelijsten.
• Klik op Formatteren, selecteer GROEN uit het kleurenpalet, en klik op OK.

• Open opnieuw een Nieuwe regel en kies hetzelfde Regeltype, < a i=4>zoals hierboven.
• In Regelbeschrijving bewerken kiest u Celwaarde, Minder dan, =Nu() in de respectievelijke vervolgkeuzelijsten.=Nu() een>
• Klik op Formatteren en selecteer ROOD uit de kleur pallet en klik op OK.

Conclusie

Deze zelfstudie is bedoeld om uitgebreid de opties en functies te behandelen die kunnen worden gebruikt voor het toepassen van voorwaardelijke opmaak in Excel-werkbladen op basis van datums. Het kan helpen begrijpen hoe u opmaakregels kunt toepassen om de visualisatie en analyse van gegevens te verbeteren. Het beheersen van voorwaardelijke opmaak kan ook helpen trends te identificeren, deadlines te controleren en taakprioriteiten te stellen, waardoor weloverwogen beslissingen kunnen worden genomen.

Hoe maak ik voorwaardelijke opmaak in Excel op basis van datums?

Selecteer eerst uw datumcellen om voorwaardelijke opmaak in Excel te maken op basis van datums. Klik op ‘Thuis’ > ‘Voorwaardelijke opmaak’ > ‘Nieuwe regel’. Kies ‘Cellen die deze bevatten’ opmaken, selecteer ‘Datums die voorkomen’ onder de regel, kies de specifieke datumcriteria en stel vervolgens het gewenste formaat in door op ‘Formaat’ te klikken. Solliciteer door op ‘OK’ te drukken. Hierdoor worden belangrijke datums in uw gegevens snel gemarkeerd.

Hoe kan ik vervaldatums voorwaardelijk opmaken in Excel?

Selecteer eerst uw cellen met datums voor vervaldatums met voorwaardelijke opmaak in Excel. Klik vervolgens op ‘Voorwaardelijke opmaak’ op het tabblad ‘Start’. Kies ‘Nieuwe regel’, selecteer ‘Alleen cellen opmaken die deze bevatten’, stel de regel in op ‘Celwaarde’ en ‘kleiner dan’ en voer uw vervaldatum of een formule in. Klik op ‘Opmaak’, kies uw opmaakstijl en druk op ‘OK’ om toe te passen.