Hoe u voorwaardelijke opmaak in Excel instelt voor datums

Voorwaardelijke opmaak in Excelkan worden beschouwd als een waardevol hulpprogramma dat kan helpen de gegevensanalyse en -presentatie in de spreadsheet visueel te 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 handleiding leest.

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

Voorgedefinieerd
Door de gebruiker gedefinieerd
Maak een op maat gemaakte opmaakregel
Feestdagen markeren in Excel
Voorwaardelijke opmaak van datums op basis van meerdere voorwaarden
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 opStart > 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.

KlikOKom 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

Open deEen datum die plaatsvindtformatteer venster door de hierboven genoemde stappen te volgen.
Nadat u het dagbereik hebt geselecteerd, selecteert uAangepast formaatuit het vervolgkeuzemenu met opmaakopties.
DeVenster Cellen opmakenzou vanwege het bovenstaande verschijnen, waar we de kleur, rand, lettertype en effecten van onze keuze kunnen kiezen en vervolgens op OK kunnen klikken 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 deThuisTabblad, klikVoorwaardelijke opmaak > Nieuwe regel, die deNieuw opmaakregelvenster.

In het genoemde venster kunnen we kiezen uit de beschikbareRegeltypen, gevolgd door het bewerken van de regelbeschrijving om de criteria voor voorwaardelijke opmaak in te stellen.
Eenmaal voltooid, wordt deFormaatU kunt op de knop rechtsonder in het venster klikken om de opmaak voor de genoemde regel te kiezen.
Op deCellen opmakenvenster dat wordt geopend, kunnen we het lettertype, de rand, de celkleur, de effecten, enz. kiezen, naar keuze en eenmaal op OK klikken in dit venster, gevolgd door deNieuwe opmaakregelVenster waarin de wijzigingen moeten worden toegepast.

Opmerking:De opmaakregels kunnen worden gebruikt voor het maken en toepassen van meer dan één regel voor dezelfde dataset op basis van verschillende voorwaarden/criteria.

Lezen:

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

OnderSelecteer een nieuwe regelkies in de optie Voorwaardelijke opmaakGebruik een formule om te bepalen welke cel moet worden opgemaakt, typ:

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

=$H4>VANDAAG()om toekomstige dagen te benadrukken

=$H4om de voorgaande dagen te benadrukken

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

=$H4<=(VANDAAG()-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$naast gebruiktHbetekent dat de kolom constant zou blijven, maar de rijen zouden variabel zijn.

Zodra deRegelbeschrijvingwordt vermeld, kunnen het lettertype, de kleuren en de effecten worden gekozen door op de knop Opmaak te klikken.
Klik opOKin het venster Cellen opmaken, gevolgd door hetzelfde in het venster Nieuwe regel, zodat de wijzigingen effectief zijn.

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.
In deBewerk de regelbeschrijvingveld, voer de onderstaande formule in,

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

De bovenstaande formule gebruikt deENoperator 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.
In deBewerk de regelbeschrijvingveld, voer de onderstaande formule in,

=WEEKDAY($H2, 2)>5

Desyntaxisvan de formule Weekdag kan worden weergegeven als WEEKDAG(serienummer, [retourtype]), waarbij serienummer het celnummer vertegenwoordigt van de datum die we proberen te controleren (SA1 in dit geval).

Dereturn_typesparameter vertegenwoordigt het type voor de week die begint op maandag(1) tot en met zondag(7), waarbij de startdag van de week wordt beschouwd 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.>5wordt 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 deDATUMWAARDE()functie. 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 opCellen opmaken > Getal,en kies danAlgemeen. Er wordt een numerieke waarde van 5 cijfers op de cel weergegeven, 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 opVoorwaardelijke opmaak>Nieuwe regel> Selecteer een nieuwe regel>Gebruik een formule om te bepalen welke cel moet worden opgemaakten typ de volgende formule,

=$G5=45249

Met behulp van deDATUMWAARDE()functie, hetzelfde kan ook 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.

Opmerking:De serienummerreeks van deze datums in Excel begint bij 1^stJanuari 1900, als serienummer 1. Als we dus de datum 19-nov-2023 beschouwen, zou deze genummerd zijn als 45249, aangezien het 45248 dagen na 1 is.^stJanuari 1900.

Lezen:

4] Feestdagen markeren in Excel

Omdat feestdagen kunnen variëren op basis van demografische gegevens, heeft Excel geen ingebouwde functies om feestdagen te markeren. Om de feestdagen te identificeren, moeten we de datums dus in een andere kolom laten noteren en detelformule om te controleren of de vakantiedata in het gegevensblad voorkomen.

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 deNieuwe regeldialoogvenster en kiesGebruik een formule om te bepalen welke cellen moeten worden opgemaaktonderSelecteer Regeltype.
Voer de formule in=AANTAL.ALS($G$3:$G$5,$D3)
Klik opOpmaak > Vullenen selecteer de kleurROODuit het palet en klik opOK.

Detel.()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 selecteerGebruik een formule om te bepalen welke cellen moeten worden opgemaaktonderSelecteer een regeltype.
Voer de onderstaande formule in

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

KlikFormaat, selecteer de BLAUWE kleur in het palet en klik op OK.
Maak er nog eenNieuwe regelmet 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 dat de klacht nog open isENcontroleer vervolgens of de VERVALDATUM groter is dan de huidige datum of korter om te bepalen of de vervaldatum nadert ($H10>$K$6)of overtroffen ($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 hetNieuwe regeldialoogvenster.
KiezenFormatteer alleen cellen dieonderSelecteer Regeltype
InRegelbeschrijving bewerken, kiezenCelwaarde,Groter dan,=Nu()+30in de respectievelijke vervolgkeuzelijsten.
Klik opFormaat,selecteer GROEN in het kleurenpalet,en klikOK.

Open opnieuw eenNieuwe regelen kies hetzelfdeRegeltype,zoals hierboven.
InRegelbeschrijving bewerken, kiezenCelwaarde,Minder dan,=Nu()in de respectievelijke vervolgkeuzelijsten.
Klik opFormaaten selecteerROODuit het kleurenpalet en klikOK.

Conclusie

Deze tutorial 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 bij het identificeren van trends, het controleren van deadlines en het stellen van taakprioriteiten, waardoor weloverwogen beslissingen kunnen worden genomen.

Lezen: Hoe

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 'Home' > '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 deze toe te passen.